Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

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 SmithFeb 9 '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?
–
dublintechFeb 9 '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 SmithFeb 9 '12 at 11:39

Start with blocking. There's no point looking at deadlock until I cover everything on blocking first. Thanks
–
dublintechFeb 9 '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 SmithFeb 9 '12 at 12:35