Monitoring for deadlocks when trace flag 1222 is on

Question

Monitoring for deadlocks when trace flag 1222 is on

derek.colley

SSCrazy Eights

Points: 8040
More actions
July 17, 2012 at 6:20 am

#143654

Hi
I've set trace flag 1222 on, (which is very useful for analysing the causes of deadlocks, reading the data directly from the error log).
However my alerting doesn't fire because message 1205 (deadlock detected, etc) no longer appears in the error log.
Version is 2005. I've got Gail's chapter in MVP Deep Dives in front of me but there's no mention of monitoring - does anyone have an idea how to monitor for this, please?
Thanks
---
Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:
Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 1

You can change the alert to capture 1222. Or you can add 1205 traceflag so you get both, but that makes for very messy error logs.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 2

Marvellous, thanks Grant.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.