How do I define a policy in SQL Server 2008 to evaluate if a Trace Flag is enabled ?

Someone posted this question in one of the internal forums and I found this interesting so thought of posting the solution. We are not allowed to add new facets in Policy Based Management and I could not find any facet which checks for trace flags.

While researching I found Blogs from Dan Jones (PBM Program Manager) and looked at below post

So to solve the given problem, I create a stored procedure in master database.


USE [master]


create proc [dbo].[TF_Policy]



set nocount on;

CREATE TABLE [dbo].[#tbl_TraceFlagSet] ([TraceFlag] INT NULL ,      [TraceFlagStatus] BIT NULL,      [Global] INT NULL,       [session] INT NULL ) ON [PRIMARY];

insert into #tbl_TraceFlagSet (TraceFlag, TraceFlagStatus, [global], [session]) 

exec ('dbcc tracestatus(-1) with NO_INFOMSGS');

Select count(*) from #tbl_TraceFlagSet;



So this procedure would give 1 if any trace flag is enabled as zero. Now, I can use this in ExecuteSQL to define condition. I named it as 'CheckTraceFlag"


finally, policy would check this condition "CheckTraceFlag"


I have tested the policy and I can see failure once I enable trace flag.