2

I know to check for live deadlocks

select * from sys.sysprocesses where blocked>0

However, that is only for deadlocks that are current. How do we check for deadlocks from the say the last 24 hours? Note: I want to be able to do this without having to configure on any trace etc - just get the information from a system table. Is that possible?

Thanks.

dublintech
  • 1,519
  • 8
  • 21
  • 26
  • 2
    Your query checking for "deadlocks" is in fact checking for blocked processes instead (which may or may not ever be involved in a deadlock). Is it deadlocks specifically you are interested in (circular wait where one of the processes needs to be killed to resolve)? If so the details of these are recorded in the default extended events session. See my answer here for code to query this – Martin Smith Feb 09 '12 at 11:28
  • Super point. Forgive the simple question but how would I know if a blocked process was involved in a deadlock? Or how would I find out more info about the blocked process? – dublintech Feb 09 '12 at 11:36
  • 2
    If you are getting deadlocks you will be seeing error messages of the form Transaction (Process ID %d) was deadlocked on %.*ls resources with another process and has been chosen as the deadlock victim. Rerun the transaction. are you getting these errors and this is what you are trying to troubleshoot? Or are you interested in general blocking. – Martin Smith Feb 09 '12 at 11:39
  • Start with blocking. There's no point looking at deadlock until I cover everything on blocking first. Thanks – dublintech Feb 09 '12 at 11:43
  • Well I don't think any historic information about this is collected by default. You might want to look at the blocked process report or polling the DMVs and storing historic data using the Management Data Warehouse or SQLNexus. – Martin Smith Feb 09 '12 at 12:35

1 Answers1

2

Either use Trace Flag 1204 and Trace Flag 1222, or use Sql Server profiler to analyse and save deadlock graph information.

See also here

Oleg Dok
  • 3,387
  • 25
  • 29