Managing Open Transactions in SQL Server

Open transactions in SQL Server occur frequently and demand close attention and proper handling to prevent issues. Locking data and blocking access to it, causing transaction log growth, affecting data consistency, and increasing resource consumption are just a few potential problems stemming from open transactions.

Monitoring open transactions and ensuring their timely completion (either committing or rolling back) is a best practice, and SQL Server offers various tools and commands to identify such open transactions. This article will explore these tools and methods and provide a guide on automatically finding and resolving open transactions.

Contents

• Understanding open transactions
• Prerequisites and preparations
• Finding SQL Server incomplete transactions
• Addressing lost transactions
• Automating cleanup and maintenance tasks
• Best practices for managing open transactions
• Conclusion

Understanding open transactions

In SQL Server, transactions are either committed upon success or rolled back if they encounter errors or are canceled. However, there are instances where transactions are left idle, which should be avoided.

For example, when script execution is interrupted without issuing COMMIT or ROLLBACK statements, it leaves the transaction in an “open” idle state. These open transactions consume resources and can lead to issues like running out of disk space and overall system damage.

Detecting and resolving such open transactions is crucial to prevent these problems.

Prerequisites and preparations

To illustrate various methods to identify and delete open transactions as well as the method of automation of this process, we’ll use SQL Server Management Studio (SSMS), the standard tool for managing SQL Server databases. We’ll be utilizing an enhanced version of SSMS that includes the dbForge SQL Complete add-in. This powerful coding assistant offers a host of additional features, including notifications about open transactions.

SQL Complete integrates smoothly with SSMS, enriching the familiar interface with numerous extra functionalities for all SQL Server professionals.

Also, we will use the test AdventureWorks2022 SQL Server database to illustrate the methods of finding idle transactions. To demonstrate the automation of the process, we have prepared the DemoDatabase test database. However, you can use the scripts presented in this article on your databases.

Finding SQL Server incomplete transactions

SQL Server provides an in-built method of defining if there are any open transactions, the DBCC OPENTRAN command.  

DBCC OPENTRAN; 

If any open transactions have been detected, SSMS will return the information about them as “oldest active transaction,” which you can see in the screenshot below.

Note: The DBCC OPENTRAN command shows open transactions for the database for which it is executed.

Another method is querying the sys.sysprocesses Dynamic Management View (DMV). 

SELECT * FROM sys.sysprocesses WHERE open_tran = 1

This command output is presented in a standard query window and provides detailed information about the transaction.

Besides, there is the possibility to present the information retrieved by the DBCC OPENTRAN command as a temporary table. This format is preferred by many specialists.

-- Create a temporary table to accept the results.
CREATE TABLE #OpenTranStatus (
   ActiveTransaction VARCHAR(25),
   Details sql_variant
   );
-- Execute the command, putting the results in the table.
INSERT INTO #OpenTranStatus
   EXEC ('DBCC OPENTRAN WITH TABLERESULTS, NO_INFOMSGS');
  
-- Display the results.
SELECT * FROM #OpenTranStatus;

Finally, if you use SQL Complete, this add-in will notify the user about any open transactions visually:

The option to warn the user about any open transactions is enabled by default in SQL Complete. If not, you can activate it manually in the Notifications section of the SQL Complete Options menu:

Once you have defined the open transaction that you need to delete, you can use the KILL command and the session ID to get rid of it:

KILL 58;

Note: Executing the KILL command requires admin privileges.

Addressing lost transactions

We have examined different ways of finding the open transactions in SQL Server. However, when you are the database administrator who deals with multiple databases and many hundreds and thousands of transactions.

We can collect information about SQL Server active transactions and their sessions that have no requests (transactions that were launched and left forgotten). Further, that information can be presented in a table format.

