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 https://blogs.msdn.com/sqlpbm/archive/2008/07/03/executesql.aspx

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

USE [master]

GO

create proc [dbo].[TF_Policy]

as

begin

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;

end

GO

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.