DB Blocking

Last Post 06 Apr 2010 10:56 AM by J013-B. 7 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
sql-tips
New Member
New Member

--
16 Mar 2010 10:51 AM
Is it possible to have an alert if database is blocking. We have one application using lot of update statements that caused the database blocking. After killing that session everything is ok. Whenever that blocking occurs need an alert. Thanks for any help.
rm
New Member
New Member

--
17 Mar 2010 05:09 AM
You can get blocking spid from sys.sysprocesses, schedule a job to query it and send mail if has blocking.
gunneyk
New Member
New Member

--
18 Mar 2010 05:06 PM
Just want to add to rms comment that you need to track these so you can decide if the blocking is excessive or not. Blocking is normal so you dont want to alert or kill spids on simple blocking.
sql-tips
New Member
New Member

--
29 Mar 2010 06:57 AM
Thank you for your reply. How do I know blocking is normal or excessive. Any useful link is appreciated.
gunneyk
New Member
New Member

--
29 Mar 2010 05:56 PM
Well that is app dependant. Normally blocking for just milliseconds is expected. Blocking for seconds is not. But again you have to know what it is doing and why. If it blocks for too long chances are you are missing an index and killing the spid is not the answer. Fixing why it is blocking is.
J013-B
New Member
New Member

--
05 Apr 2010 01:11 PM
Just to supplement what has already been posted, one thing you definitely want to look out for if you are monitoring for blocks is deadlocks. Check to ensure that two processes are not blocking each other from completing.
rm
New Member
New Member

--
06 Apr 2010 03:27 AM
Sql will kill one process if there's deadlock.
J013-B
New Member
New Member

--
06 Apr 2010 10:56 AM
Correct, but it will not prevent them from happening in the future. If one is identifying problems such as this then feedback can be given back to developers.
You are not authorized to post a reply.

Acceptable Use Policy