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:
- Create a stored proc in the master database to include the code to turn the trace flags that you wish to be on.
- Run the stored proc so that the trace flags will take effect.
- 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.
- Create a stored proc in the master database to include the code to turn the trace flags that you wish to be on.
- Run the stored proc so that the trace flags will take effect.
- 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 -- 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
-- run the stored proc to turn on the trace flags use master go exec usp_TraceFlagsOn
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