1. Skip to Menu
  2. Skip to Content
  3. Skip to Footer>

Expert Sessions!

Deadlock Monitoring Script

Tuesday, 31 July 2012 11:50

Written by Prakash Palani

Print E-mail

 

Below script will help you to effectively monitor the deadlock; the same will also send out the alerts when the deadlock is identified. Please configure database mail according to articlehttp://www.basisondemand.com/mss/configure_database_mail_sp_send_dbmail, only then this script will be able to send out the alerts, else the job will fail.

 

use msdb
Create Table LOCKMON
(
blocking_session_id int,
starttime datetime
)


insert msdb..LOCKMON select blocking_session_id, start_time from sys.dm_exec_requests  where blocking_session_id > 0

DECLARE @RowCount INT
DECLARE @Max Int
SET @RowCount = (SELECT COUNT(blocking_session_id) FROM msdb..LOCKMON)
Set @Max = (select max(blocking_session_id) from msdb..LOCKMON)
DECLARE @I INT
-- Initialize the iterator
SET @I = 1
WHILE (@I > 0)
begin
declare @blocking int
declare @difference datetime
select @blocking = blocking_session_id from msdb..LOCKMON where blocking_session_id=@Max
select @difference = starttime from msdb..LOCKMON where blocking_session_id=@Max
if ( @blocking >= 0 ) and ( datediff(minute, @difference, getdate()) > 5 )
begin
Declare @Subject varchar(4000)
DECLARE @To varchar(4000)
DECLARE @Body varchar(4000)
SELECT  @To = ' This e-mail address is being protected from spambots. You need JavaScript enabled to view it '
SELECT @Subject='SQL Alert: Lock Found :   -> blocking_session_id : ' + convert(varchar(20),@blocking)
Select @Body = 'Use This Query to Find the Oldest Lock : Select session_id, wait_type, start_time, command, blocking_session_id from sys.dm_exec_requests  where blocking_session_id > 0 order by 3'
EXECUTE msdb.dbo.sp_send_dbmail
@profile_name = 'BasisSupport',
@recipients = @To,
@subject = @Subject,
@body = @Body
end
else
begin
PRINT 'Not 0'
end
select top 1 @blocking = blocking_session_id from msdb..LOCKMON where blocking_session_id < @Max order by blocking_session_id desc
select top 1 @difference = starttime from msdb..LOCKMON where blocking_session_id=@Max
if ( @blocking >= 0 ) and ( datediff(minute, @difference, getdate()) > 5 )
begin
SELECT  @To = ' This e-mail address is being protected from spambots. You need JavaScript enabled to view it '
SELECT @Subject='SQL Alert: Lock Found : -> blocking_session_id : ' + convert(varchar(20),@blocking)
Select @Body = 'Use This Query to Find the Oldest Lock : Select session_id, wait_type, start_time, command, blocking_session_id from sys.dm_exec_requests  where blocking_session_id > 0 order by 3'
EXECUTE msdb.dbo.sp_send_dbmail
@profile_name = 'BasisSupport',
@recipients = @To,
@subject = @Subject,
@body = @Body
end
else
begin
PRINT 'Not 0'
end

SET @I = @I  - 1
drop table LOCKMON
end

{fcomment} {flike}
Share
Deadlock Monitoring Script