How to detect and resolve deadlock situations in SQL Server

Hi,

I have couple of question regarding SQL Server:

1. How can I detect deadlock sitation in SQL Server and can I resolve them automatically?

2. We run SQL jobs on a definite schedule. Is there a way I can create a report of selected SQL jobs, time taken by them to complete execution, failure if any, and number of records affected.

Thanks !
pratz09Asked:
Who is Participating?
 
8080_DiverConnect With a Mentor Commented:
Question 1:
If it is a true deadlock, SQL Server will detect it and automatically resolve it by killing all but one of the processes involved in the deadlock.  The trick is, SQL Server will decide who lives and who dies. ;-)

Question 2:
You might want to look into Pragmatic Works' bi-Xpress tool.   It can set up the audit logging for all tasks in SSIS packages in a way that will let you report on the information that you indicate you want.
0
 
pratz09Author Commented:
Thanks a lot !

May be something is wrong with how our systems are set up, but SQL Server takes long time to finally detect that there is a deadlock. Can I programmatic-ally find out if there is a deadlock and instead of killing the process, suspends one and resume it later ?

Thats a great tool. WIll be very useful. How Can I report same things for Stored Procedures ? I looked at SQL Profiler, its close but couldn't find the exact information I need.

Thanks again !
0
 
8080_DiverConnect With a Mentor Commented:
Well, you might also want to check out Confio's Ignite tool.  It monitors database (without putting nearly the load of SQL Profiler) and provides all manner of interesting information about the top 50 worst performing queries (whether within an SP or not).

As for SQL Server taking a while to recognize the deadlock, that is true . . . SQL Server tries to let the queries sort things out for themselves before intervening and killing one (or more) of them.  However, by the time you would be able to identify that there was a deadlock, it would probably be too late to select a query to suspend, even if you could suspend one selectively.
0
 
pratz09Author Commented:
I agree. Thank you !
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.