Development

SQL Server – Check the status of active Trace Flags

Hi friends, few days back I was exploring data masking features introduced in SQL Server 2016. I got stuck in some issue and needs to check the information of the trace flags that I enabled. I was searching if there is any system table exists for it or not then I found a DBCC command to check the status about the trace flags which are currently active. So, I thought of writing blog on it so it might help others also in future.

Management command to check for this is –

DBCC TRACESTATUS

It is used to display the status all the trace flags

Syntax from bol

DBCC TRACESTATUS ( [ [ trace# [ ,…n ] ] [ , ] [ -1 ] ] )

[ WITH NO_INFOMSGS ]

Currently there is not active trace flag so let me enabled one for demo –

DBCC TRACEON(1222)

1222 trace flag returns locks and resource that are participating during deadlocks.

As, I have enabled this trace flag now let’s check the status of it using Trace Status command –

DBCC TRACESTATUS(1222)

After executing the above it gives the following output in a result set –

TraceFlag_Status

TraceFlag – Trace Flag number

Status – Status of trace flag (1- Enabled)

Global – Whether trace flag is set globally or not (1 – TRUE, 0 – FALSE).

Session – Whether trace flag is set for session or not (1 – TRUE, 0 – FALSE).

So friends, in this way we can check the status of the trace flag. Have a nice day!!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s