Erreur : Transaction log for database 'xxx' is full due to 'ACTIVE_TRANSACTION'

Erreur : Transaction log for database ‘xxx’ is full due to ‘ACTIVE_TRANSACTION’

19 décembre 2017

Commentaires fermés

Qui a fait tomber le sapin de Noël ??? Le fichier de transaction d’une base de données a grossit de façon exponentielle. Dans le cas présent, il s’agit de la base de données tempdb, jusqu’à saturation du disque ( Operating system error 112 (There is not enough space on the disk.) encountered) ou dès que la taille limite du fichier de log est atteinte. L’erreur est bien reportée dans le journal SQL Server. Bien que le journal de transaction soit plein, il est impossible de procéder à un skrink, précisément pour cette raison : une transaction est active. Cela étant, le fait que le fichier de transaction soit plein n’est pas le fond du problème, c’est une conséquence.

Essayons de reproduire le phénomène. Est-il besoin de le préciser, sait-on jamais : ailleurs qu’en PRODUCTION, idéalement sur une instance bac à sable où vous êtes seul.

Créons une table dans tempdb :

USE [tempdb]
GO

CREATE TABLE [dbo].[TblTest](
	[SomeId] [int],
	[SomeTinyntInterval] [tinyint],
	[SomeBit] [int],
	[SomeVarchar] [char](10),
	[SomeDateTime] [datetime],
	[SomeDateTimeInterval] [datetime],
	[SomeNumeric] [numeric](17, 5)
) ON [PRIMARY]
GO

USE [tempdb]

CREATE TABLE [dbo].[TblTest](

[SomeId] [int],

[SomeTinyntInterval] [tinyint],

[SomeBit] [int],

[SomeVarchar] [char](10),

[SomeDateTime] [datetime],

[SomeDateTimeInterval] [datetime],

[SomeNumeric] [numeric](17, 5)

) ON [PRIMARY]

Dans la même session, lançons une transaction d’injection de données, plus ou moins longue que nous plaçons en attente pendant 30 secondes (à personnaliser au besoin) pour nous donner le temps d’observer ce qui se passe à l’arrière boutique. Et enfin faisons comme si, nous ne connaissions pas le coupable :

USE [tempdb]
GO

BEGIN TRAN

	DECLARE	@DateStart DATETIME = DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0),
			@DateEnd DATETIME =  DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, -1),
			@IntStart TINYINT = 1,
			@IntEnd TINYINT = 10

	INSERT	INTO TblTest
	SELECT  TOP 1000000
			ABS(CHECKSUM(NEWID()))%2500+1 AS SomeId,
			CAST(((@IntEnd + 1) - @IntStart) * RAND(CHECKSUM(NEWID())) + @IntStart AS TINYINT) [SomeTinyntInterval],
			ABS(CHECKSUM(NEWID()) % 10) % 2 [SomeBit], 
			CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + 
			CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + 
			CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + 
			CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + 
			CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) [SomeVarchar], 
			DATEADD(MILLISECOND, (ABS(CHECKSUM(NEWID())) % 6000) * -1, 
				DATEADD(MINUTE, (ABS(CHECKSUM(NEWID())) % 1000000) * -1, GETDATE())) [SomeDateTime], 
			DATEADD(d, RAND(ABS(CAST(CAST(NEWID() AS BINARY(8)) AS INT)))*(CAST((@DateEnd - @DateStart) AS INT )), @DateStart) [SomeDateTimeInterval],
			(ABS(CHECKSUM(NEWID())) % 100001) + ((ABS(CHECKSUM(NEWID())) % 100001) * 0.00001) [SomeNumeric]
	
	FROM  
	Master.dbo.SysColumns t1, 
	Master.dbo.SysColumns t2

	WAITFOR DELAY '00:00:30' 

COMMIT TRAN

USE [tempdb]

BEGIN TRAN

DECLARE @DateStart DATETIME = DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0),

@DateEnd DATETIME = DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, -1),

@IntStart TINYINT = 1,

@IntEnd TINYINT = 10

INSERT INTO TblTest

SELECT TOP 1000000

ABS(CHECKSUM(NEWID()))%2500+1 AS SomeId,

CAST(((@IntEnd + 1) - @IntStart) * RAND(CHECKSUM(NEWID())) + @IntStart AS TINYINT) [SomeTinyntInterval],

ABS(CHECKSUM(NEWID()) % 10) % 2 [SomeBit],

CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) +

CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) [SomeVarchar],

DATEADD(MILLISECOND, (ABS(CHECKSUM(NEWID())) % 6000) * -1,

DATEADD(MINUTE, (ABS(CHECKSUM(NEWID())) % 1000000) * -1, GETDATE())) [SomeDateTime],

DATEADD(d, RAND(ABS(CAST(CAST(NEWID() AS BINARY(8)) AS INT)))*(CAST((@DateEnd - @DateStart) AS INT )), @DateStart) [SomeDateTimeInterval],

(ABS(CHECKSUM(NEWID())) % 100001) + ((ABS(CHECKSUM(NEWID())) % 100001) * 0.00001) [SomeNumeric]

FROM

