Using aba_lockinfo in a loop

[Jim Devenish]

Erland Sommarskog's procedure aba_lockinfo provides very valuable
information when trying to sort out locking and blocking problems.

However is only provides a snapshot in time.

Is it possible to have this run continuously for a period of time? or
possibly at set intervals, say every minute?

If so, how would one set it up?

[Roy Harvey]

Re: Using aba_lockinfo in a loop

On alternative would be a watifor loop. Here is an example using a
five second delay over one minute.

declare @t datetime
set @t = dateadd(second, 60,getdate())

WHILE getdate() <= @t
BEGIN
--do something here
select getdate()
waitfor delay '00:00:05'
END

Roy Harvey
Beacon Falls, Ct

On 18 Aug 2006 02:59:45 -0700, "Jim Devenish"
<internet.shopp ing@foobox.comw rote:

>Erland Sommarskog's procedure aba_lockinfo provides very valuable
>information when trying to sort out locking and blocking problems.
>
>However is only provides a snapshot in time.
>
>Is it possible to have this run continuously for a period of time? or
>possibly at set intervals, say every minute?
>
>If so, how would one set it up?

[Erland Sommarskog]

Re: Using aba_lockinfo in a loop

Jim Devenish (internet.shopp ing@foobox.com) writes:

Erland Sommarskog's procedure aba_lockinfo provides very valuable
information when trying to sort out locking and blocking problems.
>
However is only provides a snapshot in time.
>
Is it possible to have this run continuously for a period of time? or
possibly at set intervals, say every minute?
>
If so, how would one set it up?

You could run it from SQL Server Agent. When you set up the job step, there
is an Advanced tab where you can redirect output to a file, and you can opt
to append to an existing file.

I would however not recommend you do this for any longer period of time in
a production environment. If system is already under load, and there are a
lot processes and lot of locks, the procedure itself takes some resources.

(But I recall that I did precisely this, ran aba_lockinfo once a minute at
one of our customers for a day or two, back in the days of SQL 6.5, and I
was able to find several flaws in our product. In fact some of the blocking
situations were unbelivable. In the lvl column which displays how many you
are waiting for, I saw the number 15!)


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Jim Devenish]

Re: Using aba_lockinfo in a loop

Thanks to both of you. I will give it a try.

May be back for more detailed info if I get stuck

Jim

[Jim Devenish]

Re: Using aba_lockinfo in a loop

Thanks. I have successfully set up a trial with SQL Server Agent -
every minute for 5 minutes. Works well. Now for the real thing.

Jim

Erland Sommarskog wrote:

>
You could run it from SQL Server Agent. When you set up the job step, there
is an Advanced tab where you can redirect output to a file, and you can opt
to append to an existing file.
>