Thursday, January 13, 2011

documented/undocumented Trace flags

Trace Flag 3231 (same as 2000): When set, BACKUP LOG with TRUNCATE_ONLY and BACKUP LOG with NO_LOG do not allow a log backup to run if the database’s recovery model is FULL or BULK_LOGGED.

Trace Flag 3031 (new in 2005): When set, BACKUP LOG with TRUNCATE_ONLY and BACKUP LOG with NO_LOG run as a CHECKPOINT – regardless of recovery model.

Skip Automatic Recovery (3607)
Trace flag 3607 skips the recovery of databases on the startup of SQL Server and clears the TempDB. Setting this flag lets you get past certain crashes, but there is a chance that some data will be lost.


Skip Automatic Recovery Except Master (3608)
This trace is similar to 3607, but the TempDB in this case is not cleared and only the master database is recovered.


Log Record for Connections (4013)
This trace flag writes an entry to the SQL Server error log when a new connection is established. If you set this option, your error log can fill up quickly. For each connection that occurs, the trace flag writes two entries that look like this:

Login: sa saSQL Query Analyzer(local)ODBCmaster, server
process ID (SPID): 57, kernel process ID (KPID): 57.
Login: sa XANADUsaSQL Query Analyzer(local)ODBCmaster,
server process ID (SPID): 57, kernel process ID (KPID): 57.


Network Database Files (1807)
SQL Server will not allow you to create a database or log file on a networked drive by default. If you attempt to do this, you receive error 5105, which states "Device Activation Error." The 1807 trace flag provides a workaround for this restriction, and you can create database files on a mapped drive or UNC path


Just found two more Trace flags:

3213 - outputs the Backup Buffer configuration information.
3014 - outputs additional information about Backup and File operations
3226 - suppress the backup log entries in the errorlog

For some interesting trace flags which are also hotfixes, check the below link:
http://support.microsoft.com/kb/974006

We all are aware of deadlock trace flags:
here they are from MSDN
1204:
Returns the resources and types of locks participating in a deadlock and also the current command affected

1222
Returns the resources and types of locks that are participating in a deadlock and also the current command affected, in an XML format that does not comply with any XSD schema.

1211:
by enabling this trace flag, lock escalation will be disabled based on memory pressure or the number of locks. So all the page and ro level locks won't be escalated to table level and these could potentially lead to out of locks errors.
1224: 
this will disable lock escalation based on number of locks only. lock escalation can still happen if there is memory pressure.
this will happen only if the amount of memory used by the locks exceed
1) 40% of database engine memory (if locks parameter is set to o in sp_configure)
2) 40% of lock memory

2528
Disables parallel checking of objects by DBCC CHECKDB, DBCC CHECKFILEGROUP, and DBCC CHECKTABLE

4616
Makes server-level metadata visible to application roles.

7806
Enables a dedicated administrator connection (DAC) on SQL Server Express. By default, no DAC resources are reserved on SQL Server Express.

There are hundreds of them and I just discovered from the below site:
http://social.technet.microsoft.com/wiki/contents/articles/13105.trace-flags-in-sql-server.aspx

No comments:

Post a Comment