How identify locking Store procedure

:eek: I've have a lot of locks in a SQL Server, and I'd like to identify which SPs are locking what tables, I've being trying with sp_who, sp_who2 and sp_lock, so i can identify the process number, but I don't have any idea what this process is doing (which sp is running? and what command?) and which table is locked by this process, can anybody send me some querys to get this information

I'm novice in SQL Server, and I've not access to the Enterprice manager console, and I've only have priveleges to read data from the database

Thanks for your help
Alfredo:eek: I've have a lot of locks in a SQL Server, and I'd like to identify which SPs are locking what tables, I've being trying with sp_who, sp_who2 and sp_lock, so i can identify the process number, but I don't have any idea what this process is doing (which sp is running? and what command?) and which table is locked by this process, can anybody send me some querys to get this information

I'm novice in SQL Server, and I've not access to the Enterprice manager console, and I've only have priveleges to read data from the database

Thanks for your help
Alfredo

Try this link (http://vyaskn.tripod.com/fn_get_sql.htm) to see if it can help you understand what's going on. Basically, you can only see the first 255(?) characters or so of a SPID's inputbuffer unless you use this function.

However, I should note that use of this function requires that you be running SQL 2000, SP3.

Regards,

hmscott|||hmscott, many thanks for your help, but, If I understand, this procedure requires privileges to write a store procedure at the server, and I can't do that, I only have privilges to read information.

regards

Alfredo|||are you sure you have a problem? locking is part of sql. every insert, update, and delete creates a lock and there are locks of different flavors.|||I can't make that SP work, it just goes on and I get nothing displayed in QA. After a while, I press the red stop button and all I get is some code snippet from the SP itself.|||I can't make that SP work, it just goes on and I get nothing displayed in QA. After a while, I press the red stop button and all I get is some code snippet from the SP itself.

Just to clarify, I don't use the SP itself (the one in blue text at the bottom of the link that I posted earlier). I used the page as a reference on how to use fn_get_sql (which is new to SP3). Instead, you can use the SQL BOL reference for fn_get_sql instead.

My apologies, I probably should have posted the SQL BOL reference first.

Regards,

hmscott|||hmscott, many thanks for your help, but, If I understand, this procedure requires privileges to write a store procedure at the server, and I can't do that, I only have privilges to read information.

regards

Alfredo

As I mentioned in another reply, the link is principally to highlight the functionality that is available by using the new (as of SP3) internal function called fn_get_sql. Instead, try looking up fn_get_sql in the SQL BOL (make sure that you get the updated version of the BOL). In particular, there is an example at the bottom of the SQL BOL reference that shows how to use fn_get_sql with the sysprocesses table:

DECLARE @.Handle binary(20)
SELECT @.Handle = sql_handle FROM sysprocesses WHERE spid = 52
SELECT * FROM ::fn_get_sql(@.Handle)

Not meaning to be rude or presumptuous, but if you don't have rights to create an SP on the DB, can you not engage your sysadmin/dba to assist?

Regards,

hmscott|||Is there any way at all to get the full SQL without SP3 ?|||Is there any way at all to get the full SQL without SP3 ?

Not that I am aware of.

You really ought to be running SP3 anyway. Too many vulnerabilities pre-SP3.

Regards,

hmscott