"Paul H" <Pa***@discussions.microsoft.com> wrote in message
news:C05AF375-9305-4220-99E1-F2B71A89C635@microsoft.com...
> Hello all.
>
> I am experiencing a problem with SQL locks that are not timing out. I have
> a
> legacy PowerBuilder application that does not use timeouts, and will be
> very
> impractical to modify to use timeouts. Every so often, one user will
> experience a long delay (e.g. because their network connection is slow),
> and
> this will bring the system grinding to a halt, as other users are locked
> out
> of the system.
>
> Since it is impractical to modify the front end of the application, I am
> investigating solutions to manage the back end at the SQL Server side - I
> was
> thinking of a SQL Server Agent alert that notifies an operator when a user
> is
> getting locked out for more than 2 minutes (say). The operator will then
> be
> able to identify the blocking user, and kill their session if necessary.
>
> However, I can't work out which performance counter I should be using.
> Lock
> Wait Time seems to be the total wait time for all locks, rather than the
> maximum value for any specific session. Average Wait Time might work, but
> ideally, we'd like to nip the offending lock in the bud before it drags
> the
> average wait time for all users down. I'm not sure what Lock Blocks (in
> the
> Memory Management counters) does - it seems to be sitting at a value of
> 1000
> on our development box, even though there is no particular activity and
> nobody's getting locked out.
>
> Any suggestions folks?
>
> Paul

"Tom Cooper" wrote:

> I'm not sure whether there is a performance counter which you can use for
> this.  If no one else can help you, you can "roll your own" alerts.  To do
> this, you will need to add a new error message in sysmessages, a new alert
> attached to that error message, and set up the alert to send a notification
> to the operators.  (See sp_addmessage, sp_add_alert, and sp_add_notification
> in BOL.)  Then, you need code witch will determine whether some process has
> waited longer than the maximun amount of time you want to allow.  When that
> happens, raise an error with your defined error number and the alert will be
> sent to the operators.  If the test does not find any process which has been
> blocked, then just wait for a period of time and test again.
>
> To test whether whether some process has been waiting too long, you could
> use something like the following (shamelessly stolen from
> http://support.microsoft.com/kb/271509/en-us) which is a stored proc that
> accepts a parameter specifying a number of milliseconds.  It returns a
> result set only if a process has been blocked by some other process for more
> than that length of time.
>
> Create Procedure MonitorBlocking (@WaitTime int)
> As
> Set NoCount On
> -- No report unless at least one process
> -- waiting for @Waittime milliseconds
>
>
> declare @probclients table(spid smallint, ecid smallint, blocked smallint,
> waittype binary(2), dbid smallint,
>    waittime int, primary key (blocked, spid, ecid))
> insert @probclients select spid, ecid, blocked, waittype, dbid,
>    waittime
>    from master..sysprocesses where blocked!=0 or waittype != 0x0000
>
> if exists (select spid from @probclients where waittype != 0x020B
>       and waittime > @WaitTime)
> begin
>
>    insert @probclients select distinct blocked, 0, 0, 0x0000, 0, 0 from
> @probclients
>       where blocked not in (select spid from @probclients) and blocked != 0
>
>       Set NoCount Off
>       select spid, status, blocked, open_tran, waitresource, waittype,
>          waittime, cmd, lastwaittype, cpu, physical_io,
>          memusage, last_batch=convert(varchar(26), last_batch,121),
>          login_time=convert(varchar(26), login_time,121),net_address,
>          net_library, dbid, ecid, kpid, hostname, hostprocess,
>          loginame, program_name, nt_domain, nt_username, uid, sid,
>          sql_handle, stmt_start, stmt_end
>       from master..sysprocesses
>       where blocked!=0 or waittype != 0x0000
>          or spid in (select blocked from @probclients where blocked != 0)
>          or spid in (select spid from @probclients where blocked != 0)
> end
> go
>
> HTH,
> Tom
>

"Paul H" <Pa***@discussions.microsoft.com> wrote in message
news:35C8501F-A13D-42DA-8EAF-395C48FD1F62@microsoft.com...
> Hi Tom
>
> Thanks very much for your suggestions - that's been very helpful, I hadn't
> seen the KB article you linked to. I'm using SQL Server 7, but a bit of
> adaptation has got the stored procedure working for me - I've replaced
> @probclients with a temporary table #probclients, and removed references
> to
> sql_handle, stmt_start, stmt_end.
>
> I'm going to build some kind of daemon process around the stored
> procedure -
> I'm going to let the customer decide they want the blockages to be
> handled:
> whether the daemon should inform an operator, or whether they'd be happy
> letting the daemon go ahead and kill the offending process.
>
> "Tom Cooper" wrote:
>
>> I'm not sure whether there is a performance counter which you can use for
>> this.  If no one else can help you, you can "roll your own" alerts.  To
>> do
>> this, you will need to add a new error message in sysmessages, a new
>> alert
>> attached to that error message, and set up the alert to send a
>> notification
>> to the operators.  (See sp_addmessage, sp_add_alert, and
>> sp_add_notification
>> in BOL.)  Then, you need code witch will determine whether some process
>> has
>> waited longer than the maximun amount of time you want to allow.  When
>> that
>> happens, raise an error with your defined error number and the alert will
>> be
>> sent to the operators.  If the test does not find any process which has
>> been
>> blocked, then just wait for a period of time and test again.
>>
>> To test whether whether some process has been waiting too long, you could
>> use something like the following (shamelessly stolen from
>> http://support.microsoft.com/kb/271509/en-us) which is a stored proc that
>> accepts a parameter specifying a number of milliseconds.  It returns a
>> result set only if a process has been blocked by some other process for
>> more
>> than that length of time.
>>
>> Create Procedure MonitorBlocking (@WaitTime int)
>> As
>> Set NoCount On
>> -- No report unless at least one process
>> -- waiting for @Waittime milliseconds
>>
>>
>> declare @probclients table(spid smallint, ecid smallint, blocked
>> smallint,
>> waittype binary(2), dbid smallint,
>>    waittime int, primary key (blocked, spid, ecid))
>> insert @probclients select spid, ecid, blocked, waittype, dbid,
>>    waittime
>>    from master..sysprocesses where blocked!=0 or waittype != 0x0000
>>
>> if exists (select spid from @probclients where waittype != 0x020B
>>       and waittime > @WaitTime)
>> begin
>>
>>    insert @probclients select distinct blocked, 0, 0, 0x0000, 0, 0 from
>> @probclients
>>       where blocked not in (select spid from @probclients) and blocked !=
>> 0
>>
>>       Set NoCount Off
>>       select spid, status, blocked, open_tran, waitresource, waittype,
>>          waittime, cmd, lastwaittype, cpu, physical_io,
>>          memusage, last_batch=convert(varchar(26), last_batch,121),
>>          login_time=convert(varchar(26), login_time,121),net_address,
>>          net_library, dbid, ecid, kpid, hostname, hostprocess,
>>          loginame, program_name, nt_domain, nt_username, uid, sid,
>>          sql_handle, stmt_start, stmt_end
>>       from master..sysprocesses
>>       where blocked!=0 or waittype != 0x0000
>>          or spid in (select blocked from @probclients where blocked != 0)
>>          or spid in (select spid from @probclients where blocked != 0)
>> end
>> go
>>
>> HTH,
>> Tom
>>
>