SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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.

Basit's SQL Server Tips

Basit Farooq is a Lead Database Administrator, Trainer and Technical Author. He has more than a decade of IT experience in development, technical training and database administration on Microsoft SQL Server platforms. Basit has authored numerous SQL Server technical articles, and developed and implemented many successful database infrastructure, data warehouse and business intelligence projects. He holds a master's degree in computer science from London Metropolitan University, and industry standard certifications from Microsoft, Sun, Cisco, Brainbench, Prosoft and APM, including MCITP Database Administrator 2008, MCITP Database Administrator 2005, MCDBA SQL Server 2000 and MCTS .NET Framework 2.0 Web Applications.


Leave a comment on the original post [basitaalishan.wordpress.com, opens in a new window]

Loading comments...