an automatic way to detect and report deadlock

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.
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
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.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
marrowyungSenior Technical architecture (Data)Author Commented:
"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
marrowyungSenior Technical architecture (Data)Author Commented:
I need the deadlock event to be report ASAP and know the root cause by that !
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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

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:
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
marrowyungSenior Technical architecture (Data)Author Commented:
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
marrowyungSenior Technical architecture (Data)Author Commented:
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
marrowyungSenior Technical architecture (Data)Author Commented:
but that one do not email use the deadlock informaton to us.
0
Scott PletcherSenior DBACommented:
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
marrowyungSenior Technical architecture (Data)Author Commented:
"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
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
marrowyungSenior Technical architecture (Data)Author Commented:
I know what you mean, but the Extended events tab in the managment tab don't show up, any idea why ?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Extended Events tab isn't available in SSMS 2008R2 but there's an add-in from CodePlex if you want to install it.
0
marrowyungSenior Technical architecture (Data)Author Commented:
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
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
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

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.