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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL 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
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Vitor MontalvãoMSSQL Senior EngineerCommented:
Sherlock, check this MSDN article to find the differences and how to avoid/reduce the number of deadlocks and blocking processes.
0
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.