Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

an automatic way to detect and report deadlock

Posted on 2014-09-30
14
130 Views
Last Modified: 2015-01-07
Dear all,

right now we have implemented the trace flag 1222 and 1224 for the deadlock detection but it seems that from time to time that SQL didn't handle deadlock in a efficive way, we want to see why!

any better way other than the SQL profiler's deadlock graphic feature, which only on when SQL profile is on and running?

we need a better report on this.
0
Comment
Question by:marrowyung
  • 8
  • 3
  • 2
  • +1
14 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40351715
Can you confirm which version of SQL Server you have. You have put this under SQL Server 2005 and SQL Server 2008.

If you are using SQL Server 2012, then I would recommend Extended Events.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40351725
"Can you confirm which version of SQL Server you have"

good question, SQL server 2008 R2 with SP2 + CU4 !

one thing, do you see any CU of that release can handle deadlock better?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40351726
I need the deadlock event to be report ASAP and know the root cause by that !
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 24

Accepted Solution

by:
Phillip Burton earned 250 total points
ID: 40351732
Whilst Extended Events in SQL Server 2008, it may be a good route to go down.

This web page gives some information: http://blogs.msdn.com/b/sqlserverfaq/archive/2013/04/27/an-in-depth-look-at-sql-server-memory-part-2.aspx 

If this solution is not appropriate for your needs, please could you expand on what your needs are.
0
 
LVL 48

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 total points
ID: 40351804
one thing, do you see any CU of that release can handle deadlock better?
There's no miracle solution that any DMBS engine will handle a deadlock better. You can have alerts but that will only says you that a deadlock happened and the engine chose a connection as victim and killed the process.

Deadlock it's a clue for bad programming or bad database design model that let's two concurrent transactions performing table actions almost at same time. Search for massive data operations and transactions without a commit or rollback on it.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40351853
I just read that but I don't see the extended event from "Management" tab, why is it ? any version for SQL server 2008 R2 ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40351854
one more question, from that link, what is this for:

Use Master 

SELECT

       xed.value('@timestamp', 'datetime') as Creation_Date,

       xed.query('.') AS Extend_Event

FROM

(

       SELECT CAST([target_data] AS XML) AS Target_Data

       FROM sys.dm_xe_session_targets AS xt

       INNER JOIN sys.dm_xe_sessions AS xs

       ON xs.address = xt.event_session_address

       WHERE xs.name = N'system_health'

       AND xt.target_name = N'ring_buffer'

) AS XML_Data

CROSS APPLY Target_Data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(xed)

ORDER BY Creation_Date DESC

Open in new window


create deadlock report ? in XML ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40351858
but that one do not email use the deadlock informaton to us.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40353251
You can add an Alert to notify you of a deadlock or run a job.  You could have that job notify you and capture data about the deadlock.  I use trace flag 1222 and some code to parse its data, but you could use extended events if you prefer.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40358906
"but you could use extended events if you prefer"

but I don't see the extend events tab, any idea on my SQL server 2008 R2 ?
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40358909
Unfortunately, it's more complicated in SQL Server 2008 R2 than in SQL Server 2012.

My previous weblink gives you the information: http://blogs.msdn.com/b/sqlserverfaq/archive/2013/04/27/an-in-depth-look-at-sql-server-memory-part-2.aspx
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40358912
I know what you mean, but the Extended events tab in the managment tab don't show up, any idea why ?
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 40358925
Extended Events tab isn't available in SSMS 2008R2 but there's an add-in from CodePlex if you want to install it.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40359071
OKOK>

tks but your link doesn't show out why but it also sad working for SQL server 2008 :

"using extended events in SQL Server 2008 and later"

so that's why I am interesting.

but that one don't send our email anyway,

ScottPletcher,

you can show me an example how to make use of the information and sent an email
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question