how long a table blocked

[rkusenet]

I have written a stored procedure to list out all tables in which
rows or the table itself is locked. The only information I am
not able to get is the time when the lock occurred. The way I
want is that if I run the procedure it should show all locks
on a table which are at least 5 or x seconds old. This way I can
avoid momentary locks on a table which go away after few seconds.

Which table and column of master database has that information?

Thanks.

--

email id is bogus

[Erland Sommarskog]

rkusenet (rk******@sympa tico.ca) writes:

I have written a stored procedure to list out all tables in which
rows or the table itself is locked. The only information I am
not able to get is the time when the lock occurred. The way I
want is that if I run the procedure it should show all locks
on a table which are at least 5 or x seconds old. This way I can
avoid momentary locks on a table which go away after few seconds.

Which table and column of master database has that information?

I'm afraid that this information is not available. There is a
req_lifetime column in syslockinfo, which is carefully document,
but the essence of the documentation is that this is an internal
column.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Mario]

To my knowledge that info is not available. And maybe not so interesting (my
opinion) Technicaly what is interesting about locked table is the fact that
others have to wait because they want access to the table (or rows or keys
or whatever) in an incompatible mode.
The nice thing about this is that the time information about that fact is
available, but maybe hard to get through a stored proc. But a good sp
programmer should be able figure that out ;-)
The wait information is in sysprocesses, if the 'lastwaittype' is of type
'LCK..' a spid is waiting for a lock (if waittime > 0) (or 'waittype between
0x01 and 0x1f). 'Waitresource' will show what is being waited for TBL/KEY
etc. and waittime how long the spid is waiting now.

I hope that brings you closer to what you want..

--
regards,
Mario

http://www.sqlinternals.com

"rkusenet" <rk******@sympa tico.ca> wrote in message
news:bn******** ****@ID-75254.news.uni-berlin.de...

I have written a stored procedure to list out all tables in which
rows or the table itself is locked. The only information I am
not able to get is the time when the lock occurred. The way I
want is that if I run the procedure it should show all locks
on a table which are at least 5 or x seconds old. This way I can
avoid momentary locks on a table which go away after few seconds.

Which table and column of master database has that information?

Thanks.

--

email id is bogus

[rkusenet]

"Mario" <no****@sqlinte rnals.com> wrote in message news:3f******** *************** @news.xs4all.nl ...

To my knowledge that info is not available. And maybe not so interesting (my
opinion) Technicaly what is interesting about locked table is the fact that
others have to wait because they want access to the table (or rows or keys
or whatever) in an incompatible mode.
The nice thing about this is that the time information about that fact is
available, but maybe hard to get through a stored proc. But a good sp
programmer should be able figure that out ;-)
The wait information is in sysprocesses, if the 'lastwaittype' is of type
'LCK..' a spid is waiting for a lock (if waittime > 0) (or 'waittype between
0x01 and 0x1f). 'Waitresource' will show what is being waited for TBL/KEY
etc. and waittime how long the spid is waiting now.

I hope that brings you closer to what you want..

thanks Mario and Erland. With your help I am able to develop the stored procedure
we require.

--

email id is bogus