SQL Server Alert System: error 1205

[deeman]

I'm trying to set up a SQL Alert to notify me when deadlocks are experienced on our servers.

I've set one up on a SQL2000 instance which works fine; when I generate a deadlock, the alert registers the occurance of the error and fires off an email.

I've set up the same alert on a SQL7 instance which does not register the occurance of the error nor sends the notification email.

Both alerts are identical, as are the scripts i'm using to generate a deadlock.

Any help in diagnosing this problem would be very much appreciated.

Thanks.

[skhanal]

Have you setup the trace flag 3604 to trap deadlock messages?

[deeman]

Yes, ran:

DBCC TRACEON (3604)

The alert still does not register an occurance of the error.

[skhanal]

With DBCC TRACEON(3604) it traces for that particular connection, you have to enable it for all connections using

DBCC TRACEON(3604,-1)

[deeman]

Still no joy. The deadlock error is not being written to the SQL Error Log nor NT Log.

We've discovered that another SQL7 instance we have does write to the log and increment the alert count when a deadlock occurs.

[skhanal]

Try enabling

DBCC TRACEON(3605,-1)

too.

[deeman]

No luck. Does running the DBCC commands effectively 'turn on' error logging...?

[skhanal]

When you run

DBCC TRACESTATUS(-1)

do you see 3604 and 3605 enabled?

[deeman]

Yes, status is set to 1.

[skhanal]

Ok. Let's try enabling 1204 and 1205 also using

DBCC TRACEON(1204,-1)
DBCC TRACEON(1205,-1)

[deeman]

TraceFlag Status
----------- -----------
1204 1
1205 1
3604 1
3605 1

The error is still not being logged.