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 http://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.
