Feeds:
Posts
Comments

Archive for March, 2016

Quick one today:

Trace Flags are a unique feature in Sql Server. They allow enabling certain features, that could change the way Sql Server behaves. Generally, these are helpful in troubleshooting any uncommon scenarios or identifying performance bottlenecks.

There are two types of trace flags. Global and Local (session). Global trace flags are enabled globally for the entire instance; Local flags are applicable just to the current session. Some trace flags are global flags (ex: 1204), others are local trace flags; We also have some that could be enabled either globally or locally (Ex : 4199).

CHECK STATUS

To check what TRACE FLAGS are enabled on our instances there are several ways to accomplish this, so here we’ll look at a couple of them. Following scripts allow us to check the status of any status flag; If they are enabled locally (for session) or globally.

--
-- Check if the trace flags are enabled globally
--
DBCC TRACESTATUS(1204, -1)
GO

DBCC TRACESTATUS (1222, -1)
GO

DBCC TRACESTATUS (1117, -1)
GO

--
-- Or run this alternative
--
DBCC TRACESTATUS(1204, 1222, 1117, -1)
GO
Trace Flag Status

Trace Flag Status

ENABLE TRACE FLAGS

Now, use the following code to enable them globally. Trace flags 1204 & 1222 are used to troubleshoot deadlocks. For more information on types of deadlocks, please review this post. And techniques to capture deadlock traces, please review this post.

--
-- Enable Trace Flags globally
--
DBCC TRACEON (1204,-1)
GO

DBCC TRACEON (1222,-1)
GO
Set TRACEON

Set TRACEON

Please note that if the instance is restarted these settings will not be in place after restart. For that we need to enable them during startup, as part of startup parameters.

DISABLE TRACE FLAGS

Similarly, when you are ready to turn them off, use the below script:

--
-- Turn trace flags off globally
--
DBCC TRACEOFF (1204,-1)
GO

DBCC TRACEOFF (1222,-1)
GO
Disable Trace Flags

Disable Trace Flags

 

Hope this helps,

Read Full Post »

In one of the previous posts, we covered different types of deadlocks. In this, we’ll cover one of the tools in understanding the underlying processes involved in a deadlock. There are several techniques, this is one of the least intrusive approaches.

Sql Server provides TRACE FLAGS that allows us to automatically capture a lot of metadata on the deadlock processes.

 

  • Trace flag 1204
  • Trace flag 1222

 

Trace Flag 1204

This trace flag enables capturing the all locks involved in deadlock along with resources; Including the commands being executed by each process.

Trace Flag 1222

Enables capturing the above information in an XML format. This has 3 sections

  • Deadlock victim with its SPID, command being executed, locks on its resources, etc
  • Details on both the processes involved in deadlock with their SPID, commands, locks on resources, etc
  • What resources are locked by each process, etc

 

Note: Usually Trace Flags can be  be set up either globally or locally, but the above trace flags could only be set up globally.

 

Check The Status:

Use the following steps to check the current status of these these flags first:

 --
 -- Check if the trace flags are enabled globally
 --
 DBCC TRACESTATS (1204, -1)
 GO

DBCC TRACESTATS (1222, -1)
 GO

--
 -- Or run this alternative
 --
 DBCC TRACESTATUS(1204, 1222, -1)
 GO
 

 

Check TRACESTATUS

Check TRACESTATUS

Enable The Trace Falgs

Now, use the following code to enable them globally.

 --
 -- Enable Trace Flags globally
 --
 DBCC TRACEON (1204,-1)
 GO

DBCC TRACEON (1222,-1)
 GO
 

 

Set TRACEON

Set TRACEON

Please note that if the instance is restarted these settings will not be in place after restart. For that we need to enable them during startup, as part of startup parameters.

Deadlock Trace Details

When deadlock occurs, Sql Server rollbacks on of them and lets the other process complete successfully. Following error is noticed for the process that was rolledback.

Msg 1205, Level 13, State 51, Line 1
Transaction (Process ID 71) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

In the Sql Server Error Log, more details about each process are noted;

Deadloc Trace Information

Deadlock Trace Information

Disable The Trace Flags

Similarly, when you are ready to turn them off, use the below script:

 --
 -- Turn trace flags off globally
 --
 DBCC TRACEOFF (1204,-1)
 GO
 DBCC TRACEOFF (1222,-1)
 GO
 

 

Disable Trace Flags

Disable Trace Flags

Hope this helps,
_Sqltimes

Read Full Post »

A few days ago, there was a ‘drive full’ alert on one of our production systems on the maintenance drive. Usually, all the maintenance drives sizes are configured based on retention periods. On top of it, we add an extra of 25% size, just to be safe.

So it was a bit surprising, to see space alerts on this drive. On further digging in, we identified that there was some discrepancy between

  • Backup set will expire” setting in “Backup database task” and
  • Delete files older than the following” setting in “Maintenance Cleanup Task”
Delete Files Older Than The Following

Delete Files Older Than The Following

Backup set will expire is set to 5 days (meaning, the file will expire and would be eligible for deleting). But Delete files older than the following is set to 2 days (meaning, deleting files older than 2 days). So you see the obvious conflict.

Backup Set Will Expire

Backup Set Will Expire

We are trying to delete files (after 2nd day), that are not eligible for deletion yet (according to internal calculations).

When you perform backup (FULL, DIFF, LOG, etc), a lot of meta-data is created and stored in MSDB system tables. Some are listed here:

  • backupfile
  • backupfilegroup
  • backupmediaset
  • backupmediafamily
  • backupset

Of these, backupset is of interest to us today. It stores a lot of relevant data about each back, including

  • Backup type (FULL, DIFF, LOG, partial, etc),
  • Backup file name
  • Recovery model
  • Server name
  • Backup file expiration date
  • etc
Backupset Output

Backupset Output

Of these ‘expiration_date‘ is relevant here. The value in this column is generated based on Backup set will expire setting. Until the date is passed, the backup file will not be deleted. So, when the Maintenance Cleanup Task tries to delete older files, though these files are older than 2 days they do not come up in its list of eligible files to delete.

After changing the Backup set will expire setting to 2 days, and a new backup was performed, we need the Expiration_Date column value change appropriately.

Backup Expiration After Changing Settings

Backup Expiration After Changing Settings

Now, we see that the new backup 391371 has older expiration date than the older backup (before settings change). When we run Maintenance Cleanup Task, the newer file will be deleted before the older one (Don’t do this in production; Just pointing out internal behavior).

Interesting stuff !!

 

Hope this helps,

Read Full Post »

Quick one today:

This point came to our attention recently. Its disappointing, but true.

In Sql Server as data is retrieved through queries, the underlying index stats are constantly updated. These are valuable stats for any DBA to make improvements. Adding to that DBAs configure scheduled maintenance tasks to rebuild indexes as needed.

In the past we’ve see that these stats could be reset when we:

Starting Sql Server 2012, looks like those DMV stats are reset every time we rebuild indexes. This is unfortunate and an obvious bug. Hopefully Microsoft will fix it soon.

 

Hope this helps,

Read Full Post »