Has anyone figured this one out yet. I'm having same problem.
Trace flag is set
alert is enabled
I caused an error 1205 and did not receive the alert.
- did you stop and restart sqlagent _after_ you enabled the (first) alert ?
- also add traceflag 3605 for more deadlockinfo in the sqlservereventlog.
found two articles. the first is the MS article I found. THe problem described is not the same problem. but the behavior is the same. The second is an atricle about deadlocks and the author states that you must start or restart the sql server.
I did a lot of testing on this ad the final solution was to restart the SQL Server. I think I reset up the Alert and restated the SQL Server.
This is similar to this post
Is this a secret double life?
don't need trace flag 3605, just need 1204
Try this to set the alert up:
EXEC dbo.sp_add_alert 'Error 1205 - Deadlock detected', 1205, 0, 1, 60, NULL, 5 EXEC dbo.sp_add_notification 'Error 1205 - Deadlock detected', 'whoever', 5
This works for me...