Master.dbo.SysColumns t1,

Master.dbo.SysColumns t2

WAITFOR DELAY '00:00:30'

COMMIT TRAN

DBCC OPENTRAN

Rien de mieux qu’un DBCC et quelques DMV pour accéder à l’arrière boutique. Comme son nom l’indique DBCC OPENTRAN permet d’obtenir la transaction active la plus ancienne dans le contexte indiqué, en l’occurrence pour la base de données tempdb :

USE [tempdb]
GO

DBCC OPENTRAN

USE [tempdb]

DBCC OPENTRAN

On obtient en sortie : le SPID (55) correspond bien à la session depuis laquelle nous avons lancé l’injection de données. La date supposée de notre transaction est bien le 19 décembre 2017 à 20h59… Dans la vraie vie, la transaction date très souvent de methusalem (Start time), initiée par Duncan MacLeod dans ses jeunes années.

La sortie peut être redirigée vers un resultset au besoin en vue d’exploiter les données par la suite, comme ceci :

USE [tempdb]
GO

DBCC OPENTRAN WITH TABLERESULTS, NO_INFOMSGS

USE [tempdb]

DBCC OPENTRAN WITH TABLERESULTS, NO_INFOMSGS

Ce qui donne :

Pour lancer un DBCC OPENTRAN sur toutes les bases de données et afficher le résultat en mode classique :

SET NOCOUNT ON

DECLARE @SPID INT,
		@DatabaseName SYSNAME,
		@SQLDyn NVARCHAR(1000)

DECLARE @OpenTranStatus AS TABLE (ActiveTransaction VARCHAR(25), Details SQL_VARIANT)
DECLARE @PivotOpenTranStatus AS TABLE (SPID INT, DatabaseName SYSNAME, StartTime DATETIME)

DECLARE db_cursor CURSOR FOR  

	SELECT	DatabaseName = name
	FROM	master.dbo.sysdatabases 
	ORDER BY dbid

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @DatabaseName   

WHILE @@FETCH_STATUS = 0   
BEGIN   
      
	SET @SQLDyn = CONCAT('USE ', QUOTENAME(@DatabaseName), '; DBCC OPENTRAN WITH TABLERESULTS, NO_INFOMSGS;')

	INSERT INTO @OpenTranStatus (ActiveTransaction, Details)
	EXECUTE sp_executesql @SQLString = @SQLDyn

	INSERT	INTO @PivotOpenTranStatus
	SELECT	SPID = CAST([OLDACT_SPID] AS INT), 
			DatabaseName = @DatabaseName,
			StartTime = CAST([OLDACT_STARTTIME] AS DATETIME)
	FROM 
	(
		SELECT	ActiveTransaction, Details
		FROM	@OpenTranStatus
	) AS p
	PIVOT
	(
	MAX	(Details)
	FOR	ActiveTransaction IN
	(	[OLDACT_SPID], [OLDACT_STARTTIME])
	) AS pvt

	DELETE FROM @OpenTranStatus

	FETCH NEXT FROM db_cursor INTO @DatabaseName   

END   

CLOSE db_cursor   
DEALLOCATE db_cursor

SELECT	*
FROM	@PivotOpenTranStatus

SET NOCOUNT ON

DECLARE @SPID INT,

@DatabaseName SYSNAME,

@SQLDyn NVARCHAR(1000)

DECLARE @OpenTranStatus AS TABLE (ActiveTransaction VARCHAR(25), Details SQL_VARIANT)

DECLARE @PivotOpenTranStatus AS TABLE (SPID INT, DatabaseName SYSNAME, StartTime DATETIME)

DECLARE db_cursor CURSOR FOR

SELECT DatabaseName = name

FROM master.dbo.sysdatabases

ORDER BY dbid

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @DatabaseName

WHILE @@FETCH_STATUS = 0

BEGIN

SET @SQLDyn = CONCAT('USE ', QUOTENAME(@DatabaseName), '; DBCC OPENTRAN WITH TABLERESULTS, NO_INFOMSGS;')

INSERT INTO @OpenTranStatus (ActiveTransaction, Details)

EXECUTE sp_executesql @SQLString = @SQLDyn

INSERT INTO @PivotOpenTranStatus

SELECT SPID = CAST([OLDACT_SPID] AS INT),

DatabaseName = @DatabaseName,

StartTime = CAST([OLDACT_STARTTIME] AS DATETIME)

FROM

(

SELECT ActiveTransaction, Details

FROM @OpenTranStatus

) AS p

PIVOT

(

MAX (Details)

FOR ActiveTransaction IN

( [OLDACT_SPID], [OLDACT_STARTTIME])

) AS pvt

DELETE FROM @OpenTranStatus

FETCH NEXT FROM db_cursor INTO @DatabaseName

END

CLOSE db_cursor

DEALLOCATE db_cursor

SELECT *

FROM @PivotOpenTranStatus

DBCC INPUTBUFFER

Maintenant qu’on tient un suspect, le but du jeu est d’obtenir le maximum d’informations, typiquement en effectuant un DBCC INPUTBUFFER de la session en cause :

DBCC INPUTBUFFER(55)

