Monitoring TRUNCATE TABLE Operations

Monitoring SQL Server TRUNCATE TABLE operations has always been a challenge--not just for me, but seemingly for the rest of the world too. But I think I've found a workable solution with Extended Events. It's not bullet-proof, but it should work for most cases. Feel free to skip ahead to the Extended Events definition or continue reading.

What Didn't Work

Even though the solution I settled on uses Extended Events, there's no event specific to TRUNCATE TABLE operations in Extended Events.

SELECT o.name event_name, o.description
FROM sys.dm_xe_objects o
WHERE o.object_type = 'event'
AND o.name LIKE '%TRUNCATE%'

These particular Extended Events events kinda/sorta work if you filter on LIKE %TRUNCATE%TABLE%. But there are a lot of false positives (truncation of #temp tables, T-SQL comments that match the LIKE pattern, strangely named objects that cause a query to match the LIKE pattern, etc.) It was also difficult to parse out the name of the table being truncated from various query strings with differing formats.

• sp_statement_completed
• sql_statement_completed
• rpc_completed
• sql_batch_completed

There's no related event in Event Notifications for TRUNCATE TABLE operations. (DDL events are a subset of Extended Events events--so there's no DDL event for TRUNCATE TABLE operations either.)

SELECT *
FROM sys.event_notification_event_types
WHERE type_name LIKE '%TRUNCATE%'

What Should Work

I never tried SQL Server Audit. It's a feature I've rarely used and because of that, I'm not really comfortable using it. But I thought I'd at least mention it--as I understand things, you should be able to monitor TRUNCATE TABLE operations with this feature.

What Worked For Me

OK, so back to Extended Events. MS documentation tells us a schema modification lock (Sch-M) is required for TRUNCATE TABLE. So I created an Extended Event session on the lock_acquired event that filters on Sch-M locks and queries LIKE %TRUNCATE%TABLE%. I've shared the script for the Extended Events session on Codeberg, but it's also here within this post:

CREATE EVENT SESSION SchemaModificationLockAcquiredTruncateTable ON SERVER 
ADD EVENT sqlserver.lock_acquired 
(
	SET collect_database_name = (1), collect_resource_description = (1) 
	ACTION
	(
		sqlserver.client_app_name, 
		sqlserver.client_hostname, 
		sqlserver.database_name, 
		sqlserver.server_principal_name, 
		sqlserver.sql_text
	) 
	WHERE 
	(
		[package0].[equal_boolean]([sqlserver].[is_system], (0))
		AND [package0].[equal_uint64]([mode], 'SCH_M')
		AND [object_id] > (0)
		AND [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text], N'%TRUNCATE%TABLE%')
		AND [resource_2] = (0)
	)
) 
ADD TARGET package0.event_file 
(
	SET filename = N'SchemaModificationLockAcquiredTruncateTable',
	max_file_size = (128),
	max_rollover_files = (4)
)
WITH 
(
	MAX_MEMORY = 4096 KB,
	EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
	MAX_DISPATCH_LATENCY = 30 SECONDS,
	MAX_EVENT_SIZE = 0 KB,
	MEMORY_PARTITION_MODE = NONE,
	TRACK_CAUSALITY = OFF,
	STARTUP_STATE = OFF
)
GO

Extended Event Session Notes

You can see the descriptions of the Event Fields for the events of a particular Extended Events session in the SSMS GUI. The following query also returns the same data--I used it to help me identify the data for some of the Event Fields discussed below.

SELECT o.name event_name,
	c.name event_field,
	c.description,
	c.type_name field_type
FROM sys.dm_xe_objects o
JOIN sys.dm_xe_object_columns c ON o.name = c.object_name
WHERE o.object_type = 'event'
AND o.name = N'lock_acquired'
AND c.column_type = N'data'
ORDER BY c.name

There was a concern that related lock_acquired events are so frequent & excessive that the Extended Events session may impact server performance. So I filtered heavily to reduce the 'noise'.

