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 -

• Creating and importing an external certificate to SQL Server
• Writing reflective unit tests to improve code quality
• Netbeans code template for SLF4J logging
• Arrrggh! java.security.cert.CertificateException: Certificates does not conform to algorithm constraints
• NetBeans 6.7 Broke My Parameterized Tests

---

Discuss / Comment