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 = 1This 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
In this script:
- sys.dm_exec_sessions and sys.dm_exec_connections refer to the system views
- InsertUTCDate identifies the UTC date and time when the record was created.
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
);
See the full code example below:
DECLARE @tbl TABLE (
SessionID INT
,TransactionID BIGINT
,IsSessionNotRequest BIT
,TransactionBeginTime DATETIME
);
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)
ta.[transaction_begin_time]
FROM sys.dm_tran_active_transactions AS ta
WHERE ta.[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]);
;
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;
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.
Handling Transactions
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.
Hardware Resource Challenges
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 Management
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.
