Post reply

function for current transaction....

  • March 20, 2007 at 9:04 am

    #172831

    Hi,

    is there a function to return the current update/select statement that a session is attempting to run during a deadlock.

    If say I have sid 55, is there a function I can provide the id into to return the command.

    Any help welcome.

    Eamon

  • March 20, 2007 at 9:26 am

    #695977

    Eamon

    You can use the sys.dm_exec_sessions view to find out what session(s) a given SID has open.  Then simply put the session number(s) into DBCC INPUTBUFFER command.

    John

  • March 20, 2007 at 9:55 am

    #695985

    John, heyyyy...thanks a million, perfect

  • March 21, 2007 at 5:25 am

    #696110

    Or you can use sys.dm_exec_requests and pass the SQL handle to the sys.dm_exec_sql_text function and get the entire sql statement. (DBCC Inputbuffer only displays the first 255 characters)

    select * from sys.dm_exec_requests req cross apply sys.dm_exec_sql_text(req.sql_handle) txt

    where req.session_id = @spid

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 26, 2007 at 10:04 am

    #696919

    Gail....that's even better...thanks !!!

  • March 27, 2007 at 12:12 am

    #697053

    Pleasure

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply