SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Deadlock Notifications in SQL Server 2005


Deadlock Notifications in SQL Server 2005

Author
Message
Patrick_LeBlanc
Patrick_LeBlanc
Old Hand
Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)

Group: General Forum Members
Points: 397 Visits: 324
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/pleblanc/3243.asp



Bogdan Dumitru
Bogdan Dumitru
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 336
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)

--)
LP-181697
LP-181697
SSC Veteran
SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)

Group: General Forum Members
Points: 272 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.
DBA_Rob
DBA_Rob
Right there with Babe
Right there with Babe (718 reputation)Right there with Babe (718 reputation)Right there with Babe (718 reputation)Right there with Babe (718 reputation)Right there with Babe (718 reputation)Right there with Babe (718 reputation)Right there with Babe (718 reputation)Right there with Babe (718 reputation)

Group: General Forum Members
Points: 718 Visits: 354
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?
antxxxx
antxxxx
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 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
Will SQL 4 Food
Will SQL 4 Food
Default port
Default port (1.4K reputation)Default port (1.4K reputation)Default port (1.4K reputation)Default port (1.4K reputation)Default port (1.4K reputation)Default port (1.4K reputation)Default port (1.4K reputation)Default port (1.4K reputation)

Group: General Forum Members
Points: 1433 Visits: 148
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?
Patrick_LeBlanc
Patrick_LeBlanc
Old Hand
Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)

Group: General Forum Members
Points: 397 Visits: 324
Yes you can use event notfications, and thanks for the link.



Robert Davis
Robert Davis
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6530 Visits: 1632
Great article!! And thanks for the plug of my Blog. Smooooth



My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master, SQL Server MVP
Database Engineer at BlueMountain Capital Management
Malcolm Daughtree
Malcolm Daughtree
Mr or Mrs. 500
Mr or Mrs. 500 (557 reputation)Mr or Mrs. 500 (557 reputation)Mr or Mrs. 500 (557 reputation)Mr or Mrs. 500 (557 reputation)Mr or Mrs. 500 (557 reputation)Mr or Mrs. 500 (557 reputation)Mr or Mrs. 500 (557 reputation)Mr or Mrs. 500 (557 reputation)

Group: General Forum Members
Points: 557 Visits: 335
Thanks Patrick, There are many ways to this end. Yours.... simple....effective....to the point.



Awesome !!



CodeOn Tongue
Patrick_LeBlanc
Patrick_LeBlanc
Old Hand
Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)

Group: General Forum Members
Points: 397 Visits: 324
Thanks for such a great blog.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search