microsoft.public.sqlserver.notificationsvcs
[Top] [All Lists]

Re: Notification when Locked Process is Blocking

Subject: Re: Notification when Locked Process is Blocking
From: Joe Webb
Date: Tue, 25 Apr 2006 06:28:29 -0500
Newsgroups: microsoft.public.sqlserver.notificationsvcs
Hi Gareth - 

For SQL Server 2000 or 2005, the sp_who system stored procedure will
return information about each user connected to the SQL Server
instance, including whether it's blocking. For SQL Server 2005 you can
use one of the new DMVs (Dynamic Management Views) to get the same
information. You could build a SQLNS instance around information,
*but* that seems quite excessive. (Plus blocking for very short
periods of time is part of what makes the DBMS multi-user so you'd
need to filter out the normal blocking and look for only the extended
blocking or deadlock situation you're describing.)

I'd recommend checking into using Alerts, Profiler, etc for your
application rather than SQLNS. Or better yet, identify the underlying
cause and prevent the continual blocking altogether.

HTH...

-- 
Joe Webb
SQL Server MVP
http://www.sqlns.com


~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/tg/detail/-/0972688811 

I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)


On Tue, 25 Apr 2006 03:04:02 -0700, Gareth
<[email protected]> wrote:

>Hello
>
>We have an issue with our SQL database, where when a Process ID locks and 
>then starts to block other process id's. This then has a knock on affect then 
>other process id's get block and in a couple of minutes we have a database 
>that is un-useable due to the original user blocking the whole organisation.  
>What we would like to do is set up some sort of notification to tell us when 
>a process id is blocking.
>
>We can see which process id is doing the blocking by going in to the 
>Enterprise Manager, drilling down in to the SQL server then to the Management 
>> Current Activity > Locks / Process ID area and then contact the user who is 
>linked to the  SPID number and ask them to close the database.  This then 
>allows all the other users to carry on working with no problems.
>
>I hope the above makes sense.
>
>Thanks for any help in advance
>

<Prev in Thread] Current Thread [Next in Thread>