Troubleshooting transaction log full issues

Below SQL code helps in identifying orphaned or long-running transactions.

SELECT s.session_id ,
s.status ,
s.host_name ,
s.program_name ,
s.login_name ,
s.login_time ,
s.last_request_start_time ,
s.last_request_end_time ,
t.text
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_connections c ON s.session_id = c.session_id
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) t
WHERE s.session_id = spid

SELECT st.session_id ,
st.is_user_transaction ,
dt.database_transaction_begin_time ,
dt.database_transaction_log_record_count ,
dt.database_transaction_log_bytes_used
FROM sys.dm_tran_session_transactions st
JOIN sys.dm_tran_database_transactions dt
ON st.transaction_id = dt.transaction_id
AND dt.database_id = DB_ID(‘master’)
WHERE st.session_id = spid

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s