Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Deadlock Notifications in SQL Server 2005 Expand / Collapse
Author
Message
Posted Tuesday, September 04, 2007 5:29 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, March 13, 2014 8:47 PM
Points: 229, Visits: 294
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/pleblanc/3243.asp


Post #396310
Posted Wednesday, October 10, 2007 4:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 5:08 AM
Points: 1, Visits: 262
Try this stored procedure:
(where PerfDB is my database for differend kind of administrative task)
and make a job with that stored procedure, and run this job at every minute, and you will have a real time monitor of bloking.

USE [PerfDB]
GO
/****** Object: StoredProcedure [dbo].[BD_Monitorizare_Blocaje] Script Date: 10/10/2007 12:59:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[BD_Monitorizare_Blocaje]
as

declare @spid int
declare @blk_by int
declare @spid2 int
declare @buffer1 varchar(8000)
declare @buffer2 varchar(8000)
declare @sql1 varchar(8000)
declare @sql2 varchar(8000)
declare @login1 varchar(50)
declare @login2 varchar(50)
declare @database_name varchar(20)
declare @dbid int
declare @sql5 varchar(8000)
declare @sql6 varchar(8000)
declare @hostname1 varchar(100)
declare @hostname2 varchar(100)
declare @program_name1 varchar(100)
declare @program_name2 varchar(100)

create table #BD_Monitorizare_Procese
(
spid varchar(50),
login varchar(100),
hostname varchar(100),
program_name varchar(100),
blk_by varchar(100),
dbid varchar(100),
last_batch datetime
)

insert into #BD_Monitorizare_Procese
select
spid
,convert(sysname, rtrim(loginame))
,hostname
,program_name
,blocked
,dbid
, last_batch
from master.dbo.sysprocesses with (nolock)
where len(hostname)>0

DECLARE xcursor CURSOR FOR
SELECT spid FROM #BD_Monitorizare_Procese order by spid
OPEN xcursor
FETCH NEXT FROM xcursor INTO @spid
WHILE (@@fetch_status = 0)
BEGIN
select @blk_by = blk_by from #BD_Monitorizare_Procese where spid=@spid
if @blk_by <> 0
begin

select @login1 = login from #BD_Monitorizare_Procese where spid=@spid
select @login2 = login from #BD_Monitorizare_Procese where spid=@blk_by
select @dbid = dbid from #BD_Monitorizare_Procese where spid=@spid
select @database_name = name from sys.sysdatabases where dbid=@dbid
select @hostname1 = hostname from #BD_Monitorizare_Procese where spid=@spid
select @hostname2 = hostname from #BD_Monitorizare_Procese where spid=@blk_by
select @program_name1 = program_name from #BD_Monitorizare_Procese where spid=@spid
select @program_name1 = program_name from #BD_Monitorizare_Procese where spid=@blk_by

create table #dbcc(c1 varchar(20), c2 int, c3 varchar(8000))

insert #dbcc EXEC('dbcc inputbuffer('+@spid+')')
select @buffer1 = c3 from #dbcc

delete from #dbcc

insert #dbcc EXEC('dbcc inputbuffer('+@blk_by+')')
select @buffer2 = c3 from #dbcc

drop table #dbcc

set @buffer1 = replace(@buffer1,'''','`')
set @buffer2 = replace(@buffer2,'''','`' )


--
--declare @buffer1 varchar(8000)
--declare @buffer2 varchar(8000)
--declare @sql1 varchar(8000)
--declare @sql2 varchar(8000)
--declare @login1 varchar(50)
--declare @login2 varchar(50)
--declare @database_name varchar(20)
--declare @dbid int
--declare @sql5 varchar(8000)
--declare @sql6 varchar(8000)
--declare @hostname1 varchar(100)
--declare @hostname2 varchar(100)
--declare @program_name1 varchar(100)
--declare @program_name2 varchar(100)
--set @database_name='fffff'
--set @login1='ddddd'
--set @login2='gggggg'
--set @hostname1='hhhhhhh'
--set @hostname2='sssss'
--set @buffer1='423423423324'
--set @buffer2='55345323552'

exec('exec PerfDB.dbo.BD_Send_Alert ''Este un blocaj pe baza '+@database_name+'. Userul '+@login1+' de pe statia '+@hostname1+' este blocat de '+@login2+' de pe statia '+@hostname2+' ! '+'Userul '+@login1+' executa: '+@buffer1+'. Userul '+@login2+' executa: '+@buffer2+'' +''''+ ','' BLOCAJ !!!''')
--print 'exec PerfDB.dbo.BD_Send_Alert ''Este un blocaj pe baza '+@database_name+'. Userul '+@login1+' de pe statia '+@hostname1+' este blocat de '+@login2+' de pe statia '+@hostname2+' ! '+'Userul '+@login1+' executa: '+@buffer1+'. Userul '+@login2+' executa: '+@buffer2+'' +''''+ ','' BLOCAJ !!!'''

-- set @sql1= 'EXEC master..xp_cmdshell ''net send CBOGDUM '''' Este un blocaj pe baza '+@database_name+' userul '+@login1+' este blocat de '+@login2+' !!'''
-- exec (@sql1)
--
-- set @sql2=
-- 'EXECUTE [msdb].[dbo].[sp_send_dbmail]
-- @profile_name = ''MailAccount''
-- ,@recipients = ''bogdan.dumitru@railoc.ro''
-- ,@body = '' Este un blocaj pe baza '+@database_name+'! Userul '+@login1+' de pe statia '+@hostname1+' este blocat de '+@login2+' de pe statia '+@hostname2+' ! '+char(13)+char(13)+'Userul '+@login1+' executa: '+char(13)+@buffer1+char(13)+char(13)
-- +' Userul '+@login2+' executa: '+char(13)+@buffer2+'''
-- ,@subject = ''BLOCAJ!'''
-- exec (@sql2)

insert into BD_Monitorizare_Blocaje_tbl ( database_name, login1_blocat, login2_care_blocheaza, hostname_login1, hostname_login2, buffer_login1, buffer_login2 )
values (@database_name, @login1, @login2, @hostname1, @hostname2, @buffer1, @buffer2 )

FETCH NEXT FROM xcursor INTO @spid
end
else
FETCH NEXT FROM xcursor INTO @spid
END
CLOSE xcursor
DEALLOCATE xcursor


--create table BD_Monitorizare_Blocaje_tbl
--(
--data datetime default getdate(),
--database_name varchar(20),
--login1_blocat varchar(50),
--login2_care_blocheaza varchar(50),
--hostname_login1 varchar(50),
--hostname_login2 varchar(50),
--buffer_login1 varchar(8000),
--buffer_login2 varchar(8000),
--program_name1 varchar(100),
--program_name2 varchar(100)
--)
Post #408883
Posted Wednesday, October 10, 2007 6:44 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, January 30, 2012 5:12 AM
Points: 40, Visits: 143
"For per-second counters, this value is cumulative. The rate value must be calculated by sampling the value at discrete time intervals. The difference between any two successive sample values is equal to the rate for the time interval used."

It means that initially you may have some not 0 values and the proc dbo.usp_DeadlockNotification
will gives the alerts each time.
You should use difference in time interval.
Post #408969
Posted Wednesday, October 10, 2007 7:18 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, October 25, 2013 6:36 AM
Points: 469, Visits: 259
I haven't tried it yet, but could you also set up an Alert in SQL Server 2005 where the Object is SQL Server:Locks and the Counter is Number of Deadlocks/sec with a condition of if rises above 0. Then you could subsrcibe to the Alert?
Post #409002
Posted Wednesday, October 10, 2007 7:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 19, 2011 6:40 AM
Points: 1, Visits: 32
why not just use event notfications that are available in sql 2005? If you monitor the deadlock graph event you can get an alert that contains information about the deadlock. The alert can be a realtime email or just storing the data in a table which you can check once a day.
This is a lot easier than writing a job which checks the details every minute or half an hour and contains all of the information you need to track down where the problem is.

Tony Rogerson has written an excellent article on to set up event notifications for blocking events at http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/04/06/event-notifications-monitoring-blocked-processes-and-other-events-end-to-end-how-to-set-it-up-and-make-it-work.aspx
It fairly easy to change that to report on deadlock_graph event instead
Post #409007
Posted Wednesday, October 10, 2007 7:45 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, January 03, 2014 9:35 AM
Points: 1,285, Visits: 109
Great article!

As for the DMV, I concur with LP. The sys.dm_os_performance_counters view is constantly incrementing various counters of performance that SQL Server reports to the O/S. As written, after the first deadlock occurs the proc will signal an e-mail every time the job runs. If you capture the results at the last run of the sproc, then compare the current run against those values you'll have an accurate measure of deadlocks in the interval between job runs. The you can signal an e-mail only when there's an increase in the counter since the last run.

As for the volatility of the values in the DMV, they are all reset to 0 with a reboot or an instance restart.

Thanks for the article, and I hope this helps!

Carter




But boss, why must the urgent always take precedence over the important?
Post #409028
Posted Wednesday, October 10, 2007 7:56 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, March 13, 2014 8:47 PM
Points: 229, Visits: 294
Yes you can use event notfications, and thanks for the link.


Post #409037
Posted Wednesday, October 10, 2007 4:05 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 10:56 PM
Points: 1,592, Visits: 1,486
Great article!! And thanks for the plug of my Blog.



My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #409281
Posted Wednesday, October 10, 2007 4:37 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 02, 2011 9:50 PM
Points: 189, Visits: 335
Thanks Patrick, There are many ways to this end. Yours.... simple....effective....to the point.

Awesome !!

CodeOn :P
Post #409290
Posted Thursday, October 11, 2007 7:34 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, March 13, 2014 8:47 PM
Points: 229, Visits: 294
Thanks for such a great blog.


Post #409518
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse