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.

Improve this question
5
  • 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
    Commented Feb 9, 2012 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
    Commented Feb 9, 2012 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
    Commented Feb 9, 2012 at 11:39
  • Start with blocking. There's no point looking at deadlock until I cover everything on blocking first. Thanks
    – dublintech
    Commented Feb 9, 2012 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
    Commented Feb 9, 2012 at 12:35

1 Answer 1

Reset to default
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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.