[posted and mailed, please reply in news]
rkusenet (rk******@sympatico.ca) writes:
I am fairly new to SQL Server. I am writing a tool in stored procedure
to identify locks in a table. I have already written the basic framework
of the SP. It will reside in master database and take two inputs. Database
name and table name. From that it will show all locks at that instant
on that table of that database. If table name is omitted, then it will
show locks on all tables. I am using syslockinfo, spt_values tables and
joining with SP_WHO procedure to get the table name, user name and the
session id.
Now what I need is to find out which SQL is causing the lock and since
when lock is being held on the table. Which tables in master database
holds the required information.
That informations is not available in SQL Server.
What you can find out is:
1) The most recently submitted batch from a process, by using a DBCC
INPUTBUFFER.
2) The SQL text of the currently executing scope for a process, and
the exact current statement within that scope. To do this, you use
the function fn_get_sql(), which was added to SQL 2000 in SP3. It
is documented in the updated version of Books Online (link below),
although the text is a bit cryptic.
Note that none of these statements are necessarily those that caused
the lock, because the lock may have been acquired in a calling scope,
or even in a previous batch. What you can find, though, is the statement
that causes a process to be blocked, because if a process is blocked
and is waiting for another process to yield, then its current statement
does not change.
I have a procedure which is somewhat similar to the one you are
working with, and you can download it from my web site at
http://www.algonet.se/~sommar/sqlutil/aba_lockinfo.html. There you
can find an example on how to work with fn_get_sql().
--
Erland Sommarskog, SQL Server MVP,
so****@algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp 