1	DBCC INPUTBUFFER(55)

Dans le cas présent le résultat est assez clair : la requête remontée dans EventInfo correspond bien à celle que nous avons lancée précédemment pour l’injection de données.

sys.dm_exec_cursors

Cependant, dans un contexte réel, le résultat n’est pas toujours à la hauteur de nos attentes. Il peut s’agir d’un curseur appelé via une API côté applicatif et dans ce cas EventInfo affichera quelque chose dans le genre :

FETCH API_CURSOR0000000000000004

Comme dirait l’autre, c’est un peu comme se retrouver avec un sapin sans boule ou presque. Dans ce cas on fera appel à la fonction sys.dm_exec_cursors pour obtenir le détail qui nous fait tant défaut :

SELECT c.session_id, c.properties, c.creation_time, c.is_open, t.text
FROM sys.dm_exec_cursors (55) c
CROSS APPLY sys.dm_exec_sql_text (c.sql_handle) t

SELECT c.session_id, c.properties, c.creation_time, c.is_open, t.text

FROM sys.dm_exec_cursors (55) c

CROSS APPLY sys.dm_exec_sql_text (c.sql_handle) t

Un très bon post sur le sujet : https://www.sqlskills.com/blogs/joe/hunting-down-the-origins-of-fetch-api_cursor-and-sp_cursorfetch/

sys.dm_tran_*_transactions

Le niveau de détail est encore faible. On peut clairement faire mieux en interrogeant les DMV liées aux transactions, à savoir sys.dm_tran_database_transactions et sys.dm_tran_session_transactions en pré filtrant sur notre session d’injection de données (55) et l’état des transaction (en cours) :

SELECT	CASE tdt.database_transaction_type
			WHEN 1
				THEN 'Read/Write'
			WHEN 2
				THEN 'Read-Only'
			WHEN 3
				THEN 'System'
			ELSE 'Unknown Type - ' + convert(VARCHAR(50), tdt.database_transaction_type)
			END AS database_transaction_type
		,	CASE tdt.database_transaction_state
			WHEN 1
				THEN 'Uninitialized'
			WHEN 3
				THEN 'Not Started'
			WHEN 4
				THEN 'Active'
			WHEN 5
				THEN 'Prepared'
			WHEN 10
				THEN 'Committed'
			WHEN 11
				THEN 'Rolled Back'
			WHEN 12
				THEN 'Comitting'
			ELSE 'Unknown State - ' + convert(VARCHAR(50), tdt.database_transaction_state)
			END AS database_transaction_state
		,tdt.[database_transaction_begin_time]
		,tdt.[database_transaction_log_record_count]
		,tdt.[database_transaction_log_bytes_used]
		,tdt.[database_transaction_log_bytes_reserved]
		,tst.[session_id]
		,[database_name] = DB_NAME(tdt.database_id)
		,s.[login_name]
		,s.host_name
		,program_name = CASE LEFT(s.program_name, 29)
					WHEN 'SQLAgent - TSQL JobStep (Job '
						THEN 'SQLAgent Job: ' + (SELECT name FROM msdb..sysjobs sj WHERE SUBSTRING(s.program_name,32,32)=(SUBSTRING(sys.fn_varbintohexstr(sj.job_id),3,100))) + ' - ' + SUBSTRING(s.program_name, 67, LEN(s.program_name)-67)
					ELSE s.program_name
				END
		,s.[status]
		,is_cursor = IIF(ISNULL(ec.is_open, 0) = 0, 'False', 'True')
		,s.client_interface_name
		,s.client_version
		,c.client_net_address
		,object_name = OBJECT_NAME(st.objectid, st.dbid)
		,[sql_statement] = SUBSTRING (st.text, r.statement_start_offset/2,
				ABS(CASE WHEN r.statement_end_offset = -1
						THEN LEN(CONVERT(NVARCHAR(MAX), st.text)) * 2 
					ELSE r.statement_end_offset END - r.statement_start_offset)/2)
		,[query_plan] = qp.[query_plan]
		,cursor_properties = ec.properties
		,cursor_creation_time = ec.creation_time
		,cursor_state = ec.is_open
		,cursor_writes = ec.writes
		,cursor_reads = ec.reads
FROM	sys.dm_tran_database_transactions tdt
INNER	JOIN sys.dm_tran_session_transactions tst ON tst.[transaction_id] = tdt.[transaction_id]
INNER	JOIN sys.dm_exec_sessions s ON s.[session_id] = tst.[session_id]
INNER	JOIN sys.dm_exec_connections c ON c.[session_id] = tst.[session_id]
LEFT	JOIN sys.dm_exec_requests r ON r.[session_id] = tst.[session_id]
OUTER	APPLY sys.dm_exec_sql_text(c.[most_recent_sql_handle]) AS st
OUTER	APPLY sys.dm_exec_query_plan(r.[plan_handle]) AS qp
OUTER	APPLY sys.dm_exec_cursors(s.session_id) ec
OUTER	APPLY sys.dm_exec_sql_text(ec.sql_handle) t
WHERE	1 = 1
AND		tst.[session_id] = 55
AND		tdt.database_transaction_state > 3

