Alerts for Transaction Deadlocks 1

[JohnBates]

Happy Friday people,

This is 2000.

I've set up alerts to send me an email whenever a deadlock occurs (Error 1205). That works, I receive an email.

Next, I created many more alerts for 1205 - in each one I specified a database name and the db name in the alert text.

My problem is that I have not received any Alerts for individual databases, only for the general alert which could be any database.

I even disabled the general alert, thinking that it may be blocking the individual-database alerts. Did not help - still don't receive any alerts.


Has anyone had success in using the database setting for Alerts caused by a deadlock ?

Thanks much.
John

 

[mrdenny]

Deadlock reporting probably isn't database specific. Try creating one in each of the system databases. I bet one of the system database ones starts going off.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ptheriault]

John,
I use -t1204 in my start up parameters. It puts deadlock info in the Log.

you can also enable it like this.

DBCC TRACEON (1204).

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[JohnBates]

Thanks Paul and Denny.

It just occurred to me that I may need to restart SQL Server Agent to get the new Alerts to go into effect.

Also, I think SQL Server Management Studio does a better job of detecting deadlocks. I will experiment with that but I don't know if it will detect a deadlock in a S-S 2000 database.

I'll report back with what happens.

John