We’ll use the below code to create the table to store information about current lost transactions in DemoDatabase:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [srv].[SessionTran] (
	[SessionID] INT NOT NULL
   ,[TransactionID] BIGINT NOT NULL
   ,[CountTranNotRequest] TINYINT NOT NULL
   ,[CountSessionNotRequest] TINYINT NOT NULL
   ,[TransactionBeginTime] DATETIME NOT NULL
   ,[InsertUTCDate] DATETIME NOT NULL
   ,[UpdateUTCDate] DATETIME NOT NULL
   ,CONSTRAINT [PK_SessionTran] PRIMARY KEY CLUSTERED
	(
	[SessionID] ASC,
	[TransactionID] ASC
	) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [srv].[SessionTran] ADD CONSTRAINT [DF_SessionTran_Count] DEFAULT ((0)) FOR [CountTranNotRequest]
GO

ALTER TABLE [srv].[SessionTran] ADD CONSTRAINT [DF_SessionTran_CountSessionNotRequest] DEFAULT ((0)) FOR [CountSessionNotRequest]
GO

ALTER TABLE [srv].[SessionTran] ADD CONSTRAINT [DF_SessionTran_InsertUTCDate] DEFAULT (GETUTCDATE()) FOR [InsertUTCDate]
GO

ALTER TABLE [srv].[SessionTran] ADD CONSTRAINT [DF_SessionTran_UpdateUTCDate] DEFAULT (GETUTCDATE()) FOR [UpdateUTCDate]
GO

In this script:

• SessionID identifies the session
• TransactionID identifies the lost transaction
• CountTranNotRequest stands for the number of times the transaction was recorded as lost
• CountSessionNotRequest stands for the number of times the session was recorded as one that has no active queries and contains a lost transaction
• TransactionBeginTime refers to the start date and time of the lost transaction
• InsertUTCDate identifies the date and time (UTC) when the record was made
• UpdateUTCDate identifies the date and time (UTC) when the record was updated.

Similarly, we create a table to archive open transactions selected from the first table according to the delete actions in the same DemoDatabase.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [srv].[KillSession](
[ID] [int] IDENTITY(1,1) NOT NULL,
[session_id] [smallint] NOT NULL,
[transaction_id] [bigint] NOT NULL,
[login_time] [datetime] NOT NULL,
[host_name] [nvarchar](128) NULL,
[program_name] [nvarchar](128) NULL,
[host_process_id] [int] NULL,
[client_version] [int] NULL,
[client_interface_name] [nvarchar](32) NULL,
[security_id] [varbinary](85) NOT NULL,
[login_name] [nvarchar](128) NOT NULL,
[nt_domain] [nvarchar](128) NULL,
[nt_user_name] [nvarchar](128) NULL,
[status] [nvarchar](30) NOT NULL,
[context_info] [varbinary](128) NULL,
[cpu_time] [int] NOT NULL,
[memory_usage] [int] NOT NULL,
[total_scheduled_time] [int] NOT NULL,
[total_elapsed_time] [int] NOT NULL,
[endpoint_id] [int] NOT NULL,
[last_request_start_time] [datetime] NOT NULL,
[last_request_end_time] [datetime] NULL,
[reads] [bigint] NOT NULL,
[writes] [bigint] NOT NULL,
[logical_reads] [bigint] NOT NULL,
[is_user_process] [bit] NOT NULL,
[text_size] [int] NOT NULL,
[language] [nvarchar](128) NULL,
[date_format] [nvarchar](3) NULL,
[date_first] [smallint] NOT NULL,
[quoted_identifier] [bit] NOT NULL,
[arithabort] [bit] NOT NULL,
[ansi_null_dflt_on] [bit] NOT NULL,
[ansi_defaults] [bit] NOT NULL,
[ansi_warnings] [bit] NOT NULL,
[ansi_padding] [bit] NOT NULL,
[ansi_nulls] [bit] NOT NULL,
[concat_null_yields_null] [bit] NOT NULL,
[transaction_isolation_level] [smallint] NOT NULL,
[lock_timeout] [int] NOT NULL,
[deadlock_priority] [int] NOT NULL,
[row_count] [bigint] NOT NULL,
[prev_error] [int] NOT NULL,
[original_security_id] [varbinary](85) NOT NULL,
[original_login_name] [nvarchar](128) NOT NULL,
[last_successful_logon] [datetime] NULL,
[last_unsuccessful_logon] [datetime] NULL,
[unsuccessful_logons] [bigint] NULL,
[group_id] [int] NOT NULL,
[database_id] [smallint] NOT NULL,
[authenticating_database_id] [int] NULL,
[open_transaction_count] [int] NOT NULL,
[most_recent_session_id] [int] NULL,
[connect_time] [datetime] NULL,
[net_transport] [nvarchar](40) NULL,
[protocol_type] [nvarchar](40) NULL,
[protocol_version] [int] NULL,
[encrypt_option] [nvarchar](40) NULL,
[auth_scheme] [nvarchar](40) NULL,
[node_affinity] [smallint] NULL,
[num_reads] [int] NULL,
[num_writes] [int] NULL,
[last_read] [datetime] NULL,
[last_write] [datetime] NULL,
[net_packet_size] [int] NULL,
[client_net_address] [nvarchar](48) NULL,
[client_tcp_port] [int] NULL,
[local_net_address] [nvarchar](48) NULL,
[local_tcp_port] [int] NULL,
[connection_id] [uniqueidentifier] NULL,
[parent_connection_id] [uniqueidentifier] NULL,
[most_recent_sql_handle] [varbinary](64) NULL,
[LastTSQL] [nvarchar](max) NULL,
[transaction_begin_time] [datetime] NOT NULL,
[CountTranNotRequest] [tinyint] NOT NULL,
[CountSessionNotRequest] [tinyint] NOT NULL,
[InsertUTCDate] [datetime] NOT NULL,
 CONSTRAINT [PK_KillSession] PRIMARY KEY CLUSTERED 
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [srv].[KillSession] ADD  CONSTRAINT [DF_KillSession_InsertUTCDate]  DEFAULT (getutcdate()) FOR [InsertUTCDate]
GO

This table will list all “killed” transactions. Specifically, the InsertUTCDate field will provide the exact time when each transaction was terminated and logged in the table.

Automating cleanup and maintenance tasks

Searching for open transactions and killing them manually would require too much time and resources. This tedious job can be automated, and it should be automated.

Having analyzed the requests, their statuses, and sessions, open a new window and type the following scripts to find open transactions in SQL Server that are left incomplete.

declare @tbl table (
SessionID int,
TransactionID bigint,
IsSessionNotRequest bit,
TransactionBeginTime datetime
);

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
ALTER PROCEDURE [srv].[AutoKillSessionTranBegin] @minuteOld2 INT = 30, --old age of a running transaction
@countIsNotRequests2 INT = 5 --number of hits in the table
AS
BEGIN
    /*
        --definition of frozen transactions (forgotten ones that do not have active requests) with their subsequent removal
    */

    SET NOCOUNT ON;
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;


    DECLARE @tbl TABLE (
        SessionID INT
       ,TransactionID BIGINT
       ,IsSessionNotRequest BIT
       ,TransactionBeginTime DATETIME
    );

    --collect information (transactions and their sessions that have no requests, i.e., transactions that are started and forgotten)
    INSERT INTO @tbl (SessionID,
    TransactionID,
    IsSessionNotRequest,
    TransactionBeginTime)
        SELECT
            t.[session_id] AS SessionID
           ,t.[transaction_id] AS TransactionID
           ,CASE
                WHEN EXISTS (SELECT TOP (1)
                            1
                        FROM sys.dm_exec_requests AS r
                        WHERE r.[session_id] = t.[session_id]) THEN 0
                ELSE 1
            END AS IsSessionNotRequest
           ,(SELECT TOP (1)
                    dtat.[transaction_begin_time]
                FROM sys.dm_tran_active_transactions AS dtat
                WHERE dtat.[transaction_id] = t.[transaction_id])
            AS TransactionBeginTime
        FROM sys.dm_tran_session_transactions AS t
        WHERE t.[is_user_transaction] = 1
        AND NOT EXISTS (SELECT TOP (1)
                1
            FROM sys.dm_exec_requests AS r
            WHERE r.[transaction_id] = t.[transaction_id]);

    --update the table of running transactions that have no active queries
    ;
    MERGE srv.SessionTran AS st USING @tbl AS t
    ON st.[SessionID] = t.[SessionID]
        AND st.[TransactionID] = t.[TransactionID]
    WHEN MATCHED
        THEN UPDATE
            SET [UpdateUTCDate] = GETUTCDATE()
               ,[CountTranNotRequest] = st.[CountTranNotRequest] + 1
               ,[CountSessionNotRequest] =
                CASE
                    WHEN (t.[IsSessionNotRequest] = 1) THEN (st.[CountSessionNotRequest] + 1)
                    ELSE 0
                END
               ,[TransactionBeginTime] = COALESCE(t.[TransactionBeginTime], st.[TransactionBeginTime])
    WHEN NOT MATCHED BY TARGET
        AND (t.[TransactionBeginTime] IS NOT NULL)
        THEN INSERT ([SessionID]
            , [TransactionID]
            , [TransactionBeginTime])
                VALUES (t.[SessionID], t.[TransactionID], t.[TransactionBeginTime])
    WHEN NOT MATCHED BY SOURCE
        THEN DELETE;

    --list of sessions to delete (containing frozen transactions)
    DECLARE @kills TABLE (
        SessionID INT
    );

    --detailed information for the archive
    DECLARE @kills_copy TABLE (
        SessionID INT
       ,TransactionID BIGINT
       ,CountTranNotRequest TINYINT
       ,CountSessionNotRequest TINYINT
       ,TransactionBeginTime DATETIME
    );

    --collect those sessions that need to be killed
    INSERT INTO @kills_copy (SessionID,
    TransactionID,
    CountTranNotRequest,
    CountSessionNotRequest,
    TransactionBeginTime)
        SELECT
            SessionID
           ,TransactionID
           ,CountTranNotRequest
           ,CountSessionNotRequest
           ,TransactionBeginTime
        FROM srv.SessionTran
        WHERE [CountTranNotRequest] >= @countIsNotRequests2
        AND [CountSessionNotRequest] >= @countIsNotRequests2
        AND [TransactionBeginTime] <= DATEADD(MINUTE, -@minuteOld2, GETDATE());

    --archive what we are going to delete (detailed information about deleted sessions, connections and transactions)
    INSERT INTO [srv].[KillSession] ([session_id]
    , [transaction_id]
    , [login_time]
    , [host_name]
    , [program_name]
    , [host_process_id]
    , [client_version]
    , [client_interface_name]
    , [security_id]
    , [login_name]
    , [nt_domain]
    , [nt_user_name]
    , [status]
    , [context_info]
    , [cpu_time]
    , [memory_usage]
    , [total_scheduled_time]
    , [total_elapsed_time]
    , [endpoint_id]
    , [last_request_start_time]
    , [last_request_end_time]
    , [reads]
    , [writes]
    , [logical_reads]
    , [is_user_process]
    , [text_size]
    , [language]
    , [date_format]
    , [date_first]
    , [quoted_identifier]
    , [arithabort]
    , [ansi_null_dflt_on]
    , [ansi_defaults]
    , [ansi_warnings]
    , [ansi_padding]
    , [ansi_nulls]
    , [concat_null_yields_null]
    , [transaction_isolation_level]
    , [lock_timeout]
    , [deadlock_priority]
    , [row_count]
    , [prev_error]
    , [original_security_id]
    , [original_login_name]
    , [last_successful_logon]
    , [last_unsuccessful_logon]
    , [unsuccessful_logons]
    , [group_id]
    , [database_id]
    , [authenticating_database_id]
    , [open_transaction_count]
    , [most_recent_session_id]
    , [connect_time]
    , [net_transport]
    , [protocol_type]
    , [protocol_version]
    , [encrypt_option]
    , [auth_scheme]
    , [node_affinity]
    , [num_reads]
    , [num_writes]
    , [last_read]
    , [last_write]
    , [net_packet_size]
    , [client_net_address]
    , [client_tcp_port]
    , [local_net_address]
    , [local_tcp_port]
    , [connection_id]
    , [parent_connection_id]
    , [most_recent_sql_handle]
    , [LastTSQL]
    , [transaction_begin_time]
    , [CountTranNotRequest]
    , [CountSessionNotRequest])
        SELECT
            ES.[session_id]
           ,kc.[TransactionID]
           ,ES.[login_time]
           ,ES.[host_name]
           ,ES.[program_name]
           ,ES.[host_process_id]
           ,ES.[client_version]
           ,ES.[client_interface_name]
           ,ES.[security_id]
           ,ES.[login_name]
           ,ES.[nt_domain]
           ,ES.[nt_user_name]
           ,ES.[status]
           ,ES.[context_info]
           ,ES.[cpu_time]
           ,ES.[memory_usage]
           ,ES.[total_scheduled_time]
           ,ES.[total_elapsed_time]
           ,ES.[endpoint_id]
           ,ES.[last_request_start_time]
           ,ES.[last_request_end_time]
           ,ES.[reads]
           ,ES.[writes]
           ,ES.[logical_reads]
           ,ES.[is_user_process]
           ,ES.[text_size]
           ,ES.[language]
           ,ES.[date_format]
           ,ES.[date_first]
           ,ES.[quoted_identifier]
           ,ES.[arithabort]
           ,ES.[ansi_null_dflt_on]
           ,ES.[ansi_defaults]
           ,ES.[ansi_warnings]
           ,ES.[ansi_padding]
           ,ES.[ansi_nulls]
           ,ES.[concat_null_yields_null]
           ,ES.[transaction_isolation_level]
           ,ES.[lock_timeout]
           ,ES.[deadlock_priority]
           ,ES.[row_count]
           ,ES.[prev_error]
           ,ES.[original_security_id]
           ,ES.[original_login_name]
           ,ES.[last_successful_logon]
           ,ES.[last_unsuccessful_logon]
           ,ES.[unsuccessful_logons]
           ,ES.[group_id]
           ,ES.[database_id]
           ,ES.[authenticating_database_id]
           ,ES.[open_transaction_count]
           ,EC.[most_recent_session_id]
           ,EC.[connect_time]
           ,EC.[net_transport]
           ,EC.[protocol_type]
           ,EC.[protocol_version]
           ,EC.[encrypt_option]
           ,EC.[auth_scheme]
           ,EC.[node_affinity]
           ,EC.[num_reads]
           ,EC.[num_writes]
           ,EC.[last_read]
           ,EC.[last_write]
           ,EC.[net_packet_size]
           ,EC.[client_net_address]
           ,EC.[client_tcp_port]
           ,EC.[local_net_address]
           ,EC.[local_tcp_port]
           ,EC.[connection_id]
           ,EC.[parent_connection_id]
           ,EC.[most_recent_sql_handle]
           ,(SELECT TOP (1)
                    text
                FROM sys.dm_exec_sql_text(EC.[most_recent_sql_handle]))
            AS [LastTSQL]
           ,kc.[TransactionBeginTime]
           ,kc.[CountTranNotRequest]
           ,kc.[CountSessionNotRequest]
        FROM @kills_copy AS kc
        INNER JOIN sys.dm_exec_sessions ES WITH (READUNCOMMITTED)
            ON kc.[SessionID] = ES.[session_id]
        INNER JOIN sys.dm_exec_connections EC WITH (READUNCOMMITTED)
            ON EC.session_id = ES.session_id;

    --collecting sessions
    INSERT INTO @kills (SessionID)
        SELECT
            [SessionID]
        FROM @kills_copy
        GROUP BY [SessionID];

    DECLARE @SessionID INT;

    --direct deletion of selected sessions
    WHILE (EXISTS (SELECT TOP (1)
            1
        FROM @kills)
    )
    BEGIN
    SELECT TOP (1)
        @SessionID = [SessionID]
    FROM @kills;

    BEGIN TRY
        EXEC sp_executesql N'kill @SessionID'
                          ,N'@SessionID INT'
                          ,@SessionID;
    END TRY
    BEGIN CATCH
    END CATCH;

    DELETE FROM @kills
    WHERE [SessionID] = @SessionID;
    END;

    SELECT
        st.[SessionID]
       ,st.[TransactionID] INTO #tbl
    FROM srv.SessionTran AS st
    WHERE st.[CountTranNotRequest] >= 250
    OR st.[CountSessionNotRequest] >= 250
    OR EXISTS (SELECT TOP (1)
            1
        FROM @kills_copy kc
        WHERE kc.[SessionID] = st.[SessionID]);

    --deletion of processed records, as well as those that cannot be deleted and they are too long in the table for consideration
    DELETE FROM st
        FROM #tbl AS t
        INNER JOIN srv.SessionTran AS st
            ON t.[SessionID] = st.[SessionID]
            AND t.[TransactionID] = st.[TransactionID];

    DROP TABLE #tbl;
END;

Let us execute this script to check if it is correct.

The query is executed successfully, and the script selects incomplete transactions. Therefore, we can insert this code into the stored procedure.

SQL Complete includes a robust script generator that we can use to create the EXECUTE code block in the new window to use our stored procedure. This script will emulate the open transaction scenario.

The stored procedure has been executed successfully. Now we can see the output of its work – an open transaction is added to the srv.SessionTran table that we created at the previous stage. The srv.KillSession table serves for archiving the records.

When we execute that stored procedure again, it kills the open transaction automatically.

We can view the record of that killed transaction by simply executing

SELECT * FROM DemoDatabase.srv.KillSession

Best practices for managing open transactions

Database administrators should always be mindful of open transactions, as they can occur due to various factors. Let’s explore the primary reasons for idle transactions in SQL Server and the methods to prevent them.

Transactional Errors

Open transactions are often caused by transactional errors, uncommitted data reads, or long-held locks. To prevent these issues, we should identify long-running queries, check for blocking resources, use appropriate isolation levels, and analyze error logs.

Query Design and Indexing Efficiency

Poorly optimized queries, missing indexes, or neglecting COMMIT/ROLLBACK statements can result in open transactions. To mitigate this problem, focus on optimizing queries, implementing proper indexing, and ensuring transaction control statements are included.

Insufficient HDD Resources

An overloaded CPU, insufficient memory, slow disk I/O, or network bottlenecks can hinder query performance and contribute to open transactions. Recommendations include canceling unnecessary processes, rescheduling resource-intensive tasks, and considering hardware upgrades if necessary.

Deadlocks

Deadlocks occur when processes wait for resources held by each other, potentially leaving transactions open until resolved. To minimize open transactions due to deadlocks, set transaction timeouts, script deadlock identification procedures, and assign transaction priorities.

Transaction Exceptions

Exceptions within transaction blocks, if not handled properly, can prevent transaction completion. To address this issue, examine exceptions, enhance error handling mechanisms, and conduct thorough code reviews.

Conclusion

Open transactions can lead to significant issues in SQL Server operations, which is why it’s crucial to identify and address them promptly. In this article, we’ve outlined straightforward methods for detecting open transactions using the graphical user interface of SSMS and the dbForge SQL Complete add-in. Additionally, we’ve provided guidance on automating the processes of identifying and resolving open transactions.

SQL Complete offers robust coding assistance, code formatting, scripting, and notification features, making it a valuable tool in this context. You can try out the fully functional SQL Complete trial free of charge for 14 days, allowing you to integrate its capabilities into SSMS and leverage its enhanced functionality in your actual workload.