SQL: Monitoring Locking

I've been asked to monitor SQL Server Locking, I create the following script and I scheduled it every 5 minutes, you can customize the action changing PRINT command with any other action you need (mail, log file...):

declare @Message1 nvarchar(255)
declare @Message2 nvarchar(255)
declare @ProcessID1 decimal(5,0)
declare @ProcessID2 decimal(5,0)
declare @LockTime as datetime

DECLARE my_Cursor CURSOR FOR
select convert (smallint, req_spid) As spid
from master.dbo.syslockinfo,
master.dbo.spt_values v,
master.dbo.spt_values x,
master.dbo.spt_values u
where master.dbo.syslockinfo.rsc_type = v.number
and v.type = 'LR'
and master.dbo.syslockinfo.req_status = x.number
and x.type = 'LS'
and master.dbo.syslockinfo.req_mode + 1 = u.number
and u.type = 'L'
and substring (x.name, 1, 4) = 'WAIT'
order by spid

open my_Cursor

FETCH NEXT FROM my_Cursor
INTO @ProcessID1

while @@fetch_status=0
BEGIN

select @Message1 = 'ProcessID: ' + cast(SPID AS char) + ' User: ' + loginame + ' Query: ' + cmd + ' Blocked by: ' + cast(Blocked AS char), @ProcessID2 = Blocked
from sysprocesses where SPID = @ProcessID1
select @Message2 = 'ProcessID: ' + cast(SPID AS char) + ' User: ' + loginame + ' Query: ' + cmd from sysprocesses where SPID = @ProcessID2
set @LockTime = getdate()

PRINT @LockTime
PRINT @Message1
PRINT @Message2

FETCH NEXT FROM my_Cursor
INTO @ProcessID1

END
close my_Cursor
deallocate my_Cursor

Share on Google Plus

About Vittorio Pavesi