Blog About Contact

Diagnosing transactions causing SQL Server's transaction log truncation to fail

Published Mon, 12 Aug 2013

This is one of those "for my future reference" posts.

To diagnose which transaction is causing SQL Server's transaction log truncation to fail, the following SQL can be used -

  1. Execute - DBCC OPENTRAN('DB_NAME') where DB_NAME is the database name. This yields output such as -

    Transaction information for database 'DB_NAME'.
    
    
    Oldest active transaction:
        SPID (server process ID): 112
        UID (user ID) : -1
        Name : user_transaction
        LSN : (21968:21010:1)
        Start time : Jul 01 2013 2:12:44:307AM
        SID : 0x38feca6748f0f14d8e4e8f379ce76d04
    

    The key piece of information we're after is the SPID - the process ID of the oldest transaction.

  2. Execute query, replace '123' with SPID from above -

    select ec.connect_time, ec.client_net_address, 
           host_name,program_name, original_login_name, 
           (select text from sys.dm_exec_sql_text(ec.most_recent_sql_handle))
    from  sys.dm_exec_sessions es, sys.dm_exec_connections ec 
    where es.session_id = ec.session_id
    and   ec.session_id = 123
    

This gives you the SQL statement and client details of the probably cause of the problem. Now go fix the code! :)


About the Author

Richard Nichols is an Australian software engineer with a passion for making things.

Follow him on twitter or subscribe by RSS or email.

You might also enjoy reading -


Discuss / Comment

No one has commented yet.

Add a comment

  • {{e.error}}

Thanks for your comment!/

Required.
Valid email address required.
Required.
Posting message, please wait...