Have you ever tried to figure out what traces are currently active on your SQL Server? Well recently I had to do it and it was not that trivial task. However a bit of looking around helped me. As you know trace flags are enabled on session and global level.
- Enabling and disabling trace flags on session level – the simplest way is to issue:
- DBCC TRACEON(<trace flag>)
- DBCC TRACEOFF(<trace flag>)
- Enabling and disabling trace flag globally
- Adding the flag with –T switch to the startup parameters of the SQL Server Service
- Execute DBCC TRACEON(<trace flag>,-1) and DBCC TRACEOFF(<trace flag>,-1). The option –1 is specifying that the trace flag should be enabled/disabled on global level. Also note that if you do not specify this option when you try to turn off a global trace flag, SQL Server will not disable it but will try to disable the session one
- Checking for active trace flags on the instance
- DBCC TRACESTATUS – this command will show you a table with all active traces and if they are enabled on global or session level
Note that if you execute this command and the only thing you get is “DBCC execution completed. If DBCC printed error messages, contact your system administrator.” then there are no enabled trace flags.
Complete reference for trace flag commands you can find below:
And all trace flags available: