SQL server connection and blocking finding script -- For SQL 2000 select db_name(dbid),* from master.dbo.sysprocesses where spid> 51 and status <>'sleeping' -- and blocked <>0 -- To find the spid and connections which are all running select db_name (r.database_id),r.command,r.wait_resource,s.status,s.session_id, r.blocking_session_id,r.last_wait_type,r.open_transaction_count,sql_handle, * from sys.dm_exec_sessions s join sys.dm_exec_requests r on s.session_id =r.session_id --where s.status <>'sleeping' and s.session_id >=51 and s.session_id <>@@SPID -- exclude your session -- to find the blockings select db_name (r.database_id),r.command,r.wait_resource,s.status,s.session_id, r.blocking_session_id,r.last_wait_type,r.open_transaction_count, * from sys.dm_exec_sessions s join sys.dm_exec_requests r on s.session_id =r.session_id where r.blocking_session_id<>0 -- list blocking select * FROM sys.dm_exec_requests -- to find the code which is run by spid connections SELECT T.TEXT As Query, R.session_id, R.status, R.command…
-
-
Move database between drives
SQL server Move user database steps and scripts: Check you have a free space on the moving drive and make sure the SQL service account has read and write permission. (You can check the account name in configuration manager) Move TempDB database --========================= for tempdb -- note the existing details with logical name --Step 1 USE TempDB GO EXEC sp_helpfile tempdev 1 C:\Program Files\Microsoft SQL Server\MSSQL11.PROD\MSSQL\DATA\tempdb.mdf templog 2 C:\Program Files\Microsoft SQL Server\MSSQL11.PROD\MSSQL\DATA\templog.ldf --Step 2 -- alter to new location USE master GO ALTER DATABASE TempDB MODIFY FILE (NAME = tempdev, FILENAME = 'T:\SQL_TEMPDB\tempdb.mdf') GO ALTER DATABASE TempDB MODIFY FILE (NAME = templog, FILENAME = 'T:\SQL_TEMPDB\templog.ldf') GO --Step 3 -- restart…
-
SQL server change single user mode or backup or recovery model of all database sql script
Following is the DBA script which generally used for most cases the action needed for all the databases like migration, upgrade etc. Change the database recovery model and set to single_user mode. Backup of all databases. -- Dynamic SQL select 'alter database ['+name+'] set single_user with rollback immediate' , * from sys.databases where database_id not in (1,2,3,4)-- exclude system DBs select 'alter database ['+name+'] set multi_usere' , * from sys.databases where database_id not in (1,2,3,4)-- exclude system DBs select 'alter database ['+name+'] set recovery simple' , * from sys.databases where database_id not in (1,2,3,4)-- exclude system DBs select 'backup database ['+name+'] to disk = ''B:\SQL_backup\'+name+'_full.bak''' , * from sys.databases where…
-
Drive space check SQL server steps and scripts
Following is the script used for drive space related issues. --SQL 2000 -- select name,(size/128.0) as size_mb,* from master.dbo.sysaltfiles order by size_mb desc -- To check the drive free space xp_fixeddrives ------------------------------- get all the db file info & filter the drive, mdf/ldf select db_name(a.database_id) as DBname,a.name as DBfile , size/128.0 AS CurrentSizeMB, --size/128.0 - ((size/128.0) - CAST(FILEPROPERTY(a.name, 'SpaceUsed') AS INT)/128.0) AS UsedSpaceMB, --size/128.0 - CAST(FILEPROPERTY(a.name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB, b.recovery_model_desc,a.type_desc , CASE WHEN is_percent_growth = 0 THEN LTRIM(STR(a.growth * 8.0 / 1024,10,1)) + ' MB, ' ELSE 'By ' + CAST(a.growth AS VARCHAR) + ' percent, 'END + CASE WHEN max_size = -1 THEN 'unrestricted growth' ELSE 'restricted…