Just Misc

–clear wait stat
DBCC SQLPERF(“sys.dm_os_wait_stats”,CLEAR)

–To setup the template
–You will have to change your username
XCopy “\\serverName\e$\SQL_Template\*.*” “C:\Users\loginname\AppData\Roaming\Microsoft\SQL Server Management Studio\11.0\Templates\Sql\” /E /Y

–root block
SELECT p.sid
,convert(char(12), d.name) db_name
, program_name
, convert(char(12), l.name) login_name
, convert(char(20), hostname) hostname
, cmd
, p.status
, p.blocked
, login_time
, last_batch
, p.spid
FROM master..sysprocesses p
JOIN master..sysdatabases d ON p.dbid = d.dbid
JOIN master..syslogins l ON p.sid = l.sid
WHERE p.blocked = 0
AND EXISTS ( SELECT 1
FROM master..sysprocesses p2
WHERE p2.blocked = p.spid )

Leave a Reply

Your email address will not be published. Required fields are marked *

*