[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 526
  • Last Modified:

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 !
0
pratz09
Asked:
pratz09
  • 2
  • 2
2 Solutions
 
8080_DiverCommented:
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_DiverCommented:
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now