Can I enable trace flag 1205 (T-1205) without restarting SQL server service? I normally set this flag as a startup parameter and restart SQL Server service to log the deadlocking info in the SQL Server error log.

However, someone told me the that you can set this flag without restarting a service (by running dbcc traceon (1205, -1). Is this correct? If so how should this be done?

By default this trace flag is activated, trace flag will be in effect until you restart server, or until you deactivate trace flag by using the DBCC TRACEOFF command.

See what BOL specifies:

On a production server, to avoid unpredictable behavior, we recommend that you only enable trace flags server-wide by using one of the following methods:

• Use the -T command-line startup option of Sqlservr.exe. This is a recommended best practice because it makes sure that all statements will run with the trace flag enabled. These include commands in startup scripts. For more information, see sqlservr Application.
  
  
• Use DBCC TRACEON ( trace# [, ....n], -1 ) only while users or applications are not concurrently running statements on the system.
  
  

Trace flags are used to customize certain characteristics by controlling how SQL Server 2005 operates. Trace flags, after they are enabled, remain enabled in the server until disabled by executing a DBCC TRACEOFF statement. In SQL Server, there are two types of trace flags: session and global. Session trace flags are active for a connection and are visible only for that connection. Global trace flags are set at the server level and are visible to every connection on the server. To determine the status of trace flags, use DBCC TRACESTATUS. To disable trace flags, use DBCC TRACEOFF.

Thanks Satya

Can you please let me know if I can run the DBCC TRACEON (1205, -1) command (whilst there are no users connected, and not restarting the SQL service) - whether it will guarantee logging the deadlocking info?

PS. I'm not going to an interview - I need all these for my job - how do you get the experience you've got? - I've always wanted to get this, but my job doesn't challenge me enough. Tthanks.

AFAIK you need to restart the service. Why dont you check the status DBCC TRACESTATUS to know the status? BTB, which version of sql server you have? if it is 2005 , you have another method to troubleshoot the deadlocks

Check

 http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=Troubleshooting%20Deadlock%20in%20SQL%20Server%202005%20

Madhu

Treve

Appreciate your interest on learning the advanced techniques as your job doesn't challenge such situations, best is to practice what you are learned on test server.

There are some system configuration options that requires restart of SQL services in order to take that affect, for that reason I refered the BOL too and I would like to suggest to refer update BOL as a first hand information, as I do that always when I want to answer any complex question here... nothing wrong in that .

BTW, due to the majority of questions you posted in last week it gave me such thought... hope you don't mind asking that wya....