Take the 2-minute tour ×
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.

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.

share|improve this question
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 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? –  dublintech Feb 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 Smith Feb 9 '12 at 11:39
    
Start with blocking. There's no point looking at deadlock until I cover everything on blocking first. Thanks –  dublintech Feb 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 Smith Feb 9 '12 at 12:35
add comment

1 Answer

up vote 2 down vote accepted

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

See also here

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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