Query to get the details of Blocking and deadlock occurred for the Day

All replies

• 

  

  0

  Sign in to vote

  You can use sys.dm_exec_requests for that

  see

  http://sqlworks.blogspot.in/2012/10/deadlock-and-blocking-in-sql-server.html

  http://www.mssqltips.com/sqlservertip/2732/different-techniques-to-identify-blocking-in-sql-server/

  https://sites.google.com/site/saifsqlserverrecipes/sql-server-identify-blocking-deadlocks-and-troubleshooting

  ---

  Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

  Monday, August 18, 2014 9:42 AM

  Answerer
• 

  

  0

  Sign in to vote

  You havent specified which version of SQL you are using which makes it difficult to give a solution. Assuming its latest versions , by default SQL Server (in any versions) doesnt track blocking information.

  You need to run some kind of queries/traces to capture blocking. The same goes with Deadlocks where majority of the DBA's enable trace flag 1222/1205 when they suspect deadlocks happening.

  Check this link - http://dba.stackexchange.com/questions/10644/deadlock-error-isnt-returning-the-deadlock-sql/10646#10646

  This link gives code to get historic deadlock information. I havent used it , I just googled to get that.

  That being said if you are looking for something to capture for the future check the below links.

  Check these links on how to setup extended events to capture deadlock and blocking.

  http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/12/21/an-xevent-a-day-21-of-31-the-future-tracking-blocking-in-denali.aspx

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

  http://blogs.technet.com/b/mspfe/archive/2012/06/28/how_2d00_to_2d00_monitor_2d00_deadlocks_2d00_in_2d00_sql_2d00_server.aspx

  HTH

  ---

  Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

  • Proposed as answer by Shanky_621MVP Monday, August 18, 2014 10:05 AM
  • Marked as answer by tracycai Monday, August 25, 2014 10:50 AM

  Monday, August 18, 2014 9:45 AM

  Answerer
• 

  

  0

  Sign in to vote

  Hi,

     I need a query to get the details of blocking and deadlock occurred for the day.

  What version of SQL Server you are using if it is SQL Server 2008 and above use below query to get list of deadlocks

  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

  For 2005 enable trace flag 1222 this will send deadlock in errorlog. So keep checking it.

  dbcc traceon(1222,-1)

  Blocking can be seen by

    select * from sys.dm_exec_requests where blocking_session_id <>0

  
  

  ---

  Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.
  
  My TechNet Wiki Articles

  
  

  • Proposed as answer by tracycai Tuesday, August 19, 2014 1:34 PM
  • Edited by Shanky_621MVP Tuesday, August 19, 2014 9:43 PM
  • Marked as answer by tracycai Monday, August 25, 2014 10:50 AM

  Monday, August 18, 2014 10:07 AM