• [mode] = 'SCH_M': Sch-M locks should be one of the more infrequent lock types used in the field. But be aware index rebuild operations require a Sch-M lock.
• [sql_text] LIKE '%TRUNCATE%TABLE%': No surprises here. We only want to capture events for operations that truncate a table. We don't care about other operations that require a Sch-M lock.
• [is_system] = 0: we only want events for locks acquired by users.
• [object_id] > 0: object_id is the ID of the locked object, when lock_resource_type is OBJECT. (For other lock resource types it will be 0)
• [resource_2] = 0: the description for this field is "The ID of the lock partition, when lock_resource_type is OBJECT, and resource_1 is 0." This led me to think [resource_2] identified the partition number of the table being truncated. However, the tables being truncated were not partitioned. I further observed each TRUNCATE TABLE operation generated dozens of lock_acquired events (numbered 0-X in the [resource_2] field). On one server, it was 128 events (0-127), on another server, it was 64 events (0-63), and on a third server, it was a single event. Further reading and research led me to the topic "lock partitioning", which I wasn't familiar with. I didn't find much documentation on the topic, other than How It Works: SQL Server Lock Partitioning and Strange Sch-S / Sch-M Deadlock on Machines with 16 or More Schedulers. Between those two articles, I surmised on two of the servers I tested on there was one event per processor/vCPU, and on the third server there was one event because the server had less than 16 processors/vCPU. Based on that assumption, I filter out anything > 0. However, more research may be needed.
• [database_name] = 'database_name': (optional) Note there is a Global Field for [database_name] (which appears to be the database name specified in the connection string), and an event-specific Event Field for [database_name] (which appears to be the database name where the truncated object resides).

This has not been tested with Accelerated Database Recovery enabled.

Querying The Extended Event File Target

I used my XEvent-Shredder scripts to generate this query (columns/fields I don't care about are commented out):

;WITH XEventsAS (
	SELECT object_name, CAST(event_data AS XML) AS event_data
	FROM sys.fn_xe_file_target_read_file('SchemaModificationLockAcquiredTruncateTable*.xel', NULL, NULL, NULL)
	)
SELECT
	--XEvent timestamp is UTC. Adjust for local server time.
	DATEADD(HOUR, DATEDIFF(HOUR, GETUTCDATE(), GETDATE()), event_data.value('(/event/@timestamp)[1]', 'DATETIME')) AS [timestamp],
	event_data.value('(/event/data[@name=''associated_object_id'']/value)[1]', 'BIGINT') AS [associated_object_id],
	event_data.value('(/event/action[@name=''client_app_name'']/value)[1]', 'NVARCHAR(128)') AS [client_app_name],
	event_data.value('(/event/action[@name=''client_hostname'']/value)[1]', 'NVARCHAR(128)') AS [client_hostname],
	--event_data.value ('(/event/data[@name=''database_id'']/value)[1]', 'BIGINT') AS [database_id],
	event_data.value('(/event/action[@name=''database_name'']/value)[1]', 'NVARCHAR(128)') AS [database_name_Global],
	event_data.value('(/event/data[@name=''database_name'']/value)[1]', 'NVARCHAR(128)') AS [database_name_Event],
	--event_data.value ('(/event/data[@name=''duration'']/value)[1]', 'BIGINT') AS [duration],
	--event_data.value ('(/event/data[@name=''lockspace_nest_id'']/value)[1]', 'BIGINT') AS [lockspace_nest_id],
	--event_data.value ('(/event/data[@name=''lockspace_sub_id'']/value)[1]', 'BIGINT') AS [lockspace_sub_id],
	--event_data.value ('(/event/data[@name=''lockspace_workspace_id'']/value)[1]', 'NVARCHAR(128)') AS [lockspace_workspace_id],
	event_data.value('(/event/data[@name=''mode'']/text)[1]', 'NVARCHAR(128)') AS [mode],
	event_data.value('(/event/data[@name=''object_id'']/value)[1]', 'INT') AS [object_id],
	event_data.value('(/event/data[@name=''owner_type'']/text)[1]', 'NVARCHAR(128)') AS [owner_type],
	event_data.value('(/event/data[@name=''resource_0'']/value)[1]', 'BIGINT') AS [resource_0],
	event_data.value('(/event/data[@name=''resource_1'']/value)[1]', 'BIGINT') AS [resource_1],
	event_data.value('(/event/data[@name=''resource_2'']/value)[1]', 'BIGINT') AS [resource_2],
	event_data.value('(/event/data[@name=''resource_description'']/value)[1]', 'NVARCHAR(128)') AS [resource_description],
	event_data.value('(/event/data[@name=''resource_type'']/text)[1]', 'NVARCHAR(128)') AS [resource_type],
	event_data.value('(/event/action[@name=''server_principal_name'']/value)[1]', 'NVARCHAR(128)') AS [server_principal_name],
	event_data.value('(/event/action[@name=''sql_text'']/value)[1]', 'NVARCHAR(MAX)') AS [sql_text]
	--event_data.value ('(/event/data[@name=''transaction_id'']/value)[1]', 'BIGINT') AS [transaction_id]
FROM XEvents;