Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2001
    Posts
    18

    Unanswered: Blocked transactions - how to identify the sql commands?

    Hi SQLServer gurus

    Just wondering if anyone can give me some info on how to find out the full syntax of the commands executed by the blocked and blocking SPID's in a locking situation.

    Using sp_who or sp_who2 will give basic info on the blocked trans (such as DELETE, SELECT etc), but not the actual statement. The blocking spid's command is only showing AWAITING COMMAND.

    Not really an urgent problem, but any suggestions appreciated!

    Cheers,
    Megan

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    DBCC INPUTBUFFER
    Displays the last statement sent from a client to Microsoft® SQL Server™.

    Syntax
    DBCC INPUTBUFFER (spid)


    You can use this command to see what is the longest running command which may point to the transaction that is causing the blocking.

    DBCC OPENTRAN
    Displays information about the oldest active transaction and the oldest distributed and nondistributed replicated transactions, if any, within the specified database. Results are displayed only if there is an active transaction or if the database contains replication information. An informational message is displayed if there are no active transactions.

    Syntax
    DBCC OPENTRAN
    ( { 'database_name' | database_id} )
    [ WITH TABLERESULTS
    [ , NO_INFOMSGS ]
    ]

  3. #3
    Join Date
    Oct 2001
    Posts
    18
    Thanks for your response, achorozy.

Posting Permissions

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