Edit

Share via

sys.dm_change_feed_log_scan_sessions (Transact-SQL)

  • Article

Applies to: SQL Server 2022 (16.x) Azure SQL Database Azure Synapse Analytics Microsoft Fabric

Returns activity from the SQL change feed.

This dynamic management view is used for:

Column name Data type Description
session_id int ID of the session.

0 = the data returned in this row is an aggregate of all sessions since the instance of SQL Server was last started.
start_time datetime Time the session began.

When session_id = 0, the time aggregated data collection began.
end_time datetime Time the session ended.

NULL = session is active.
When session_id = 0, the time the last session ended.
duration int The duration (in seconds) of the session.

0 = the session does not contain change data capture transactions.

When session_id = 0, the sum of the duration (in seconds) of all sessions with change feed transactions.
batch_processing_phase nvarchar(200) The stage of scan reached in a particular log scan session. The following are the currently implemented phases:
1: Reading configuration
2: First scan, building hash table
3: Second scan
4: Second scan
5: Second scan
6: Schema versioning
7: Last scan, publish and commit.
8: Done
error_count int Number of errors encountered.

When session_id = 0, the total number of errors in all sessions.
batch_start_lsn nvarchar(23) Starting LSN for the session.

When session_id = 0, the starting LSN for the last session.
currently_processed_lsn nvarchar(23) Current LSN being scanned.

When session_id = 0, the current LSN is 0.
batch_end_lsn nvarchar(23) Ending LSN for the session.

NULL = session is active.

When session_id = 0, the ending LSN for the last session.
tran_count bigint Number of change data capture transactions processed. This counter is populated in batch_processing_phase 2.

When session_id = 0, the number of processed transactions in all sessions.
currently_processed_commit_lsn nvarchar(23) LSN of the last commit log record processed.

When session_id = 0, the last commit log record LSN for any session.
currently_processed_commit_time datetime Time the last commit log record was processed.

When session_id = 0, the time the last commit log record for any session.
log_record_count bigint Number of log records scanned.

When session_id = 0, number of records scanned for all sessions.
schema_change_count int Number of data definition language (DDL) operations detected. This counter is populated in batch_processing_phase 6.

When session_id = 0, the number of DDL operations processed in all sessions.
command_count bigint Number of commands processed.

When session_id = 0, the number of commands processed in all sessions.
latency int The difference, in seconds, between end_time and currently_processed_commit_time, in the session. This counter is populated at the end of batch_processing_phase 7.

When session_id = 0, the last nonzero latency value recorded by a session.
empty_scan_count int Number of consecutive sessions that contained no captured transactions.
failed_sessions_count int Number of sessions that failed.

Permissions

Requires VIEW DATABASE STATE or VIEW DATABASE PERFORMANCE STATE permission to query the sys.dm_change_feed_log_scan_sessions dynamic management view. For more information about permissions on dynamic management views, see Dynamic Management Views and Functions.

Related content

For Microsoft Fabric mirrored databases:

For Azure Synapse Link: