SQL Server blocking

Question

Post reply

SQL Server blocking

gary1

SSC-Insane

Points: 21152
More actions
September 20, 2010 at 12:37 pm

#227031

Hi,
I would like to have a script to monitor blocking. The script should run every 2 mins and if it finds the blocking, it should send an email to DBA with the blocking session details and if the blocking stays more than 10 mins then kill the session that causing the blocking.
I want to have a Fully Automated script and should run from SQLAgent job every 2 mins.
Is there any script that available to do all the above tasks?
How do you guys deal with blocking?
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

homebrew01 SSC Guru Points: 55548 More actions · Answer 1

I think there have been some scripts and threads posted here before.

I would not suggest just arbitrarily killing a job after 10 minutes. You might cause worse problems for your data.

You should look at the processes that are running, find out the cause of the blocking, then look at solving the problem to prevent future blocking.

use SP_WHO2, SP_LOCK and DBCC INPUTBUFFER to get information on what the processes are doing, to help track down the cause.

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

Go to this link for the stored procedures and scripts to run them.

http://blogs.msdn.com/b/psssql/archive/2007/02/21/sql-server-2005-performance-statistics-script.aspx

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

SQLQuest29 SSCrazy Eights Points: 8264 More actions · Answer 3

gmamata7 (9/20/2010)
Hi,
I would like to have a script to monitor blocking. The script should run every 2 mins and if it finds the blocking, it should send an email to DBA with the blocking session details and if the blocking stays more than 10 mins then kill the session that causing the blocking.
I want to have a Fully Automated script and should run from SQLAgent job every 2 mins.
Is there any script that available to do all the above tasks?
How do you guys deal with blocking?
Thanks

Hello,

I would rather ask you that what work have you done in achieving this instead of giving your requirements to SSC. Here we can improve or provide feedback on what you have managed to achieve rather than working on your requirements.

Let us know what you have achieved 🙂

HTH,

Cheers

______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂

gary1 SSC-Insane Points: 21152 More actions · Answer 4

I'm working on it parallelly and I post once I have working script. Thanks

george sibbald SSC Guru Points: 104210 More actions · Answer 5

sort of related to the blog ron posted profiler now contains a 'blocking' event that gives good information. You can tailor the threshold for reporting on blocks.

personally I would use this in conjunction with a script with more detail if blocking is a big problem

http://www.simple-talk.com/sql/sql-tools/how-to-identify-blocking-problems-with-sql-profiler/

---------------------------------------------------------------------

gary1 SSC-Insane Points: 21152 More actions · Answer 6

Currently, we do not have a problem with blocking. It occurs once in while and we are getting alarms for them using 3 rd party tool.

But on development and QA, I need to have a script to do the same as I cannot use the 3 rd party tool because of license cost

Thanks