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

Re: Notification when Locked Process is Blocking

Subject: Re: Notification when Locked Process is Blocking
From: Gareth
Date: Tue, 25 Apr 2006 04:47:01 -0700
Newsgroups: microsoft.public.sqlserver.notificationsvcs
Hi Joe

Cheers for this, we do know the reason why this is happening but the 
software developers who developed the software are "still looking in to this" 
which is not a help.

Will look in to using the Alerts, but as i am not a very compitent SQL man 
this may take some time

Cheers for the responce

Gareth Collins

"Joe Webb" wrote:

> 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>