忘れられたトランザクションの自動削除

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 

 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 

 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