SELECT CASE tdt.database_transaction_type

WHEN 1

THEN 'Read/Write'

WHEN 2

THEN 'Read-Only'

WHEN 3

THEN 'System'

ELSE 'Unknown Type - ' + convert(VARCHAR(50), tdt.database_transaction_type)

END AS database_transaction_type

, CASE tdt.database_transaction_state

WHEN 1

THEN 'Uninitialized'

WHEN 3

THEN 'Not Started'

WHEN 4

THEN 'Active'

WHEN 5

THEN 'Prepared'

WHEN 10

THEN 'Committed'

WHEN 11

THEN 'Rolled Back'

WHEN 12

THEN 'Comitting'

ELSE 'Unknown State - ' + convert(VARCHAR(50), tdt.database_transaction_state)

END AS database_transaction_state

,tdt.[database_transaction_begin_time]

,tdt.[database_transaction_log_record_count]

,tdt.[database_transaction_log_bytes_used]

,tdt.[database_transaction_log_bytes_reserved]

,tst.[session_id]

,[database_name] = DB_NAME(tdt.database_id)

,s.[login_name]

,s.host_name

,program_name = CASE LEFT(s.program_name, 29)

WHEN 'SQLAgent - TSQL JobStep (Job '

THEN 'SQLAgent Job: ' + (SELECT name FROM msdb..sysjobs sj WHERE SUBSTRING(s.program_name,32,32)=(SUBSTRING(sys.fn_varbintohexstr(sj.job_id),3,100))) + ' - ' + SUBSTRING(s.program_name, 67, LEN(s.program_name)-67)

ELSE s.program_name

END

,s.[status]

,is_cursor = IIF(ISNULL(ec.is_open, 0) = 0, 'False', 'True')

,s.client_interface_name

,s.client_version

,c.client_net_address

,object_name = OBJECT_NAME(st.objectid, st.dbid)

,[sql_statement] = SUBSTRING (st.text, r.statement_start_offset/2,

ABS(CASE WHEN r.statement_end_offset = -1

THEN LEN(CONVERT(NVARCHAR(MAX), st.text)) * 2

ELSE r.statement_end_offset END - r.statement_start_offset)/2)

,[query_plan] = qp.[query_plan]

,cursor_properties = ec.properties

,cursor_creation_time = ec.creation_time

,cursor_state = ec.is_open

,cursor_writes = ec.writes

,cursor_reads = ec.reads

FROM sys.dm_tran_database_transactions tdt

INNER JOIN sys.dm_tran_session_transactions tst ON tst.[transaction_id] = tdt.[transaction_id]

INNER JOIN sys.dm_exec_sessions s ON s.[session_id] = tst.[session_id]

INNER JOIN sys.dm_exec_connections c ON c.[session_id] = tst.[session_id]

LEFT JOIN sys.dm_exec_requests r ON r.[session_id] = tst.[session_id]

OUTER APPLY sys.dm_exec_sql_text(c.[most_recent_sql_handle]) AS st

OUTER APPLY sys.dm_exec_query_plan(r.[plan_handle]) AS qp

OUTER APPLY sys.dm_exec_cursors(s.session_id) ec

OUTER APPLY sys.dm_exec_sql_text(ec.sql_handle) t

WHERE 1 = 1

AND tst.[session_id] = 55

AND tdt.database_transaction_state > 3

La requête va nous fournir de précieuses informations sur le contexte de la transaction :

– login_name
– host_name
– program_name
– client_net_address (IP cliente)

Cette requête peut être lancée sans être précédée d’un DBCC OPENTRAN et DBCC INPUTBUFFER. Alors pourquoi ne pas l’avoir dit plus tôt ? On fait semblant de ne pas connaître le coupable ! Le résultat va aussi nous donner des précisions sur la requête (sql_statement), voir l’objet associé (object_name : procédure stockée ou autre) mais également nous permettre de quantifier la consommation dans le journal de transaction, voir colonnes :

– database_transaction_log_record_count
– database_transaction_log_bytes_used
– database_transaction_log_bytes_reserved

On ne va pas énumérer toutes les colonnes en sortie, chacun fait son marché. Le but de cet article était de remonter progressivement le fils pour identifier la transaction susceptible d’être responsable de la croissance inopinée de la base de données tempdb.

Cause(s)

Concrètement, cet incident m’avait été remonté par un client qui avait effectué une migration de version de SQL Server 2008 R2 vers SQL Server 2014. Le driver ODBC n’était pas à jour sur les machines clientes. Il a suffit d’installer la bonne version sur les clients identifiés par leur IP (client_net_address) pour corriger le problème. Ce n’est pas l’unique cause mais c’est relativement courant.

Industrialisation

Une fois le problème identifié, un rapport HTML adressé via DatabaseMail a été mis en place pour remonter les machines à l’origine de transactions restant ouvertes, en vue de mettre à jour leur driver ODBC.

1) Le paramètre @TranDelayInMinute (10 minutes par défaut) sert à filtrer sur la durée minium d’une transaction.
2) Il y a une exclusion sur l’objet sp_readrequest qui est appelé en boucle par DatabaseMail

