Post reply

DBCC OPENTRAN not showing my open transaction

  • February 25, 2009 at 7:53 am

    #128029

    Hi gurus,

    I created an sp which does a variety of things to monitor the

    state of my SQL server (locks etc). One of the things the proc reports on is

    open transactions for more than 2 minutes :

    SELECT spid, open_tran, HostName, Program_Name, Uid, Dbid from

    master..sysprocesses where open_tran >0 and and (convert(money,getdate()-last_batch)* 86400.0)

    Herein lies my problem. If I execute DBCC OPENTRAN I am told there are no

    open transactions. However, running my procedure reports that there is one

    open transaction open. I can identify that transaction as something created

    by myself to test, just an create #temp and insert into table. This is the transaction that

    my sp is showing as open (my proc runs DBCC INPUTBUFFER against the spid

    with the open_tran >0)--which is ok but

    should'nt the DBCC OPENTRAN return

    this open transaction?

  • February 25, 2009 at 8:53 am

    #950052

    DBA (2/25/2009)

    should'nt the DBCC OPENTRAN return

    this open transaction?

    What DB are you running it in? What DB's the transaction in?

    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

  • February 27, 2009 at 9:42 am

    #951267

    i am running it on my DB , is called Distributors

    DBCC opentran(Distributors).

Viewing 3 posts - 1 through 2 (of 2 total)

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