| 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[1] and the current commands affected
|
| 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.
[1]Deadlock: When two connections each hold locks on resources that the other connection wants.