monitor checkpoint by Extended Event
CREATE EVENT SESSION Monitor_Checkpoint ON SERVER
ADD EVENT sqlserver.checkpoint_begin, ADD EVENT sqlserver.checkpoint_end
(
ACTION (sqlserver.database_id, sqlserver.sql_text)
)
ADD TARGET package0.ring_buffer
(SET max_memory = 4096)
WITH (max_dispatch_latency = 1 seconds)
GO
ALTER EVENT SESSION Monitor_Checkpoint ON SERVER
STATE = START;
SELECT
FinalData.x.value ('@name', 'nvarchar(50)') AS EventName,
FinalData.x.value ('@timestamp', 'nvarchar(50)') AS CheckpointStartTime,
db_name(FinalData.x.value ('data(data/value)[1]', 'nvarchar(50)')) AS DatabaseID,
FinalData.x.value ('(action/.)[2]', 'nvarchar(200)') AS SQLText
FROM
(SELECT CAST(st.target_data AS XML) XMLData
FROM sys.dm_xe_session_targets st
JOIN sys.dm_xe_sessions s ON
s.address = st.event_session_address
WHERE st.target_name = 'ring_buffer'
AND s.name = 'Monitor_Checkpoint'
) data
CROSS APPLY XMLData.nodes ('//RingBufferTarget/event') AS FinalData (x);
ADD EVENT sqlserver.checkpoint_begin, ADD EVENT sqlserver.checkpoint_end
(
ACTION (sqlserver.database_id, sqlserver.sql_text)
)
ADD TARGET package0.ring_buffer
(SET max_memory = 4096)
WITH (max_dispatch_latency = 1 seconds)
GO
ALTER EVENT SESSION Monitor_Checkpoint ON SERVER
STATE = START;
SELECT
FinalData.x.value ('@name', 'nvarchar(50)') AS EventName,
FinalData.x.value ('@timestamp', 'nvarchar(50)') AS CheckpointStartTime,
db_name(FinalData.x.value ('data(data/value)[1]', 'nvarchar(50)')) AS DatabaseID,
FinalData.x.value ('(action/.)[2]', 'nvarchar(200)') AS SQLText
FROM
(SELECT CAST(st.target_data AS XML) XMLData
FROM sys.dm_xe_session_targets st
JOIN sys.dm_xe_sessions s ON
s.address = st.event_session_address
WHERE st.target_name = 'ring_buffer'
AND s.name = 'Monitor_Checkpoint'
) data
CROSS APPLY XMLData.nodes ('//RingBufferTarget/event') AS FinalData (x);