SQL Server Performance

Deadlock not logged ??

Discussion in 'SQL Server 2005 General DBA Questions' started by acki4711, Jul 31, 2007.

  1. acki4711 Member

    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 ServerMSSQL.1MSSQLDATAmaster.mdf
    2007-07-20 17:19:03.80 Server -e E:Microsoft SQL ServerMSSQL.1MSSQLLOGERRORLOG
    2007-07-20 17:19:03.80 Server -l E:Microsoft SQL ServerMSSQL.1MSSQLDATAmastlog.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)....
  2. satya Moderator

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

    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?
  4. satya Moderator

    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
  5. acki4711 Member

    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 ?
  6. satya Moderator

    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?
  7. acki4711 Member

    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?
  8. MohammedU New Member

    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.
  9. acki4711 Member

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

    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
  11. satya Moderator

    Good to the feedback for the problem and hope you are getting desired results now.
  12. SQLSolutions New Member

    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.

Share This Page