Essential Trace Flags for Recovery & Debugging

The following trace flags are essential for a variety of recovery scenarios. The use of trace flags allow the DBA to gain a finer granularity of control on the DBMS not normally given. Following are the list of important trace flags:

• T260 – Show version information on extended stored procedures
• T1200 – Prints lock information (process ID and lock requested)
• T1204 – Lock types participating in deadlocking
• T1205 – Detailed information on commands being run at time of deadlock
• T1206 – Complements 1204.
• T1704 – Show information about the creation/deletion of temporary tables
• T3502 – Prints information about start/end of a checkpoint
• T3607 – Skip auto-recovery for all instance databases
• T3608 – As above, except master database
• T3609 – Skip creation of the tempdb database
• T4022 – Bypass master database stored procedure that run on instance start up
• T7300 – Get extended error information on running a distributed query
• T7502 – Disable cursor plan caching for extended stored procedures

Remember that each flag has its own –T<trace#>. Do not try and use spaces or commas for multiple flags and a single –T command.

Always review trace output in the SQL Server error log to ensure start up flags has taken effect.

There are several ways to enable or disable a trace flag in SQL Server. Maybe the easiest way is to do it in the Sql Server Configuration Manager. In the Configuration Manager you then go to the properties of the SQL Server service (default is MSSQLSERVER). In the Advanced tab, select the StartUp parameters and add e.g ;-T4616 at the end and then click on OK button:

Now we have added the trace flag. It will be enabled as soon as we restart the SQL Server service.