Last week I published below blog where I talked about a trace flag. Backup Randomly Failing with Error 112 (There is not enough space on the disk.) One of the readers commented, whether he needs to enable trace flag every time SQL Server is restarted? I realized that I missed mentioning that it can also be enabled as startup parameter which can survive even after SQL Server restart. This blog would cover the basics about trace flag in SQL Server. That comment on the blog was a great reminder for me not to assume people know everything. I am sure that is the only way I started learning these and hence this blog has survived for so long.
What is a trace flag?
Trace flag in SQL Server is to change certain behavior. You can think of them as an “IF” condition in SQL Server. One of the most common heard/used trace flag is 1222 – used for deadlock graph printing in ERROLROG. You can imagine it as below.
If (Trace_flag_1222_enabled == TRUE)
{
Print_Deadlock_graph_in_ERRORLOG()
}
How to enable them?
Trace flag can be enabled by two different ways.
- Startup Parameter:
We need to add T (stands for Traceflag) in front of the number and add as a startup parameter via the SQL Server Configuration Manager.
If you are using a version below SQL Server 2012 then we need to add ;-T (semicolon hyphen capital T) via the SQL Server Configuration Manager.
- Using DBCC TRACEON statement
Many trace flags can be enabled and disabled using this. Here is the example for 1222
DBCC TRACEON(1222,-1)
Second parameter defines the scope of the trace flag. If value is 0 or not supplied it enabled only for the session where the query was run.
If value if specified as -1 then it would be enabled for all sessions on the SQL instance.
How to check which flag is enabled?
DBCC TRACESTATUS can be used to know if trace flag is enabled.
How to disable them?
DBCC TRACEOFF can be used to disable the trace flag which is currently enabled.
Here is the quick script to review everything we have learned above
DBCC TRACESTATUS GO DBCC TRACEON(1222,-1) GO DBCC TRACEON(3604) GO DBCC TRACESTATUS GO DBCC TRACEOFF(1222,-1) GO DBCC TRACESTATUS GO
Which are other common trace flags you have used for production purpose? Please leave a comment and I will be happy to compile the answers and post it together on the separate blog post.
Reference: Pinal Dave (https://blog.sqlauthority.com)
