Posted in 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

Author:

I am Kapil Singh Kumawat working on SQL Server since last 5 years. I am from Jaipur, Rajasthan, India and currently working in Cognizant Technology Solutions as SQL Server Developer. I have a good experience in writing queries, performance tuning, SSIS, SSAS, Power BI, Data migration and database designing. Apart from database I have interest in travelling,watching football and listening music. My blogs are also published on http://www.sqlservercentral.com/blogs/kapil-blogs/

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s