SQLConfigurationSQLConfiguration





Monitoring SQL  «Prev  Next»
Lesson 10 SQL Server - Trace Flags
Objective Monitor SQL Server using trace flags

Monitor SQL Server with Trace Flags

SQL Server provides trace flags that allow you to view and configure SQL Server’s behavior. While there are many different flags, we will only cover the ones that are used to help monitor SQL Server's performance. When setting a trace flag you provide SQL Server with a number identifying which flag to set. The following list contains the flag number and a description of what the flag does.

-1 Causes the trace flag changes to affect all connection
1204 When a dead lock occurs, SQL Server will print out information about the type of locks participating in the deadlock and the current commands affected
Deadlock: When two connections each hold locks on resources that the other connection wants.
1205 Details information about the command being executed at the time of a deadlock
1704 Prints information about the creation and removal of temporary tables
3604 Sends trace output to the client
3605 Sends trace output to the error log
4030 Prints both the byte and the ASCII representation of the input buffer
4031 Prints both the byte and the ASCII representation of the output buffers
4032 Prints only the ASCII representation of the input buffer


The trace commands that you will probably find the most usefull are the 1204 and 1205. If your application is having deadlocks, these flags will pinpoint exactly what is causing it.

dbcc traceon

The dbcc traceon command is used to turn flags on after SQL Server has started. The syntax is as follows:
dbcc traceon (flag [,flag[ … ] ] )

dbcc traceoff

The dbcc traceoff command is used to turn flags off after SQL Server has started.
dbcc traceoff (flag [,flag[ … ] ] )

dbcc tracestatus

The dbcc tracestatus command is used to see if a flag is on or off. The syntax is as follows:
dbcc tracestatus (flag [,flag[ … ] ] )
For each flag that you pass in to the command, SQL Server will tell you if it is on (1) or off (2). Sample output is:

TraceFlag   Status      
--------    ----------- 

3604        1

106         0

1402        0

1705        0

1205        1

Automatically starting trace flags

You can configure any of the flags to be turned on automatically when SQL Server starts up. To do so you need to add a –T#### or /T#### parameter where #### is one of the flags list in this lesson to the start up configuration, which you learned about if you took the first course in this series. You can set as many trace flags as you want.
The next lesson covers how to use the built-in functions provided with SQL Server to monitor its behavior.