USE [master]
GO

IF OBJECT_ID('SendReportOpenedTransactions', 'P') IS NOT NULL
	DROP PROCEDURE SendReportOpenedTransactions
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Sarah BESARD
-- Create date: 2017-12-19
-- Description:	Envoie par mail du report des transactions ouvertes depuis xxx minutes
-- =============================================
CREATE PROCEDURE [dbo].[SendReportOpenedTransactions]

	@TranDelayInMinute INT = 10
	
AS
BEGIN

	SET NOCOUNT ON

	DECLARE @DatabaseName SYSNAME,
			@TranDateMax DATETIME,
			@SQLDyn NVARCHAR(1000)

	SET @TranDelayInMinute = ABS(@TranDelayInMinute) * (-1)
	SET @TranDateMax = DATEADD(MINUTE, @TranDelayInMinute, GETDATE())

	SELECT	CASE tdt.database_transaction_type
				WHEN 1
					THEN 'Read/Write'
				WHEN 2
					THEN 'Read-Only'
				WHEN 3
					THEN 'System'
				ELSE 'Unknown Type - ' + convert(VARCHAR(50), tdt.database_transaction_type)
				END AS database_transaction_type
			,	CASE tdt.database_transaction_state
				WHEN 1
					THEN 'Uninitialized'
				WHEN 3
					THEN 'Not Started'
				WHEN 4
					THEN 'Active'
				WHEN 5
					THEN 'Prepared'
				WHEN 10
					THEN 'Committed'
				WHEN 11
					THEN 'Rolled Back'
				WHEN 12
					THEN 'Comitting'
				ELSE 'Unknown State - ' + convert(VARCHAR(50), tdt.database_transaction_state)
				END AS database_transaction_state
			,tdt.[database_transaction_begin_time]
			,tdt.[database_transaction_log_record_count]
			,tdt.[database_transaction_log_bytes_used]
			,tdt.[database_transaction_log_bytes_reserved]
			,tst.[session_id]
			,[database_name] = DB_NAME(tdt.database_id)
			,s.[login_name]
			,s.host_name
			,program_name = CASE LEFT(s.program_name, 29)
						WHEN 'SQLAgent - TSQL JobStep (Job '
							THEN 'SQLAgent Job: ' + (SELECT name FROM msdb..sysjobs sj WHERE SUBSTRING(s.program_name,32,32)=(SUBSTRING(sys.fn_varbintohexstr(sj.job_id),3,100))) + ' - ' + SUBSTRING(s.program_name, 67, LEN(s.program_name)-67)
						ELSE s.program_name
					END
			,s.[status]
			,is_cursor = IIF(ISNULL(ec.is_open, 0) = 0, 'False', 'True')
			,s.client_interface_name
			,s.client_version
			,c.client_net_address
			,object_name = OBJECT_NAME(st.objectid, st.dbid)
			--,[text] = ISNULL(st.[text], t.[text])
			,[sql_statement] = SUBSTRING (st.text, r.statement_start_offset/2,
					ABS(CASE WHEN r.statement_end_offset = -1
							THEN LEN(CONVERT(NVARCHAR(MAX), st.text)) * 2 
						ELSE r.statement_end_offset END - r.statement_start_offset)/2)
			,[query_plan] = qp.[query_plan]
			,cursor_properties = ec.properties
			,cursor_creation_time = ec.creation_time
			,cursor_state = ec.is_open
			,cursor_writes = ec.writes
			,cursor_reads = ec.reads
	INTO	#tmpDbTrans
	FROM	sys.dm_tran_database_transactions tdt
	INNER	JOIN sys.dm_tran_session_transactions tst ON tst.[transaction_id] = tdt.[transaction_id]
	INNER	JOIN sys.dm_exec_sessions s ON s.[session_id] = tst.[session_id]
	INNER	JOIN sys.dm_exec_connections c ON c.[session_id] = tst.[session_id]
	LEFT	JOIN sys.dm_exec_requests r ON r.[session_id] = tst.[session_id]
	OUTER	APPLY sys.dm_exec_sql_text(c.[most_recent_sql_handle]) AS st
	OUTER	APPLY sys.dm_exec_query_plan(r.[plan_handle]) AS qp
	OUTER	APPLY sys.dm_exec_cursors(s.session_id) ec
	OUTER	APPLY sys.dm_exec_sql_text(ec.sql_handle) t
	WHERE	1 = 1
	AND		tdt.database_transaction_state > 3
	AND		tdt.[database_transaction_begin_time] <= @TranDateMax
	AND		ISNULL(OBJECT_NAME(st.objectid, st.dbid), '') != 'sp_readrequest' -- Exclude MSDB process on DatabaseMail

	IF EXISTS(SELECT TOP 1 1 FROM #tmpDbTrans)
	BEGIN
 
 		DECLARE @Subject NVARCHAR(200) = CONCAT(@@SERVERNAME, ' : Opened transactions since ', ABS(@TranDelayInMinute) , ' minutes')

		-- Define CSS Style

		DECLARE @Style NVARCHAR(MAX)= '';

		SET @Style += +N'<style type="text/css">' 
			+ N'.h2  {font-family:Arial, sans-serif;font-size:14px;}'
			+ N'.tg  {border-collapse:collapse;border-spacing:0;border-color:#aaa;}'
			+ N'.tg td{font-family:Arial, sans-serif;font-size:10px;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:#aaa;color:#333;background-color:#fff;vertical-align:top;}'
			+ N'.tg th{font-family:Arial, sans-serif;font-size:10px;font-weight:normal;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:#aaa;color:#fff;background-color:#999999;}'
			+ N'.tg .tg-9ajh{font-weight:bold;background-color:#CCCCCC}' + N'.tg .tg-hgcj{font-weight:bold;text-align:center}'
			+ N'</style>';

		DECLARE @tableHTML NVARCHAR(MAX)= '';
 
		SET @tableHTML = N'<H2>Opened transactions since 60 minutes</H2>' 
			+ N'<table class="tg">'

		-- Define Column Headers and Column Span for each Header Column

		+ N'<tr>' 
		+ N'<th class="tg-hgcj" colspan="6">Transaction</th>' 
		+ N'<th class="tg-hgcj" colspan="7">Session</th>'
		+ N'<th class="tg-hgcj" colspan="2">Requête</th>'
		+ N'<th class="tg-hgcj" colspan="3">Client</th>'
		+ N'</tr>' 

		-- Define Column Sub-Headers

		+ N'<tr>'
		+ N'<td class="tg-9ajh">TranType</td>'
		+ N'<td class="tg-9ajh">TranState</td>' 
		+ N'<td class="tg-9ajh">BeginTime</td>'
		+ N'<td class="tg-9ajh">LogRecordCount</td>'
		+ N'<td class="tg-9ajh">LogBytesUsed</td>'    
		+ N'<td class="tg-9ajh">LogByteReserved</td>'    
		+ N'<td class="tg-9ajh">SessionId</td>'    
		+ N'<td class="tg-9ajh">DBName</td>'    
		+ N'<td class="tg-9ajh">LoginName</td>'
		+ N'<td class="tg-9ajh">HostName</td>'    
		+ N'<td class="tg-9ajh">ProgramName</td>'    
		+ N'<td class="tg-9ajh">ObjectName</td>'    
		+ N'<td class="tg-9ajh">Status</td>'
		+ N'<td class="tg-9ajh">T-SQL</td>'
		+ N'<td class="tg-9ajh">IsCursor</td>'
		+ N'<td class="tg-9ajh">ClientInterfaceName</td>'    
		+ N'<td class="tg-9ajh">ClientVersion</td>'    
		+ N'<td class="tg-9ajh">ClientNetAddress</td></tr>'

		DECLARE @xml NVARCHAR(MAX)

		SET @xml = CAST(
			( 
				SELECT	[database_transaction_type] AS 'td','',
						[database_transaction_state] AS 'td','',
						ISNULL(FORMAT([database_transaction_begin_time], 'yyyy-MM-dd HH:mm:ss'), '') AS 'td','',
						ISNULL([database_transaction_log_record_count],0) AS 'td','',
						ISNULL([database_transaction_log_bytes_used],0) AS 'td','',
						ISNULL([database_transaction_log_bytes_reserved],0) AS 'td','',
						[session_id] AS 'td','',
						ISNULL([database_name], '') AS 'td','',
						ISNULL([login_name], '') AS 'td','',
						ISNULL([host_name], '') AS 'td','',
						ISNULL([program_name], '') AS 'td','',
						ISNULL([object_name], '') AS 'td','',
						[status] AS 'td','',
						ISNULL([sql_statement], '') AS 'td','',
						[is_cursor] AS 'td','', 
						ISNULL([client_interface_name], '') AS 'td','',
						ISNULL([client_version], '') AS 'td','',
						ISNULL([client_net_address], '') AS 'td'
				FROM  #tmpDbTrans 
				ORDER BY [database_transaction_begin_time] 
				FOR XML PATH('tr'), ELEMENTS 
				) AS NVARCHAR(MAX)
			)

		DECLARE @body NVARCHAR(MAX)

		SET @body =  @Style + @tableHTML +  @xml + '</table>'

		DECLARE @profile NVARCHAR(128)

		SELECT	TOP 1 @profile = [name]
		FROM	[msdb].[dbo].[sysmail_profile]

		EXEC msdb.dbo.sp_send_dbmail
		@profile_name = @profile,
		@body = @body,
		@body_format ='HTML',
		@recipients = 'sqlteam@concatskills.com',
		@subject = @Subject;

	END

END
GO

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

USE [master]

IF OBJECT_ID('SendReportOpenedTransactions', 'P') IS NOT NULL

DROP PROCEDURE SendReportOpenedTransactions

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

-- Author: Sarah BESARD

-- Create date: 2017-12-19

-- Description: Envoie par mail du report des transactions ouvertes depuis xxx minutes

-- =============================================

CREATE PROCEDURE [dbo].[SendReportOpenedTransactions]

@TranDelayInMinute INT = 10

BEGIN

SET NOCOUNT ON

DECLARE @DatabaseName SYSNAME,

@TranDateMax DATETIME,

@SQLDyn NVARCHAR(1000)

SET @TranDelayInMinute = ABS(@TranDelayInMinute) * (-1)

SET @TranDateMax = DATEADD(MINUTE, @TranDelayInMinute, GETDATE())

SELECT CASE tdt.database_transaction_type

WHEN 1

THEN 'Read/Write'

WHEN 2

THEN 'Read-Only'

WHEN 3

THEN 'System'

ELSE 'Unknown Type - ' + convert(VARCHAR(50), tdt.database_transaction_type)

END AS database_transaction_type

, CASE tdt.database_transaction_state

WHEN 1

THEN 'Uninitialized'

WHEN 3

THEN 'Not Started'

WHEN 4

THEN 'Active'

WHEN 5

THEN 'Prepared'

WHEN 10

THEN 'Committed'

WHEN 11

THEN 'Rolled Back'

WHEN 12

THEN 'Comitting'

ELSE 'Unknown State - ' + convert(VARCHAR(50), tdt.database_transaction_state)

END AS database_transaction_state

,tdt.[database_transaction_begin_time]

,tdt.[database_transaction_log_record_count]

,tdt.[database_transaction_log_bytes_used]

,tdt.[database_transaction_log_bytes_reserved]

,tst.[session_id]

,[database_name] = DB_NAME(tdt.database_id)

,s.[login_name]

,s.host_name

,program_name = CASE LEFT(s.program_name, 29)

WHEN 'SQLAgent - TSQL JobStep (Job '

ELSE s.program_name

END

,s.[status]

,is_cursor = IIF(ISNULL(ec.is_open, 0) = 0, 'False', 'True')

,s.client_interface_name

,s.client_version

,c.client_net_address

,object_name = OBJECT_NAME(st.objectid, st.dbid)

--,[text] = ISNULL(st.[text], t.[text])

,[sql_statement] = SUBSTRING (st.text, r.statement_start_offset/2,

ABS(CASE WHEN r.statement_end_offset = -1

THEN LEN(CONVERT(NVARCHAR(MAX), st.text)) * 2

ELSE r.statement_end_offset END - r.statement_start_offset)/2)

,[query_plan] = qp.[query_plan]

,cursor_properties = ec.properties

,cursor_creation_time = ec.creation_time

,cursor_state = ec.is_open

,cursor_writes = ec.writes

,cursor_reads = ec.reads

INTO #tmpDbTrans

FROM sys.dm_tran_database_transactions tdt

INNER JOIN sys.dm_tran_session_transactions tst ON tst.[transaction_id] = tdt.[transaction_id]

INNER JOIN sys.dm_exec_sessions s ON s.[session_id] = tst.[session_id]

INNER JOIN sys.dm_exec_connections c ON c.[session_id] = tst.[session_id]

LEFT JOIN sys.dm_exec_requests r ON r.[session_id] = tst.[session_id]

OUTER APPLY sys.dm_exec_sql_text(c.[most_recent_sql_handle]) AS st

OUTER APPLY sys.dm_exec_query_plan(r.[plan_handle]) AS qp

OUTER APPLY sys.dm_exec_cursors(s.session_id) ec

OUTER APPLY sys.dm_exec_sql_text(ec.sql_handle) t

WHERE 1 = 1

AND tdt.database_transaction_state > 3

AND tdt.[database_transaction_begin_time] <= @TranDateMax

AND ISNULL(OBJECT_NAME(st.objectid, st.dbid), '') != 'sp_readrequest' -- Exclude MSDB process on DatabaseMail

IF EXISTS(SELECT TOP 1 1 FROM #tmpDbTrans)

BEGIN

DECLARE @Subject NVARCHAR(200) = CONCAT(@@SERVERNAME, ' : Opened transactions since ', ABS(@TranDelayInMinute) , ' minutes')

-- Define CSS Style

DECLARE @Style NVARCHAR(MAX)= '';

SET @Style += +N'<style type="text/css">'

+ N'.h2 {font-family:Arial, sans-serif;font-size:14px;}'

+ N'.tg {border-collapse:collapse;border-spacing:0;border-color:#aaa;}'

+ N'.tg td{font-family:Arial, sans-serif;font-size:10px;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:#aaa;color:#333;background-color:#fff;vertical-align:top;}'

+ N'.tg th{font-family:Arial, sans-serif;font-size:10px;font-weight:normal;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:#aaa;color:#fff;background-color:#999999;}'

+ N'.tg .tg-9ajh{font-weight:bold;background-color:#CCCCCC}' + N'.tg .tg-hgcj{font-weight:bold;text-align:center}'

+ N'</style>';

DECLARE @tableHTML NVARCHAR(MAX)= '';

SET @tableHTML = N'<H2>Opened transactions since 60 minutes</H2>'

+ N'<table class="tg">'

-- Define Column Headers and Column Span for each Header Column

+ N'<tr>'

+ N'<th class="tg-hgcj" colspan="6">Transaction</th>'

+ N'<th class="tg-hgcj" colspan="7">Session</th>'

+ N'<th class="tg-hgcj" colspan="2">Requête</th>'

+ N'<th class="tg-hgcj" colspan="3">Client</th>'

+ N'</tr>'

-- Define Column Sub-Headers

+ N'<tr>'

+ N'<td class="tg-9ajh">TranType</td>'

+ N'<td class="tg-9ajh">TranState</td>'

+ N'<td class="tg-9ajh">BeginTime</td>'

+ N'<td class="tg-9ajh">LogRecordCount</td>'

+ N'<td class="tg-9ajh">LogBytesUsed</td>'

+ N'<td class="tg-9ajh">LogByteReserved</td>'

+ N'<td class="tg-9ajh">SessionId</td>'

+ N'<td class="tg-9ajh">DBName</td>'

+ N'<td class="tg-9ajh">LoginName</td>'

+ N'<td class="tg-9ajh">HostName</td>'

+ N'<td class="tg-9ajh">ProgramName</td>'

+ N'<td class="tg-9ajh">ObjectName</td>'

+ N'<td class="tg-9ajh">Status</td>'

+ N'<td class="tg-9ajh">T-SQL</td>'

+ N'<td class="tg-9ajh">IsCursor</td>'

+ N'<td class="tg-9ajh">ClientInterfaceName</td>'

+ N'<td class="tg-9ajh">ClientVersion</td>'

+ N'<td class="tg-9ajh">ClientNetAddress</td></tr>'

DECLARE @xml NVARCHAR(MAX)

SET @xml = CAST(

(

SELECT [database_transaction_type] AS 'td','',

[database_transaction_state] AS 'td','',

ISNULL(FORMAT([database_transaction_begin_time], 'yyyy-MM-dd HH:mm:ss'), '') AS 'td','',

ISNULL([database_transaction_log_record_count],0) AS 'td','',

ISNULL([database_transaction_log_bytes_used],0) AS 'td','',

ISNULL([database_transaction_log_bytes_reserved],0) AS 'td','',

[session_id] AS 'td','',

ISNULL([database_name], '') AS 'td','',

ISNULL([login_name], '') AS 'td','',

ISNULL([host_name], '') AS 'td','',

ISNULL([program_name], '') AS 'td','',

ISNULL([object_name], '') AS 'td','',

[status] AS 'td','',

ISNULL([sql_statement], '') AS 'td','',

[is_cursor] AS 'td','',

ISNULL([client_interface_name], '') AS 'td','',

ISNULL([client_version], '') AS 'td','',

ISNULL([client_net_address], '') AS 'td'

FROM #tmpDbTrans

ORDER BY [database_transaction_begin_time]

FOR XML PATH('tr'), ELEMENTS

) AS NVARCHAR(MAX)

)

DECLARE @body NVARCHAR(MAX)

SET @body = @Style + @tableHTML + @xml + '</table>'

DECLARE @profile NVARCHAR(128)

SELECT TOP 1 @profile = [name]

FROM [msdb].[dbo].[sysmail_profile]

EXEC msdb.dbo.sp_send_dbmail

@profile_name = @profile,

@body = @body,

@body_format ='HTML',

@recipients = 'sqlteam@concatskills.com',

@subject = @Subject;

END

Pour l’envoie de mail embarquant le résultat de la requête, soit la liste des transactions ouvertes dans un tableau HTML, je me suis largement inspirée de cette source pour styliser un peu le contenu :
https://www.sqlshack.com/format-dbmail-with-html-and-css
Attention aux valeurs nulles, elles créent des décalages dans le tableau si elles ne sont pas remplacées. Voici un exemple de rapport :

Reste à créer le job qui exécutera la procédure stockée comme suit à intervalle régulier :

USE [master]
GO

EXEC dbo.SendReportOpenedTransactions @TranDelayInMinute = 10

USE [master]

EXEC dbo.SendReportOpenedTransactions @TranDelayInMinute = 10

En cas d’alerte…

Et pour finir, si vous recevez un rapport faisant état de transactions ouvertes sur une période suffisamment critique, lancez les actions suivantes :

– Vérifier si la transaction est toujours en cours
– Vérifier qu’il ne s’agit pas d’une transaction orpheline (mauvaise gestion des transactions, driver ODBC obsolète, etc)
– Transmettez les infos collectées aux équipes de développement et/ou aux métiers (host_name, client_net_address, etc)
– Analyser la requête en question (durée d’exécution conforme ou non)
– Killer la session en dernier recours

Auteur

Sarah Béquet

Archietcte Data Microsoft, les maîtres mots sont : performance, industrialisation, méthodologie & bonne humeur.

Derniers articles

2019.12.02BI & Big Data Etude des contextes en DAX (4/4) : Transition de contexte
2019.11.25BI & Big Data Etude des contextes en DAX (3/4) : Contexte de requête
2019.11.17BI & Big Data Etude des contextes en DAX (2/4) : Contexte de filtre
2019.11.11BI & Big Data Etude des contextes en DAX (1/4) : Contexte de ligne

Articles liés

Optimiseur de requête

Droits Active Directory pour la création d'un groupe de disponibilité

DELETE ou UPDATE en masse (patterns)

Scripter la création et suppression des index

Blog