You want the deadlock information to appear on the SQL Server error logs and at the same time suppress those informational messages for successful backups. What can do you? Trace flags to your rescue!

Trace flags can be turned on or off to control the specific server characteristics or behaviors. There are 2 types of trace flags in SQL Server: session and global. Session trace flags are connections specific they apply to the individual connections only. Global trace flags are server level specific they apply to all connections on the server.

The most frequently used trace flag is 1222 which must be turned on to capture deadlock information on the SQL Server error logs. Other trace flags that you may be interested are as follows:

• 3226 to stop logging all successful backup informational messages in the SQL Server Error Logs. Yes. You can turn off those annoying informational messages every time you have a successful backup.
• 1118 for concurrency enhancements for the tempdb database. See reference http://support.microsoft.com/default.aspx?scid=kb;en-us;328551 for more details.
• 4199 to enable query optimizer fixes.  See reference http://support.microsoft.com/kb/974006 for more details.

Trace flags can be set to on or off using the –T startup option to specify the trace flag be set on during startup or using the DBCC TRACEON and DBCC TRACEOFF commands.

The following example sets flag 1222 on by using DBCC TRACEON.

DBCC TRACEON (1222, -1)

To set it off:

DBCC TRACEOFF (1222, -1)

What if you are fortunate enough to manage more than 200 instances of SQL Server and you can’t afford to go to each instance to manually specify the –T startup option on each of the instances and you wish to have more granular control over which trace flags to turn on or off as you wish for specific sets of SQL Server instances? SQL Server startup proc can help. More specifically, instructing SQL Server which stored proc you wish to enable as the startup proc. The following are the implementation steps:

1. Create a stored proc in the master database to include the code to turn the trace flags that you wish to be on.
2. Run the stored proc so that the trace flags will take effect.
3. Set the stored proc to run at SQL Server startup using the system stored proc SP_PROCOPTION. If and when SQL Server restarts, all the trace flags in the startup stored proc will be turned on.

The following steps are an example of how it can be done.

1. Create a stored proc in the master database to include the code to turn the trace flags that you wish to be on.

use master
go
-- create a stored proc to turn on the trace flags
IF OBJECT_ID('usp_TraceFlagsOn') IS NOT NULL
    DROP PROC usp_TraceFlagsOn
GO

CREATE PROC usp_TraceFlagsOn
AS

DBCC TRACEON (1222, -1) -- capture deadlocks in the SQL Server Error Logs
DBCC TRACEON (3226, -1) -- stop logging successful backup informational messages in the SQL Server Error Logs
DBCC TRACEON (1118, -1) -- enable currency enhancements for the tempdb database. http://support.microsoft.com/default.aspx?scid=kb;en-us;328551
DBCC TRACEON (4199, -1) -- enable query optimizer fixes http://support.microsoft.com/kb/974006

2. Run the stored proc so that the trace flags will take effect.

-- run the stored proc to turn on the trace flags
use master
go
exec usp_TraceFlagsOn

3. Set the stored proc to run at SQL Server startup using the system stored proc SP_PROCOPTION. If and when SQL Server restarts, all the trace flags in the startup stored proc will be turned on.

USE master;
GO
-- first set the server to show advanced options
EXEC sp_configure 'show advanced option', '1';
RECONFIGURE
-- then set the scan for startup procs option to 1
EXEC sp_configure 'scan for startup procs', '1';
RECONFIGURE

-- set the stored proc to run at SQL Server start-up
exec sp_procoption N'usp_TraceFlagsOn', 'startup', 'on'

In case you are interested in seeing or verifying the trace flags that are on:

DBCC TRACESTATUS(-1);

To check if there is any stored proc configured for scan for startup:

USE MASTER
GO
SELECT VALUE, VALUE_IN_USE, DESCRIPTION
FROM SYS.CONFIGURATIONS
WHERE NAME = 'scan for startup procs'
GO

REFERENCES:

Trace flags
http://msdn.microsoft.com/en-us/library/ms188396(v=sql.105).aspx

Trace flag 1118
http://support.microsoft.com/default.aspx?scid=kb;en-us;328551

Trace flag 4199
http://support.microsoft.com/kb/974006

SP_PROCOPTION
http://msdn.microsoft.com/en-us/library/ms181720(v=sql.105).aspx

DBCC TRACESTATUS ()
http://msdn.microsoft.com/en-us/library/ms187809(v=sql.105).aspx

Scan for startup procs Option
http://msdn.microsoft.com/en-us/library/ms179460(v=sql.105).aspx