Category Archives: sql server 2012

Should we really need to setup monitoring to capture deadlocks in SQL Server ???

I’ll like to begin this blog  with short and usual conversation in DBA space !

———————-start conversation——————–

App team: Hi

DBA Team: Hello

App Team: we observe the following error in our application logs –“Transaction (Process ID XX) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction”. Can you please provide the cause of the error?

DBA Team : Those errors are deadlock errors and We are sorry to say that deadlock information would be captured in SQL server only when we set up monitoring in place i.e. using Trace flags or enable SQL Sever profiler/ server side trace.

———————-end conversation——————–

I am not flabbergasted at the above conversation as most of us provide the same response in our own grammatical way. However, the above conversation holds good for Pre SQL 2008 era.

From SQL Server 2008 on-wards, we  can make use a new feature called System_Health Session which is based on Extended Events. It will be ‘On’ seamlessly and gets recreated every time SQL server restarts.

System_Health Session monitors various events and deadlock is one of the event which is captured whenever it occurs.


Below is small demo on how to gather data on SQL 2008 /SQL 20008 R2 and SQL 2012 on-wards. I used SQL 2008 R2 and SQL 2014 for this demo.


a) Using the random script on web simulated deadlock on a demo database called deadlock. The following error is received:


b) System_health session logs/writes a the data on a target called Ring Buffer (Bing for Ring Buffer to know internals of ring buffer). Using the following query we can read the details captured in ring buffer in XML format

select CAST(xet.target_data as xml) from sys.dm_xe_session_targets xet
join sys.dm_xe_sessions xe
on (xe.address = xet.event_session_address)
where = ‘system_health

The XML contains the complete deadlock report similar to Trace flag 1205. The analysis of the XML file is outside the scope of this blog-post.




Microsoft made our lives easier by adding system_health session to GUI. It is very easier to access the system health session data i.e. scroll through Management folder–>Extended Events –> system_health.

The system health session contain two targets -event file and ring buffer. Ring buffer is similar to one discussed above. Event file contain captures a event called xml_deadlock_report which proides the deadlock information in graphical format as shown in below screenshot.




Conclusion: Extended events and system_health session is one of the interesting subjects to dwell into hence recommend to ‘Bing’ on this subject to learn more. Indeed it is better to substitute sql profiler with extended events to capture respective details without much performance overhead on the server. I would like to thank all the SQL server stalwarts who inspired me to share my knowledge through this blog.





Leave a comment

Filed under Deadlock, sql server 2008, sql server 2012

How to find Failed or Hung SQL Server Agent Jobs in the last 24 Hours

Run the below query to find failed/hung jobs in your SQL Server Instance:



SELECT sjh.server as 'Server Name' , as 'Failed Job Name', sjh.message as 'Error Description', sjh.run_date, sjh.run_time as 'Job Run Time/Job Hung Time'
FROM msdb..sysjobhistory sjh
JOIN msdb..sysjobs sj on sjh.job_id = sj.job_id
JOIN (SELECT job_id, max(instance_id) maxinstanceid
FROM msdb..sysjobhistory
WHERE run_status NOT IN (1,4)
GROUP BY job_id) a ON sjh.job_id = a.job_id AND sjh.instance_id = a.maxinstanceid
WHERE    DATEDIFF(hh, CONVERT(datetime,CONVERT(VARCHAR(8),sjh.run_date),12), GETDATE()) <= 24
select job.originating_server as 'Server Name', job.Name as 'Failed Job Name', 'Warning: Job Hung for More than 1 Day.' as 'Error Description',
CONVERT(char(10), activity.run_requested_Date,112) as run_date,
datediff(minute, activity.run_requested_Date, getdate()) as Elapsed
from msdb.dbo.sysjobs_view job
inner join msdb.dbo.sysjobactivity activity on (job.job_id = activity.job_id)
where run_Requested_date is not null
and stop_execution_date is null
and datediff(hour, activity.run_requested_Date, getdate()) >= 24




NOTE: Ideally, you would want to know if a job is hung as soon as possible.

Hence, you can replace the underlined code above with the one below to reduce the time-interval for Hung Jobs from 1 day to 1 Hour:

datediff(minute, activity.run_requested_Date, getdate()) >= 60

