How to get notified when deadlock occurs

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

How to get notified when deadlock occurs

Author

Topic

venkath
Posting Yak Master

202 Posts

Posted - 2006-07-04 : 01:30:05

Hi all

How to notified automatically or how to know when ever the deadlock occurs in the database...

Thanks

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-07-04 : 03:02:27

this one's tricky and best left to determine which ones are engaged in deadlocks rather than let it run it's own course and get notified when it occurs

i never have explored the alerts much but without using alerts, you can browse the sysprocesses table and send email if rowcount>0, for automation, schedule the query... below is a simple code to query for deadlocks


select s1.spid,s1.blocked 
from master..sysprocesses s1
join master..sysprocesses s2
on s1.spid=s2.blocked and s1.blocked=s2.spid

--------------------
keeping it simple...

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2006-07-05 : 03:19:02

>> How to notified automatically or how to know when ever the deadlock occurs in the
>> database...

1. Create an alert for deadlock
2. In the response tab, you can configure for email/net send/ pager.

------------------------
I think, therefore I am - Rene Descartes

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2006-07-05 : 03:24:36

-- Following code will eneter the deadlock code to sql server log
dbcc traceon (1204, 3605, -1)
go
dbcc tracestatus(-1)
go

Need to do more work on this..

------------------------
I think, therefore I am - Rene Descartes

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources