Vous êtes ici :
Une autre approche ici pour éviter des verrous bloquants (voir l’autre script « Kill lead blocker automatique ») : déconnecter toutes les transactions interminables, qu’elles soient bloquantes ou pas (si elles ne le sont pas, elles peuvent le devenir !). C’est brutal et c’est bien loin d’être une véritable solution, le problème provenant de l’applicatif, mais cela m’a déjà servi en pansement temporaire pour une application qui « oublie » de fermer ses transactions.
Dans le script, la durée des transactions à déconnecter est de 30 minutes, à configurer. On peut aussi filtrer sur une base en particulier. Et une table de log, à créer dans msdb, journalise les déconnexions effectuées.
Transact-SQL
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 |
/*------------------------------------------------------------------- [SCRIPT] automatic kill long transactions [DATABASE] master [DESCRIPTION] use it as SQL Agent Job [MAJ PAR] DATAFLY - Arian Papillon [DATEMAJ] 20190122 -------------------------------------------------------------------*/ /* -- Create log table in msdb -- USE [msdb] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[LongTransactionKillLog]( [Id] [int] IDENTITY(1,1) NOT NULL, [event_time] [datetime2](0) NULL, [session_id] [int] NULL, [client_net_address] [varchar](20) NULL, [login_name] [sysname] NOT NULL, [host_name] [sysname] NOT NULL, [program_name] [varchar](200) NULL, [duration] [varchar](20) NULL, [transaction_begin_time] [datetime2](0) NULL, [last_read] [datetime2](0) NULL, [last_write] [datetime2](0) NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[LongTransactionKillLog] ADD DEFAULT (sysdatetime()) FOR [event_time] GO */ DECLARE @SpidToKill INT, @SQLCmd VARCHAR(200); WHILE (1=1) BEGIN SET @SpidToKill = NULL SELECT TOP (1) @SpidToKill = s.session_id FROM sys.dm_tran_session_transactions s INNER JOIN sys.dm_tran_database_transactions dt ON s.transaction_id = dt.transaction_id WHERE database_transaction_log_record_count > 0 -- configure filters here : duration threshold in seconds, database id AND DATEDIFF(SECOND, database_transaction_begin_time, GETDATE()) > 1800 -- 30 mn -- AND dt.database_id = 2 OPTION (RECOMPILE); SELECT @SpidToKill; IF @SpidToKill IS NULL BEGIN BREAK; END; INSERT msdb.dbo.LongTransactionKillLog ( session_id, client_net_address, login_name, host_name, program_name, duration, transaction_begin_time, last_read, last_write ) SELECT s.session_id, c.client_net_address, se.login_name, se.host_name, se.program_name, Duration = CASE WHEN DATEDIFF(SECOND, a.transaction_begin_time, GETDATE()) >= 3600 THEN CAST(CAST(DATEDIFF(SECOND, a.transaction_begin_time, GETDATE()) / 3600. AS DECIMAL(7, 2)) AS VARCHAR) + ' Hr' WHEN DATEDIFF(SECOND, a.transaction_begin_time, GETDATE()) >= 60 THEN CAST(CAST(DATEDIFF(SECOND, a.transaction_begin_time, GETDATE()) / 60. AS DECIMAL(7, 2)) AS VARCHAR) + ' Min' ELSE CAST(CAST(DATEDIFF(MILLISECOND, a.transaction_begin_time, GETDATE()) / 1000. AS DECIMAL(7, 2)) AS VARCHAR) + ' Sec' END, a.transaction_begin_time, c.last_read, c.last_write FROM sys.dm_tran_active_transactions a LEFT JOIN sys.dm_tran_session_transactions s ON a.transaction_id = s.transaction_id LEFT JOIN sys.[dm_exec_connections] c ON s.session_id = c.session_id LEFT JOIN sys.dm_exec_sessions se ON c.session_id = se.session_id LEFT JOIN sys.dm_tran_database_transactions dt ON a.transaction_id = dt.transaction_id LEFT JOIN sys.sysprocesses AS sp ON sp.spid = s.session_id WHERE dt.database_id = 2 AND s.session_id = @SpidToKill OPTION (RECOMPILE); SET @SQLCmd = 'KILL ' + CAST(@SpidToKill AS NVARCHAR(6)); EXEC (@SQLCmd); END; |
Table of Contents