Home Forums SQL Server 2008 SQL Server 2008 Administration transaction log eats up all the space and growing until sql is stopped RE: transaction log eats up all the space and growing until sql is stopped

  • November 26, 2013 at 9:58 pm

    #1669322

    mJai (11/26/2013)

    Thanks, That returned me the following results. From the start time this seems like this process's been running for over 3 days.

    Oldest active transaction:

    SPID (server process ID): 58

    UID (user ID) : -1

    Name : user_transaction

    LSN : (473273:176905:59)

    Start time : Nov 24 2013 11:31:44:010PM

    SID : 0xb6e2b7cea93af54eb49780a9ff6d2b0b

    Yup, that looks like a suspect.

    Query sys.dm_exec_sessions and sys.dm_exec_connections and see where session_id 58 is coming from, which login, what machine, what application. USe the most_recent_sql_handle column of sys.dm_exec_connections along with the sys.dm_exec_sql_text function to see the last thing it ran. That combined should give you a good idea of where this open transaction is coming from.

    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