What is Trace Flag? Enable / Disabling Trace Flag

Trace flag is a directive used to " set specific server characteristics or switch off a perticular behavious" -- is a setting that in some way or another alter the behaviour of various SQL Server functions

Trace flags are used to generate internal information regarding SQL Server's activities, primarily so that Microsoft developers can troubleshoot their code.

Trace flags are enbled in SESSION or GLOBAL levels

How to check whcih trace flags are enabled in the server globally

DBCC  TRACESTATUS(-1) 

Above command will display the status of all trace flags that are currently enabled globally.

How to check whcih trace flags are enabled in the server for current session

DBCC  TRACESTATUS()

Above command will display the list of all trace flags that are enabled for the current session 

You can enable/implement Trace Flags in several ways

• using SQL Server Configuration Manager
• using Registry Editor
• using Query Analyser

Implementing Trace Flag using SQL Configuration Manager 

This is also know as Enabling Trace Flag using –T Startup Option

Adding trace flag in Startup Parameters 

-T startup option to specify that the trace flag be set on during startup. Saparate each trace flag by semi-colon(;). -T startup option enables a trace flag globally 

You can't enable a session level trace flag  by using start up option

  

Implementing Trace Flags using Registry (REGEDIT.MSC)

This is not a recomended method. please avoid using it in production box.

• Goto Run and type regedit.msc and press endter
• Goto below location in registry
• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQL2008\MSSQLServer\Parameters
• Add the trace flag by right click on the Parameters folder and click on New and then String Value
• Provide the name as SQLArg3 and then right click on the key value to modify the value.
• Now provide the value as -T1222 

Implementing Trace Flags using Query Analyser 

Using DBCC TRACEON command you can enable the trace flags.All trace flags can't be enabled using DBCC TRACEON e.g. DBCC TRACEON (835), this can be enabled during the server startup only.

You can enable Session Scope or Global Scope using this command

Session Scope : Using below command we can enable session level trace flag

DBCC TRACEON(1222)

Global Scope :  Using below command we can enable global level trace flag

DBCC TRACEON(1222,-1)

Disabling Trace flag

Remove from start-up parameters 

• Go to SQL Server configuration manager and to sql server properties 
• Go to Advanced Tab and click on drop box of start-up parameter and remove the parameter -T1222
• Restart SQL Server services 

Remove from Registry 

• Go to regedit.exe and 
• Browse HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQL2008\MSSQLServer\Parameters
• Remove the string value which has -T1222
• Restart the SQL Server services 

Remove using Query Analyser 

use below command to turn off session level trace flag

DBCC TRACEOFF(1222)

use below command to turn off global level trace flag

DBCC TRACEOFF(1222,-1)

Caution : 

Do not use trace flags in production environment without testing it on non production environments, trace flags should be used under the guidance of Microsoft SQL Server support.

DBCC CHECKIDENT

-- Create Table Script to test CHECKIDENT

CREATE TABLE DBCC_ReSeed

( ID INT IDENTITY(1,1) NOT NULL,

DateAdded DATETIME NOT NULL )

-- Inserting values to DBCC_ReSeed table

INSERT INTO DBCC_ReSeed(DateAdded) VALUES(GETDATE())

INSERT INTO DBCC_ReSeed(DateAdded) VALUES(GETDATE())

INSERT INTO DBCC_ReSeed(DateAdded) VALUES(GETDATE())

INSERT INTO DBCC_ReSeed(DateAdded) VALUES(GETDATE())

INSERT INTO DBCC_ReSeed(DateAdded) VALUES(GETDATE())

SELECT * FROM DBCC_ReSeed

--- Check the current value which produces the below output… 

DBCC CHECKIDENT ('DBCC_ReSeed', NORESEED)

---  Output like :  Checking identity information: current identity value '5', current column value '5'.

--- Now reset the identity value to 20 so that the next time when we insert data into DBCC_RdSeed table , the value will be 21…

DBCC CHECKIDENT('DBCC_ReSeed', RESEED, 20)

----- Output like :   Checking identity information: current identity value '5', current column value '20'

--  Add another row and check identity value. The row inserted will have a value of 21… 

INSERT INTO DBCC_ReSeed (DateAdded)  VALUES(GETDATE());

SELECT * FROM DBCC_ReSeed

-- Delete data in table and reset to start from 1

DELETE FROM DBCC_ReSeed

DBCC CHECKIDENT('DBCC_ReSeed')

-- After deleting data identity value didn’t reset and the value will be remain same  

---  Output like :  Checking identity information: current identity value '20', current column value '20'.

DBCC CHECKIDENT('DBCC_ReSeed', RESEED, -1)

---  Output like :  Checking identity information: current identity value '21', current column value '-1'.

INSERT INTO DBCC_ReSeed (DateAdded)  VALUES(GETDATE());

INSERT INTO DBCC_ReSeed (DateAdded)  VALUES(GETDATE());

SELECT * FROM DBCC_ReSeed

Last good dbcc checkdb to all databases in sql server

 CREATE TABLE #tempTotal
(
DatabaseName varchar(255),
Field VARCHAR(255),
Value VARCHAR(255)
)
 CREATE TABLE #temp
 (
 ParentObject VARCHAR(255),
 Object VARCHAR(255),
 Field VARCHAR(255),
 Value VARCHAR(255)
 )
 EXECUTE sp_MSforeachdb '
 INSERT INTO #temp EXEC(''DBCC DBINFO ( ''''?'''') WITH TABLERESULTS'')
 INSERT INTO #tempTotal (Field, Value, DatabaseName)
 SELECT Field, Value, ''?'' FROM #temp
 TRUNCATE TABLE #temp';
 ;WITH cte as
 (
 SELECT
 ROW_NUMBER() OVER(PARTITION BY DatabaseName, Field ORDER BY Value DESC) AS rn,
 DatabaseName,
 Value
 FROM #tempTotal t1
 WHERE (Field = 'dbi_dbccLastKnownGood')
 )
 SELECT
 DatabaseName,
 Value as dbccLastKnownGood
 FROM cte
 WHERE (rn = 1)
 DROP TABLE #temp
 DROP TABLE #tempTotal