Hi all,

I configured my sql 2005 cluser server (both) with traceflag 1204 and 1222 (Startup Params: "...; -T1204; -T1222") and restartet server.

Client app. reported deadlock errormessage but no deadlock is logged into errorlog?

What did I miss out here ??

(Startup message in Errorlog

This instance of SQL Server last reported using a process ID of 812 at 20.07.2007 17:18:33 (local) 20.07.2007 15:18:33 (UTC). This is an informational message only; no user action is required.
2007-07-20 17:19:03.78 Server      Registry startup parameters:
2007-07-20 17:19:03.78 Server        -d E:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2007-07-20 17:19:03.80 Server        -e E:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2007-07-20 17:19:03.80 Server        -l E:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2007-07-20 17:19:03.80 Server         -T1204
2007-07-20 17:19:03.80 Server         -T1222
2007-07-20 17:19:03.81 Server      SQL Server is starting at normal priority base (=7)....

TIA

acki

See this Tip fyi and also KBA 926070 fyi on such informational messages.

satya

I set the traceflags 1204 and 1222 and restartet sql server.

For my understanding (as it was in sql 2000) if a deadlock occurs it should be logged into sql server errorlog - but in my case it was not.

All I want is if a deadlock happens it should be logged.

I don't want to set up a tracing (as described in your first tip) to catch it. Dono what the 2nd tip should be good for?

TIA

acki 

Still the same behaviour continues in SQL 2005, as per BOL:

When deadlocks occur, trace flag 1204 and trace flag 1222 return information that is captured in the SQL Server 2005 error log. Trace flag 1204 reports deadlock information formatted by each node involved in the deadlock. Trace flag 1222 formats deadlock information, first by processes and then by resources. It is possible to enable both trace flags to obtain two representations of the same deadlock event.

Refer to the updated books online for SQL 2005 for "Detecting and Ending Deadlocks " topic under trace flags --> Deadlock

satya

I know how it should work. The question is why does the logging of a deadlock is not work on my server?

The flags are set, sql server is restartet, deadlock occurs (client get's 1205 error) but is not logged in sql server errorlog ?

Have you looked at bol for information on detecting deadlock and that will write the log by default.

Are you getting deadlocks on client at the moment, if so how long that lock is held?

The deadlock information are not written to errorlog by default. You have to set traceflag 1204 and/or 1222 as a startup param to see information in errorlog.
(that's the way it was with sql 2000 (flags 1204 and/or 3605) and for my understanding BOL this has not changed in 2005).

The question remains: Why are deadlocks not logged into sql server 2005 errorlog even when the flags are set to do so?

You can do two thing to make sure your trace is working fine or not and your application is reporting wrong...

1. You can manually generate a dead lock and see it is writing to sql error log or not.

2. Create an WMI alert to send an alert when you the dead lock occurs...

 Check the url for help.

MohammendU

I generated a deadlock manualy and, as expected, the deadlock was NOT logged in Errorlog.

OK, found the source of the problem.

When entering startup params, sql server cannot handle blanks between params.

Incorrect: "...; -T1204; -T1222 (with blank after semicolon)   -> no effect
Correct :  "...;-T1204;-T1222 (no blank after semicolon)  -> ok

 HTH

acki

Good to the feedback for the problem and hope you are getting desired results now.

Just to add, you can enable the trace flags with the -1 switch/parameter without needing to restart SQL Server (which is not ideal in production environments),

So, you can execute dbcc traceon (1204,1222,-1) which enables the trace flags for all (global) sql server sessions without the need to restart the service. It works for me.

To solve locking and deadlock issues I recommend you try using the tool called SQL Deadlock Detector. it can be downloaded from here: http://lakesidesql.com/downloads/DLD2/2_0_2007_730/DeadlockDetector2_Setup_07-30-2007.zip. It performs lock/deadlock monitoring and logging for you and makes it extremely easy to identify culprits of problematic locks and deadlocks.