Dot Net Rules

Using Trace Flags in SQL Server

I have just finished a seminar in SQL Server 2012 and one of the topics I have explained thoroughly was Trace flags.

In this post I will try to shed some light on what trace flags are and how we can use them.

Trace flags are used to change the behavior of SQL Server. Please bear in mind that you should use them with caution.

There are hundred of Trace flags that help us troubleshoot and optimise SQL Server installations.

Before turning trace flags on, make sure you understand fully what they do.

There are global trace flags (server level) that are enabled the entire time SQL Server is running.

There are session trace flags. These trace flags are enabled and disabled at the client session level. Those flags influence only the current session.

I will provide some hands-one demos. In the first one I will look into backups and trace flags. In the second demo I will look into deadlocks and trace flags.

We use DBCC TRACEOFF/DBCC TRACEON flags to enable trace flags at both the global and session level. They do not require to restart the service.

You can also use the SQL Server Configuration Manager, to enable them by using the startup parameter flags.(-Txxx format)

Have a look at the picture below.

I have SQL Server 2012 Enterprise edition installed in my machine. You can try these examples in any edition/version of SQL Server.

I connect to my local instance through windows authentication and I create a new query window.

USE master
GO

DBCC TRACESTATUS(-1);
GO

First I check if there are any trace flags enabled on my server at a global level. In my case there is no a global trace flag.

Type and execute the following t-sql code.

Firstly I recycle the SQL Server error log so I have a brand new error log. Then I create a database backup of the AdventureWorksLT2o12 database. You can use any database you want.

EXEC sp_cycle_errorlog;

BACKUP DATABASE [AdventureWorksLT2012] TO
 DISK = N'C:\sqldata\fulladv.bak'
 WITH NOFORMAT, NOINIT,
 NAME = N'AdventureWorksLT2012-Full Database Backup',
 SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO 20

All the backups by default write detailed information to the SQL Server error log.

When I go again and view the SQL Server error log I see it with lots of entries about the backups.

Have a look at the picture below

I might want to change that behavior and make less entries to the SQL Server error log so I can see easier the real errors.

In a new query window type(copy-paste) the following.

--enable the this flag at the global level

--With this trace flag, we can suppress these log entries
DBCC TRACEON(3226,-1)

--check to see if flags exist at the global level

DBCC TRACESTATUS(-1)

--recycle the error log again

EXEC sp_cycle_errorlog;

BACKUP DATABASE [AdventureWorksLT2012] TO
 DISK = N'C:\sqldata\fulladv1.bak'
 WITH NOFORMAT, NOINIT,
 NAME = N'AdventureWorksLT2012-Full Database Backup',
 SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO 20

I enable the 3226 flag. Then I check to see if this flag is indeed enabled with the DBCC TRACESTATUS command.

Then I recycle the error log and then I run the database backup script again. Now all these entries are not going to be logged into the error log.

Have a look at the picture below.

In this new demo I will show you what trace flags you can enable in order to get deadlock information.

By default deadlock related entries do not go into the SQL Server error log.

We have a deadlock when two separate processes/transactions hold locks on resources that each other needs. Nothing can happen. This situation could go on forever.But one of the transactions is forced by SQL Server to “die”.SQL Server selects a victim and kills the process/transaction. It usually kills the one that is least expensive to roll back. We can also have some control over the process to be chosen to be the victim by setting the deadlock priority by typing  “SET DEADLOCK_PRIORITY LOW”.This option makes SQL Server to choose that session to be the victim when a deadlock occurs.

I will use the Northwind database to create a deadlock. If you need the installation scripts for the sample Northwind database, click here

Type (copy-paste) the following T-SQL statements.

[sourcecode language="sql"]

--Returns the resources and types of locks that are participating in a deadlock
DBCC TRACEON(1222,-1)

--check to see if flags exist at the global level

DBCC TRACESTATUS(-1)

USE Northwind
GO
--open a query window (1) and run these commands
begin tran
update products set CategoryID = 7

use Northwind
GO
--go to query window (2) and run this command

begin tran
UPDATE Suppliers set CompanyName = 'Mycompany'
update products set CategoryID = 12

--execute this line after you execute the statement in the other query window(1) , in the original query window

update Suppliers set CompanyName = 'TheCompany'

[/sourcecode]

First, I enable the 1222 trace flag.Then I check that this flag is enabled.Then I start a transaction in the query window in the current session.

In another query window I type and execute another t-sql statement. Then I returned to the original query window and typed

update Suppliers set CompanyName = 'TheCompany'

After I execute the line above, a deadlock occured

If I go to the SQL Server error log I will see the deadlock info captured in it. Have a look at the picture below.

By all means use trace flags but make sure that you know what each does before you enable them in your SQL Server ecosystem.

Hope it helps!!!