Script: open transactions with text and plans

script-open-transactions-with-text-and-plans/

RELATED LINKS
https://www.sqlskills.com/blogs/paul/survey-transaction-log-files-per-database-code-to-run/
https://www.sqlskills.com/blogs/paul/20122014-bug-that-can-cause-database-or-server-to-go-offline/
https://www.sqlskills.com/blogs/paul/code-to-analyze-the-transaction-hierarchy-in-the-log/
https://www.sqlskills.com/blogs/paul/code-to-show-rolled-back-transactions-after-a-crash/

Here’s a little script I knocked up this afternoon to tell me who has open transactions on the server – not just the single oldest active transaction that DBCC OPENTRAN returns.

It gives back:

• session ID
• login name
• database context
• transaction begin time
• how many log records have been generated by the transaction
• how much log space has been taken up by those log records
• how much log space has been reserved in case the transaction rolls back
• the last T-SQL that was executed in the context of the transaction
• the last query plan that was executed (only for currently executing plans)

It’s ordered by the transaction begin time. I had some trouble using CROSS APPLY with the sys.dm_exec_query_plan DMV – if the plan isn’t available, it blows out the entire result-set for that transaction. After messing around for ten minutes I discovered the OUTER APPLY operator which works nicely with NULL values.

Here’s the script with some example output (note, there are no delimiters around ‘text’ in line 8 as that confuses the code-formatting plugin):

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34

SELECT     [s_tst].[session_id],     [s_es].[login_name] AS [Login Name],     DB_NAME (s_tdt.database_id) AS [Database],     [s_tdt].[database_transaction_begin_time] AS [Begin Time],     [s_tdt].[database_transaction_log_bytes_used] AS [Log Bytes],     [s_tdt].[database_transaction_log_bytes_reserved] AS [Log Rsvd],     [s_est].text AS [Last T-SQL Text],     [s_eqp].[query_plan] AS [Last Plan] FROM     sys.dm_tran_database_transactions [s_tdt] JOIN     sys.dm_tran_session_transactions [s_tst] ON     [s_tst].[transaction_id] = [s_tdt].[transaction_id] JOIN     sys.[dm_exec_sessions] [s_es] ON     [s_es].[session_id] = [s_tst].[session_id] JOIN     sys.dm_exec_connections [s_ec] ON     [s_ec].[session_id] = [s_tst].[session_id] LEFT OUTER JOIN     sys.dm_exec_requests [s_er] ON     [s_er].[session_id] = [s_tst].[session_id] CROSS APPLY     sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est] OUTER APPLY     sys.dm_exec_query_plan ([s_er].[plan_handle]) AS [s_eqp] ORDER BY     [Begin Time] ASC; GO

?

session_id Login Name        Database Begin Time              Log Bytes Log Rsvd Last T-SQL Text                      Last Plan ---------- ----------------- -------- ----------------------- --------- -------- ------------------------------------ --------- 54         ROADRUNNERPR\paul foo      2010-02-01 15:28:48.560 236       8550     begin tran insert into t1 values (1) NULL 55         ROADRUNNERPR\paul foo      2010-02-01 16:38:18.373 356       8852     insert into t1 values (3)            NULL Example:2 Suppose we have below Employee table in SQL Server. CREATE TABLE dbo.Employee ( EmpID int IDENTITY(1,1) NOT NULL, Name varchar(55) NULL, Salary decimal(10, 2) NULL, Designation varchar(20) NULL ) The data in this table is as shown below: Remove Duplicate Records by using ROW_NUMBER() WITH TempEmp (Name,duplicateRecCount) AS ( SELECT Name,ROW_NUMBER() OVER(PARTITION by Name, Salary ORDER BY Name) AS duplicateRecCount FROM dbo.Employee ) --Now Delete Duplicate Records DELETE FROM TempEmp WHERE duplicateRecCount > 1 --See affected table Select * from Employee