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 -
DB_NAMEis 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.
Execute query, replace '123' with
SPIDfrom 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.
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