marrowyung
asked on
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.
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.
ASKER
"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?
good question, SQL server 2008 R2 with SP2 + CU4 !
one thing, do you see any CU of that release can handle deadlock better?
ASKER
I need the deadlock event to be report ASAP and know the root cause by that !
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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 ?
ASKER
one more question, from that link, what is this for:
create deadlock report ? in XML ?
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 DESCcreate deadlock report ? in XML ?
ASKER
but that one do not email use the deadlock informaton to us.
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.
ASKER
"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 ?
but I don't see the extend events tab, any idea on my SQL server 2008 R2 ?
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
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
ASKER
I know what you mean, but the Extended events tab in the managment tab don't show up, any idea why ?
Extended Events tab isn't available in SSMS 2008R2 but there's an add-in from CodePlex if you want to install it.
ASKER
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
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
If you are using SQL Server 2012, then I would recommend Extended Events.