With the help of Trace Flags we can control specific SQL Server characteristics. Unless there is a very good reason, many expert DBAs never set trace flags for entire instance. If you miss to remove discontinued trace flags from SQL Server’s startup parameters, you will see error messages in SQL Server Logs. In this article we will learn how to identify SQL instances that are still using discontinued trace flags.
Collect flags
Execute Script 1 from your central server. For each server name you save in the table TBL1, the script would create a Linked server. It then connects to the newly created Linked Server to create a database “DBA”. Within this database, a table is created. The trace status of that SQL Server is collected and saved in the table. After executing Script 1, execute Script 2 from a new query window. Script 2 creates a Log table. Then using Linked Servers, the TBL2 from the database “DBA” is read and data is saved in the Central Server. You can then filter this log table to find servers that are using flags that were discontinued.
Script 1:
CREATE TABLE TBL1(cservername VARCHAR(200)); CREATE TABLE TBL2 (c_servername VARCHAR(200), c_flag VARCHAR(200), c_status INT, c_global INT, c_session INT ); DECLARE @cmd NVARCHAR(2000); DECLARE @cmd2 NVARCHAR(4000); DECLARE @vsvrname VARCHAR(200); DECLARE @vuser VARCHAR(100); DECLARE @vpwd VARCHAR(100); DECLARE cur1 CURSOR FOR SELECT cservername FROM TBL1; OPEN cur1; FETCH NEXT FROM cur1 INTO @vsvrname; WHILE @@FETCH_STATUS = 0 BEGIN PRINT @vsvrname; SET @vuser = 'linkedserveruser'; SET @vpwd = 'linkedserveruser'; SET @cmd = 'EXEC sp_addlinkedserver @server='''+@vsvrname+''''; PRINT @cmd; EXEC (@cmd); SET @cmd = 'EXEC sp_addlinkedsrvlogin '''+@vsvrname+''', ''false'', NULL, '''+@vuser+''', '''+@vpwd+''''; PRINT @cmd; EXEC (@cmd); SET @cmd2 = 'IF NOT EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N''''DBA'''') begin CREATE DATABASE [DBA] end use [DBA] CREATE TABLE TBL1 ( c_flag varchar(200), c_status int, c_global int, c_session int ) INSERT INTO TBL1 EXEC (''DBCC TRACESTATUS()'')'; SET @cmd = 'EXEC ('''+@cmd2+''') at ['+@vsvrname+']'; PRINT @cmd; EXEC (@cmd); FETCH NEXT FROM cur1 INTO @vsvrname; END; CLOSE cur1; DEALLOCATE cur1;
Script 2:
DECLARE @vsvrname VARCHAR(200); DECLARE @cmd NVARCHAR(2000); DECLARE cur1 CURSOR FOR SELECT cservername FROM TBL1; OPEN cur1; FETCH NEXT FROM cur1 NTO @vsvrname; WHILE @@FETCH_STATUS = 0 BEGIN SET @cmd = 'INSERT INTO TBL1 Select @@servername, * from ['++@vsvrname+'].DBA.DBO.TBL1'; EXEC (@cmd); FETCH NEXT FROM cur1 INTO @vsvrname; END; CLOSE cur1; DEALLOCATE cur1;
Periodic check
You can schedule the Script 1 on each Linked server to periodically check the trace status on the SQL Server. In the SQL job, do not miss to add truncate statement to remove old records from TBL1. A corrupted mdf of master database wouldn’t interrupt connections to the SQL Server. If the script is not able to connect to a Linked server, please do an integrity check on master database.
Author Introduction:
Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including Outlook fix and excel recovery software products. For more information visit www.datanumen.com
Leave a Reply