Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2005
    Posts
    12

    Unhappy Unanswered: How identify locking Store procedure

    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

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by afrugone
    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 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
    Have you hugged your backup today?

  3. #3
    Join Date
    Mar 2005
    Posts
    12
    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

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    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.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Jul 2002
    Posts
    229
    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.

  6. #6
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by Coolberg
    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
    Have you hugged your backup today?

  7. #7
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by afrugone
    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:

    Quote Originally Posted by SQL BOL
    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
    Have you hugged your backup today?

  8. #8
    Join Date
    Jul 2002
    Posts
    229
    Is there any way at all to get the full SQL without SP3 ?

  9. #9
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by Coolberg
    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
    Have you hugged your backup today?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •