Important Trace Flag that Every Expert DBA Should Know

I have recently co-presented with Victor Isakov (from SQL Server Solutions) at the Sydney SQL Server User Group on “Important Trace Flag that Every Expert DBA Should Know. The presentation went quite well and I learnt more things that I could imagine from co-presenting.

Some of the trace flags we discussed were:

1204

This trace flag lets SQL Server to log detailed deadlock information to SQL Server Error Log in text format. In SQL Server 2008, this trace flag is only available at the Global Level (i.e. applies to the SQL Server instance). In my experience, it is worth turning this trace flag on, only for debugging purposes. A wealth of information on Deadlocks can be found here: http://msdn.microsoft.com/en-us/library/ms178104.aspx

1222

SImilar to trace flag 1204, this trace flag lets SQL Server to log detailed deadlock information to SQL Server Error Log in XML format.

3226

In an environment where database backup is frequently performed, it is a good idea to turn trace flag 3226 on, to supress an entry to the SQL Server Error Log for each backup.  This is beneficial as the bigger the log, the harder it is to find other messages.  However, you will need to ensure that none of your scripts or systems rely on the backup entry detail on the SQL Server Error Log.

There are more trace flags that are covered in the presentation.  Above are the basic ones that every DBAs should know and would one way or another be very helpful in running production environment.

The presentation will be run again in a few weeks time (13 October 2011) at PASS Summit 2011 in Seattle!  I’m certainly looking forward to co-presenting it again with Victor Isakov (SQL Server Solutions).

 

 

Edit (25 Feb 2012): As requested, below is a sample of a stored procedure that can be executed at the database server start up to enable certain trace flags. Please ensure you understand and test the trace flags before you turn them on, especially in Production environment.  Special thanks to Victor Isakov for providing the sample code.  This stored procedure was covered at the PASS Summit 2011 presentation and the slide deck is downloadable from here.

USE [master]
GO

CREATE PROC [dbo].[EnableTraceFlags]
-- Author  : Victor Isakov
-- Company : SQL Server Solutions (http://www.sqlserversolutions.com.au)
-- Purpose : Enable global trace flags upon SQL Server startup.
-- Notes   : Need to execute sp_procoption to enable this stored procedure to autoexecute
--           whenever SQL Server instance starts:
--           EXEC sp_procoption 'dbo.EnableTraceFlags', 'startup', 'true'
-- Bugs    : None
-- Version : 1.0
-- History : 
-- DATE       DESCRIPTION
-- ========== ==================================================
-- 11/04/2011 Version 1.0 released.
AS
DBCC TRACEON (4199, -1);
-- Enable Query Optimiser fixes (http://support.microsoft.com/kb/974006)
DBCC TRACEON (1222, -1);
-- Write deadlocks to errorlog (BOL)
DBCC TRACEON (3226, -1);
-- Supress successfull backup messages (BOL)
GO

EXEC sp_procoption N'[dbo].[EnableTraceFlags]', 'startup', '1'
GO