"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
> <Gareth@discussions.microsoft.com> 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
> >
