Thursday, February 25, 2010

How to enable the trace flags when the server restarts ?

Scenario:  Recently, I was asked by my team lead to enable the trace flags esp. 1204 and 1205 which are deadlock trace flags and they should be in effect after the server restarts.

Approach:  The idea as seems much simple and straight forward. My first approach was to do

DBCC TRACEON (1204, 1205, -1)  -- here –1 is to enable the trace flags globally

                                                 --i.e. not just at the session level

But this will be in effect till the SQL server service is running and once it is restarted the trace flags are no longer in effect.

To overcome above problem, the trace flags can be enabled when the SQL server service starts. This can be done using SQL server Configuration Manager as show below:

SQL server Configuration Manager -> SQL server 2005 services -> SQL server –> Properties -> Advanced -> startup parameters -> ;-T1204;-T1205


The only downside is that we need to restart the service for the changes to take affect !

A quick check if the changes are in affect is to run in SSMS Query window :


The result should be:

TraceFlag Status Global Session

--------- ------ ------ -------

1204          1       1        0

1205          1       1        0

(2 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.



No comments:

Post a Comment

This blog is for SQL server and ArcSDE administration. Thanks for visiting !

Disclaimer: The contents provided on this blog are as is and the poster assumes no responsibility in case of any loss of data. Please test thoroughly the solution or codes provided in your environment.