Use a Power Shell script/Windows Scheduled Task to have this scheduled & run on multiple SQL Server Instances in a jiffy!

Leave a comment

Filed under sql server 2008, sql server 2012

Difference between a Primary Key and a Unique Constraint – DBA Perspective

I was preparing for an interview (hope my manager isn’t reading this yet) & found this wonderful link SQL Server DBA Interview Questions which had a great question on the difference between a Primary Key and a Unique Constraint.

That question brought in a thought about how this difference between a Primary Key and a Unique Constraint matters to a DBA? (Developer’s headache – not mine was my first opinion, however that quickly changed when I started exploring it.)

For DBAs like me who might NOT remember all the differences, this simple post by Shailendra Chauhan captures the essence of the Difference between Primary Key and Unique Key

Here is the DBA perspective that DBAs would hopefully benefit from {or pay the price if ignored 😉 }

Primary Key creates a Clustered Index internally, where Unique Constraint creates a “Unique, Non-Clustered” Index.

This means you can actually have multiple Unique Constraints in a single table. Simple yet useful to maintain Data Consistency.

For all those Science fans who live by “Seeing is believing”, here’s the screen preview:


Notice that I have created a Unique Constraint “IX_ID” on a Column “ID”. SQL Server has created a “Unique, Non-Clustered” Index with the same name.

Key Takeaway here would be to consider the above difference in how SQL server perceives your Primary key/Unique Constraint. Based on this solid understanding, you can make your database design better!

Cheerio! Hope this article was useful 🙂

PS: Please feel free to add onto your inputs/experience in the below comments section.

Leave a comment

Filed under Keys and Constraint, sql server 2005, sql server 2008, sql server 2012, Uncategorized

What’s in a NULL ?

Let’s take a Sneak Peek inside SQL Server 2012 (this hasn’t changed in versions 2008 & 2005 as well) & find out How SQL Server stores a NULL Value!


Step 1: Create a Table


(Id int null)

Step 2: Insert the below values

insert into TestforNULLs

Step 3: Verify to see what you have inserted

select * from TestforNULLs


Hmmm… We’ve already found out that SQL treats ‘’ {empty value inserted as ‘0’ (zero)}

Step 4: Next enable a SQL Trace for the current session




DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Step 5: Verify as to what traces have been enabled





Step 6: Next, find out where your Data pages are located:

DBCC IND(testbed, TestforNULLs, -1)



Step 7: Copy the First Page ID, here it is ‘328’ & paste it in the below command:

This command will help you see what’s in a SQL Data page.

DBCC PAGE (testbed, 1, 328, 1)



Step 8: Scroll down to the “DATA” section below:


  • Notice that the NULL Bitmap Value has been enabled for the NULL Value with Binary 1 indicating that the first & the only column contains a NULL value.
  • For the Second and Third records which are NOT NULLs, the NULL Bitmap Value is 0.
  • This is how SQL Server identifies a NULL Value.

Hope this was useful piece of Information for you & answers the questions such as –

“How does SQL Server store NULL values?” 

“What’s in a NULL, bro?”

“hmmm… NULL huh?”


Key Learning:

  1.  SQL Server marks NULL Values via its NULL BITMAP
  2. SQL Server stores Integer value 0 (zero) when you pass an empty value
  3. Empty value is NOT equal to NULL Value


Related Reading:

Deep Dive on to Paul Randall’s article on Misconceptions around null bitmap size





Filed under SQL NULL, sql server 2005, sql server 2008, sql server 2012

Useful URLs for SQL DBAs – Foundation

1)      SQL Server Architecture

2)      SQL Server Transaction Log Architecture and Management

3)      About transaction log and its truncation in SQL Server

4)      Truncating the Transaction Log

5)      Checkpoints and the Active Portion of the Log

6)      How to Identify Blocking in SQL Server

7)   Does a full backup truncate the log?

8)   Full Backups & Transaction Logs Backup – There’s Something about SQL!

9) TempDB Related

10) SQL Server Best Practices

11) SQL Server Replication 

Please share your favorite URLs that will be useful for other DBAs.

Category: SQL Server Basics

Leave a comment

Filed under sql server 2008, sql server 2012