Automated Deletion of Forgotten Transactions
- Tutorial
Foreword
Quite often there are situations when a transaction in MS SQL Server is forgotten by those who launched it. The most common example of this is running a script in SSMS, where the transaction is explicitly opened with the begin tran instruction, then an error occurs, but commit or rollback tran does not occur, and the launch initiator safely departed for a long time from this request. As a result, over time, more and more fluctuations arise in terms of blocking for requests that request access to blocked resources (tables, server resources (RAM, CPU, input-output system).
This article will examine an example of automation of deleting forgotten transactions.
Decision
By a forgotten transaction, we mean that active (running) transaction in which for some sufficiently large period of time T there are no active (executed) requests.
First, we give a general algorithm for deleting such transactions:
- We create a table for storing and analyzing information on current forgotten transactions, as well as a table for archiving by deletion actions of transactions selected from the first table for subsequent analysis
- We collect information (transactions and their sessions that have no requests, i.e., transactions that are started and forgotten for a certain time T)
- We update the table of current forgotten transactions from item 1 (if a forgotten transaction has an active request, then such a transaction becomes unforgettable and is deleted from the first table of item 1)
- We collect the sessions that need to be killed (a session has at least one transaction that has hit a certain number of times K as forgotten in the table from step 1 and the same number of times the session itself has no active queries)
- We archive what we are going to delete (detailed information about deleted sessions, connections and transactions)
- Delete selected sessions
- We delete the processed records, as well as those that cannot be deleted and they are too long in table 1
Next, we give an example of the implementation of the above algorithm.
Let's create a table for storing and analyzing information on current forgotten transactions as follows:
Transaction hit table as forgotten with their sessions
USE [ИМЯ_БД]
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 - identifier of a forgotten transaction
3) CountTranNotRequest - the number of times the fact that the transaction was forgotten
4) CountSessionNotRequest - the number of times the fact that the session was without active of requests and contained a forgotten transaction
5) TransactionBeginTime - date and time of the beginning of the forgotten transaction
6) InsertUTCDate - date and time of creation of the record in UTC
7) UpdateUTCDate - date and time of the change in the record in UTC
Create a table for archiving by actions of deleting the transactions selected from the first table for afterwards of analysis:
Table for archiving remote sessions for forgotten transactions
USE [ИМЯ_БД]
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
Here, all the fields from the system views sys.dm_exec_sessions and sys.dm_exec_connections , and InsertUTCDate are the date and time the record was created in UTC.
Further, to implement the remaining items, we implement the stored procedure [srv]. [AutoKillSessionTranBegin] as follows:
Implementing the stored procedure [srv]. [AutoKillSessionTranBegin]
USE [ИМЯ_БД]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [srv].[AutoKillSessionTranBegin]
@minuteOld int, --старость запущенной транзакции (T мин)
@countIsNotRequests int --кол-во попаданий в таблицу (K)
AS
BEGIN
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
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] = 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;
--список сессий для удаления (содержащие забытые транзакции)
declare @kills table (
SessionID int
);
--детальная информация для архива
declare @kills_copy table (
SessionID int,
TransactionID bigint,
CountTranNotRequest tinyint,
CountSessionNotRequest tinyint,
TransactionBeginTime datetime
)
--собираем те сессии, которые нужно убить
--у сессии есть хотя бы одна транзакция, которая попала @countIsNotRequests раз как без активных запросов и столько же раз у самой сессии нет активных запросов
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());
--архивируем что собираемся удалить (детальная информация про удаляемые сессии, подключения и транзакции)
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;
--собираем сессии
insert into @kills (
SessionID
)
select [SessionID]
from @kills_copy
group by [SessionID];
declare @SessionID int;
--непосредственное удаление выбранных сессий
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]);
--удаление обработанных записей, а также те, что невозможно удалить и они находятся слишком долго в таблице на рассмотрение
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
Here, item 7 of the algorithm is implemented upon reaching one of the counters CountTranNotRequest or CountSessionNotRequest of 250.
Result
This article examined an example of implementing automated deletion of forgotten transactions.
This method allows you to automate the process of deleting forgotten transactions, the result of which is the suppression of attempts to increase fluctuations in terms of locks emanating from such transactions. As a result, the DBMS performance is protected from such actions of initiators to launch transactions that may become forgotten in the future.
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