Check for deadlocks - best method in SQL server 2012

Hi Experts,

We are running SQL server 2012 on a Windows server running Windows server 2012 standard 64bit in an SQL cluster setup with two server nodes (Both nodes running same O/S  and SQL server version).

We would like to check for deadlocks in the SQL database. Is there a script or method in which you would recommended to do this? and do we need to turn on any flags or enable anything in the SQL server in order to record deadlocks? or in SQL server 2012 is recording deadlocks enabled as standard?

Since there SQL database is reachable on both nodes I presume the deadlocks can be checked from either server node? or would both nodes need to be checked individually?

Users have encountered performance related issues recently in the application that connected to the SQL server and we would like to check for any deadlocks / or at least rule this out as being a cause of the issue if there are not any deadlocks

Thanks
sherlock1Asked:
Who is Participating?
 
Eugene ZConnect With a Mentor Commented:
there are several ways to track deadlocks-- > your code error handler;

sql profiler; -
Analyze Deadlocks with SQL Server Profile
 https://docs.microsoft.com/en-us/sql/tools/sql-server-profiler/analyze-deadlocks-with-sql-server-profiler
--
sql traces  
Dbcc traceon (1204,-1) -.> https://technet.microsoft.com/en-us/library/ms178104(v=sql.105).aspx
DBCC Traceon (1222,-1) -- >Output is captured in the SQL Server error log.

Extended Events ( https://msdn.microsoft.com/en-us/library/bb630354(v=sql.105).aspx )


you should start from MSFT source:

#1 what is Deadlocking?
https://technet.microsoft.com/en-us/library/ms177433(v=sql.105).aspx
#2 Detecting and Ending Deadlocks
https://technet.microsoft.com/en-us/library/ms178104(v=sql.105).aspx 
#3 Handling Deadlocks
https://technet.microsoft.com/en-us/library/ms177453(v=sql.105).aspx
#4 Minimizing Deadlocks
https://technet.microsoft.com/en-us/library/ms191242(v=sql.105).aspx
0
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
Just to be sure, do you really want to check for deadlocks of blocking processes?
I'm asking because people are usually confusing both.
With deadlocks you can have only information that it happened but you can't do nothing after receiving the warning as SQL Server engine will solve that for you.
With blocking processes is different as you usually have an action to perform after receiving the warning message as you need to solve it manually.
0
 
sherlock1Author Commented:
Thanks for your replies

Vitor - hmm checking for blocking processes would be good too - maybe that would be of more benefit. and may well be of more use
Thanks for questioning this. So to clarify do deadlocks resolve themselves and its not possible to manually resolve them?
wheres blocking processes must be resolved manually? any further clarification would be great

Eugene - Thanks for the information and those weblinks which are very useful

Are you saying in order to see deadlocks I have to run the below to turn on logging the events? or is this only needed if I want to run SQL traces? and its possible to see deadlocks with other methods without turning these traces on?
Dbcc traceon (1204,-1)
DBCC Traceon (1222,-1)

Thanks
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
Sherlock, check this MSDN article to find the differences and how to avoid/reduce the number of deadlocks and blocking processes.
0
 
Eugene ZConnect With a Mentor Commented:
you can turn off trace 1222 anytime

however, for Azure (probably not you case ) you can try to use this query  ( more from https://blogs.msdn.microsoft.com/sqldatabasetalk/2013/05/01/tracking-down-deadlocks-in-sql-database/)
SELECT * FROM sys.event_log


WHERE database_name like 'yourdb'


AND event_type = 'deadlock
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Recommendation to close this question by accepting the above comments as solution.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.