T-SQL Tuesday #130: Automate Trace Flag Consistency

September 8, 2020

I thank Elizabeth Noble (blog|twitter) for hosting T-SQL Tuesday #123. The topic for this month is Automate Your Stress Away. I also want to acknowledge Adam Machanic’s vision behind this project. I learned a lot from some of the #TSQL Tuesday’s and participated in a few. Thank you, Steve Jones (blog|twitter), for coordinating this program. If you are interested to see previous topics, visit here.

At my current work, being a small team and not using any third party software for managing SQL Servers, we pretty much automate everything. Primarily once we identify something that we had to fix a few times manually, we attempt to automate that. I know that I did not define “few times”. It all depends on how frequently it is happening, the impact of the same happening again, and what other tasks we have in hand. Sometimes we will add it to our parking lot/wish list. Other times we will tackle it right away.

Recently during a SQL Server upgrade, I noticed some inconsistency with trace flags between production and non-production instances. Investigating further also found a similar inconsistency between Always On Availability Group replicas.

My team members fixed this issue by leveraging startup stored procedures. If you do not know how startup stored procedures work, please read these articles:

• sp_procoption (Transact-SQL)
• Configure the scan for startup procs Server Configuration Option
• SQL SERVER – Quick Introduction to Startup Procedures by Pinal Dave

For trace flags that we want to implement in all SQL Servers, were hardcoded in the startup stored procedure. For example, TF 3226:

[By default, every successful backup operation adds an entry in the SQL Server error log and in the system event log. If you create very frequent log backups, these success messages accumulate quickly, resulting in huge error logs in which finding other messages is problematic.

With this trace flag, you can suppress these log entries. This is useful if you are running frequent log backups and if none of your scripts depend on those entries.

Scope: global only]

https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql?view=sql-server-ver15

Checks were made for major SQL Server versions and compatibility level of databases for certain Trace Flags.

DECLARE @Version numeric(18,10);

/*****************************************************************
Get SQL server version
*****************************************************************/
SET @Version = CONVERT(int, SERVERPROPERTY(‘ProductMajorVersion’));
	

---CHECK IF there is no any databases running under 130 & version is 13 or more
IF (@Version >=13 AND NOT EXISTS(SELECT 1 FROM sys.databases WHERE compatibility_level<130))
BEGIN
--Put your code here
SELECT GETDATE();--Dummy code
END;

For instance, specific trace flags, we created a staging table. The startup stored procedure will deploy the trace flags based on the configuration settings from this table.

/************************************************************
Create DBA table in master database is not already exists
************************************************************/
IF OBJECT_ID('Master..DBA_TraceFlags') IS NULL
	CREATE TABLE DBA_TraceFlags
	(
		TraceFlag int PRIMARY KEY,
		Status bit,
		Global bit,
		Session bit
	)

We already had another set of checks that keep availability replicas in sync. Added trace flag in that suite to keep availability replicas trace flags in sync.