How to Find SQL Server Instances Using Discontinued Trace Flags

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