Introduction
It is often the case when an MS SQL Server transaction is being forgotten by the initiator. The best example would be the following: a script is executed in SSMS which, via the ‘begin tran’ instruction, starts a transaction and an error occurs; however, ‘commit’ or ‘rollback’ do not go through and the execution initiator has left this query for a long time. As a result, more and more fluctuation appears when it comes to blocking the queries which request access to closed-off resources (tables and server resources such as RAM, CPU and the input-output system).
In this article, we’ll look at one of the ways you can automate the forgotten transaction deletion process.
The solution
Let’s define a forgotten transaction as an active (currently executed) transaction which, during a sufficiently large span of time T, has no active (currently executed) queries.
Here’s the general algorithm for deleting such transactions:
- Creating a table to store and analyze information about currently forgotten transactions as well as a table to sort and archive the transactions selected from the first table by deletion actions.
- Gathering information (transactions and their sessions which have no queries, i.e., the transactions that have been executed and forgotten within a specified timespan T.
- Refreshing the table containing all currently forgotten transactions we got in step 1 (if a forgotten transaction has acquired an active query, then such a transaction will be deleted from this table).
- Retrieving the sessions we need to kill (a session has at least one transaction that was placed as forgotten into the table from step 1 K or more times and the session had a missing active query the same amount of times).
- Archiving the data that we’re going to delete (details about the sessions, connections and transactions that will be killed).
- Deleting the selected sessions.
- Deleting the processed entries along with those that cannot be removed and have been in the table from step 1 for too long.
Now, let’s see how we can implement this algorithm.
First of all, we’ll need to create a table to store and analyze the information about all currently forgotten transactions:
USE [DB_NAME] GO 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) 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
Here:
1) SessionID — session identifier
2) TransactionID — forgotten transaction identifier
3) CountTranNotRequest — the amount of times a transaction has been registered as forgotten
4) CountSessionNotRequest — the amount of times a session without active queries has been registered and had a forgotten transaction
5) TransactionBeginTime — date and time of the forgotten transaction’s initiation
6) InsertUTCDate — date and time of entry creation (UTC)
7) UpdateUTCDate — date and time of entry update (UTC)
Next, we’ll create a table to archive and sort the transactions from the first table by deletion actions:
[expand title =”Code“]
USE [DB_NAME] GO 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
[/expand]
Here, all fields are taken from the ‘sys.dm_exec_sessions’ and ‘sys.dm_exec_connections’ system representations, and ‘InsertUTCDate’ specifies the UTC time the entry was created.
Then, to complete the remaining steps, let’s implement the [srv].[AutoKillSessionTranBegin] stored procedure as follows:
[expand title =”Code“]
USE [DB_NAME]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [srv].[AutoKillSessionTranBegin]
@minuteOld int, --age of the executed transaction (T min.)
@countIsNotRequests int --amount of times it has been placed into the table (K)
AS
BEGIN
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
declare @tbl table (
SessionID int,
TransactionID bigint,
IsSessionNotRequest bit,
TransactionBeginTime datetime
);
--retrieving information (transactions and theirs session which have no requests, i.e, transactions that were initiated 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) 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]);
--refreshing the table containing all initiated transactions with no requests
;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] = t.[TransactionBeginTime]
when not matched by target then
insert (
[SessionID]
,[TransactionID]
,[TransactionBeginTime]
)
values (
t.[SessionID]
,t.[TransactionID]
,t.[TransactionBeginTime]
)
when not matched by source then delete;
--list of sessions which need to be deleted (those that contain forgotten transactions)
declare @kills table (
SessionID int
);
--детальная информация для архива
declare @kills_copy table (
SessionID int,
TransactionID bigint,
CountTranNotRequest tinyint,
CountSessionNotRequest tinyint,
TransactionBeginTime datetime
)
--gathering the sessions we need to kill
--a session has at least one transaction which was marked as having no requests @countIsNotRequests times
--and this session was marked as having no active requests the same amount of times
insert into @kills_copy (
SessionID,
TransactionID,
CountTranNotRequest,
CountSessionNotRequest,
TransactionBeginTime
)
select SessionID,
TransactionID,
CountTranNotRequest,
CountSessionNotRequest,
TransactionBeginTime
from srv.SessionTran
where [CountTranNotRequest]>=@countIsNotRequests
and [CountSessionNotRequest]>=@countIsNotRequests
and [TransactionBeginTime]<=DateAdd(minute,-@minuteOld,GetDate());
--archiving the data we need to delete (details on the sessions to be deleted, 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;
--gathering sessions
insert into @kills (
SessionID
)
select [SessionID]
from @kills_copy
group by [SessionID];
declare @SessionID int;
--deleting 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]);
--Deleting the processed entries along with those that cannot be removed and have been in the table for too long
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
GO
[/expand]
Step 7 of the algorithm is implemented through one of these two counters – CountTranNotRequest or CountSessionNotRequest – reaching a value of 250.
The result
In this article, we’ve looked at an implementation of a process which automatically deletes forgotten transactions.
This method allows us to automate the forgotten transaction deletion process. This results in decreasing or stopping the fluctuation growth in the blocking produced by such transactions. So, the DBMS performance is protected from the actions which can result in forgotten transactions.
Sources:
» sys.dm_exec_requests
» sys.dm_tran_active_transactions
» sys.dm_tran_session_transactions
» sys.dm_exec_sql_text
» sys.dm_exec_sessions
» sys.dm_exec_connections
» KILL
