Hello Blog visitors!
In this post I will demonstrate how to identify inactive sessions, that is, that are not running a query and have open transactions.
This can lead to a number of problems with your database, from crashing other sessions trying to access the same resource to preventing the database transaction log from being automatically backed up, and gradually increasing until tempdb bursts (READ DISASTER) .
How to identify inactive sessions with open transactions in SQL Server
To demonstrate this situation, I will open a SQL Server tab and start a transaction. Note that I will not run COMMIT or ROLLBACK, leaving this transaction open:
Now let's identify this session:
sys.dm_exec_sessions A WITH(NOLOCK)
JOIN sys.dm_exec_connections B WITH(NOLOCK) ON A.session_id = B.session_id
CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) C
EXISTS (SELECT * FROM sys.dm_tran_session_transactions AS t WITH(NOLOCK) WHERE t.session_id = A.session_id)
AND NOT EXISTS (SELECT * FROM sys.dm_exec_requests AS r WITH(NOLOCK) WHERE r.session_id = A.session_id)
How to identify open transactions in instance
While the query above shows all sessions that have transactions open, the query below shows transactions that are open:
C.name AS database_name,
WHEN 1 THEN 'Read/write transaction'
WHEN 2 THEN 'Read-only transaction'
WHEN 3 THEN 'System transaction'
END) AS database_transaction_type,
WHEN 1 THEN 'The transaction has not been initialized.'
WHEN 3 THEN 'The transaction has been initialized but has not generated any log records.'
WHEN 4 THEN 'The transaction has generated log records.'
WHEN 5 THEN 'The transaction has been prepared.'
WHEN 10 THEN 'The transaction has been committed.'
WHEN 11 THEN 'The transaction has been rolled back.'
WHEN 12 THEN 'The transaction is being committed. In this state the log record is being generated, but it has not been materialized or persisted.'
END) AS database_transaction_state,
JOIN sys.dm_tran_database_transactions B ON A.transaction_id = B.transaction_id
JOIN sys.databases C ON B.database_id = C.database_id
With this, we can make a decision if it is impacting our database (drop session, request explanations from responsible user, etc.)
Thanks for stopping by and see you next time!