Upgrading to SQL Server 2005

As we all know that Microsoft has released SQL Server 2005 sometime back and is planning to release the next version KATMAI before end of 2008. Also it has announced the end of life cycle support for SQL Server 2000. It’s high time that we start with upgrading our instances with SQL Server 2005 and upgrade before the life cycles support ends.

http://www.sql-articles.com/index.php?page=articles/usql.htm

Query Plans & Planguides

Most developer DBAs have tough time tuning TSQL part of the database and application. And most people leave in middle of it either frustrated or can’t go beyond that. Here is a simple look on how to use the query hints and plan guides to tune the TSQL part of your database and application.

http://www.sql-articles.com/index.php?page=articles/query_plans.html

Memory Management in SQL Server

I have heard people asking me how SQL Server manages memory. The answer to this was very difficult with SQL Server earlier versions. And with SQL Server 2005 it has been simpler to explain and understand. SQL Server by default installation manages memory dynamically and has settings that it can well negotiate with the operating system for the need of memory. Let us look into the depth of the SQL Server memory management.

Read more of this on http://www.sql-articles.com/index.php?page=articles/memory_management.html

Check Identity

This will list out identity columns in a Database. This is compatible to SQL 2005 only.
http://www.sql-articles.com/index.php?page=Scripts/check_idenity.php

Connection Property

This script will list out the current connection properties on SQL 2005.

http://www.sql-articles.com/index.php?page=Scripts/conn_prop.php

Database Details

This will list out the Database properties of your database in sql server. This is compatible to SQL 2000 and 2005. Before you use the script make sure you have given the databasename for the parameter @dbname.

http://www.sql-articles.com/index.php?page=Scripts/db_details.php

Index Details for Tables

This script will list out the details of the indexes in a table.This is compatible to SQL 2000 and 2005.
http://www.sql-articles.com/index.php?page=Scripts/index_details.php

Permission List

This script gives a detailed information about permission for users in a database. This output lists Object name, Schema name, user name and permission. This is compatible only to SQL 2000.
http://www.sql-articles.com/index.php?page=Scripts/permission_list.php

Permission List SQL 2005

This script gives a detailed information about permission for users in a database. This output lists Object name, Schema name, user name and permission. This is compatible only to SQL 2005.

http://www.sql-articles.com/index.php?page=Scripts/permission_list_2005.php

Scripting User level Permission - SQL2000

This script gives a out script that can be used to script out the exising user permissions in a database.This is compatible only to SQL 2000.Don't forget to change the database name in paramert @dbname before using the script.

http://www.sql-articles.com/index.php?page=Scripts/scrip_user_00.php

Scripting User Level Permission SQL2005

This script gives a out script that can be used to script out the exising user permissions in a database.This is compatible only to SQL 2005.

http://www.sql-articles.com/index.php?page=Scripts/scrip_user_05.php

Server Property

This will list out the server properties of your sql server including the account under which your services run. This is compatible to SQL 2000 and 2005.

http://www.sql-articles.com/index.php?page=Scripts/ServerProperties.php

Table Space Details

This script will list out the details of the space used by each in a database.This is compatible to SQL 2000 and 2005.

http://www.sql-articles.com/index.php?page=Scripts/sp_details_tbl.php

Table and Column Details

This script gives a detailed information about tables and thier associated column properties for each database. This lists out Database name, owner name, table name, column name, data type, collation, etc. This is compatible to SQL 2000 and 2005.

http://www.sql-articles.com/index.php?page=Scripts/tbl_col_det.php

Trigger Status

This will list out status of the trigger in a Database. This is compatible to SQL 2000 and 2005.

http://www.sql-articles.com/index.php?page=Scripts/trigger_status.php

List orphan IDs

This script displays the list of orphan SQL Server accounts from all the databases

Exec SP_MSForEachDB 'select ''?'' as DBName, name AS UserName, sid AS UserSID
from ?..sysusers
where issqluser = 1 and
(sid is not null and sid <> 0x0)
and suser_sname(sid) is null
order by name'

Estimated time for backup/restore

This script can be used to find the estimated time of backup and restore that is on progress in your SQL server.


SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete)
AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],
CONVERT(NUMERIC(6,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
CONVERT(NUMERIC(6,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],
CONVERT(NUMERIC(6,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
CONVERT(VARCHAR(100),(SELECT SUBSTRING(text,r.statement_start_offset/2,
CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
FROM sys.dm_exec_sql_text(sql_handle)))
FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')

Check if /3GB is configured or not

This script is used to check whether the 3gb switch is configured or not in the boot.ini file.

If(Select Virtual_Memory_In_Bytes/1024/(2048*1024) from Sys.dm_os_Sys_Info) < 1

Begin

PRINT '/3GB Switch Not Configured in Boot.Ini (CHECK)'

End

Else

Begin

PRINT '/3GB Switch Configured in Boot.Ini'

End

Create Windows network shares

This procedure can be used to share any windows folders with default permissions
This procedure accepts 3 parameters
-- @path - The path of the share folder
-- @sharename - The sharename to be given
-- @type - This accepts 2 vaules either to create or revoke if create a new share is created if revoke the share ie removed

Script:

create procedure windowsshare (@path varchar(200), @sharename varchar(50),@type varchar(10))
as
begin
declare @cmd varchar(100)
if lower(@type) = lower('create')
BEGIN
set @cmd = 'net share '+@sharename+'='+@path
print @cmd
exec master..xp_cmdshell @cmd,no_output
PRINT ' SHARE '+@sharename+' HAS BEEN CREATED SUCCESSFULLY'
end
ELSE
BEGIN
set @cmd = 'net share '+@sharename+' '+@path+' /delete'
exec master..xp_cmdshell @cmd,no_output
PRINT ' SHARE '+@sharename+' HAS BEEN REMOVED SUCCESSFULLY'
end
END

Give permission to Windows NTFS objects

This procedure can be used to provide the security options in windows NTFS folders with default permissions.
This procedure accepts 5 parameters
-- @path - The path of the share folder
-- @traverse - To specify if the permission needs to be traversed to sub folders or not
-- Y - to give permissions to subfolders also
-- N - to not give permissions to sub folders
-- @type - This accepts 4 vaules
-- g - to grant access to the specified folder and/or sub-folders
-- p - to modify access to the specified folder and/or sub-folders
-- r - to revoke access to the specified folder and/or sub-folders
-- d - to deny access to the specified folder and/or sub-folders
-- @user - the user to whom access needs to be specified
-- @perm - type of permission to be given and this accepts 4 parameters
-- N - No Permissions
-- R - Read
-- C - Change
-- F - Full Control


Script:
create procedure windowssec (@path varchar(200), @traverse char,@type char,@user varchar(50),@perm char)
as
begin
declare @cmd varchar(100)
if (upper(@traverse) <> 'Y')
begin
set @cmd = 'cacls '+@path
end
else
begin
set @cmd = 'cacls '+@path+' /t '
end
if (lower(@type) = 'r' or lower(@type) = 'd')
begin
set @cmd = @cmd+' /e /'+@type+' '+@user
exec master..xp_cmdshell @cmd
end
else
begin
set @cmd = @cmd+' /e /'+@type+' '+@user+':'+@perm
exec master..xp_cmdshell @cmd
END
end