How to Check TDE Progress and Elapsed Time in SQL Server

/*
    PURPOSE
    -------
    Shows the progress, status, and duration of current TDE encryption,
    decryption, key-change, and protection-change operations.

    Why this query is different
    ---------------------------
 Most TDE monitoring queries show only the current status and 
 percentage complete from sys.dm_database_encryption_keys. This 
 query also matches the DMV results with the most recent 
 scan-start entry in the SQL Server error log, allowing it to show 
 the start time and elapsed duration without requiring a monitoring 
 table, SQL Agent job, or external tool.
 HOW IT WORKS
 ------------
 1. Reads TDE scan-start messages from the current SQL Server 
 error log and stores them in a temporary table.
 2. Queries sys.dm_database_encryption_keys for databases with an 
 active, suspended, or aborted TDE operation.
 3. Matches each database to its most recent "Beginning database
 encryption scan" error-log entry.
 4. Calculates the duration between that entry and the current 
 server time. Duration is displayed in seconds, minutes, and 
 HH:MM:SS format.
 IMPORTANT LIMITATIONS
 ---------------------
 - Only the current SQL Server error log is searched.
 - If the error log rolled over after the scan started, the start 
 time and duration will be NULL.
 - If a scan was suspended and resumed, duration begins with the 
 most recent scan-start entry. It does not include time from 
 earlier runs.
 - encryption_scan_modify_date is reported in UTC, while the 
 error-log and collection times normally use the SQL Server 
 host's local time.
 REQUIREMENTS
 ------------
 SQL Server 2019 or later because the encryption scan state 
 columns were introduced with SQL Server 2019.
*/
USE master;
GO
SET NOCOUNT ON;
DROP TABLE IF EXISTS #TdeErrorLog;
CREATE TABLE #TdeErrorLog
(
 LogDate datetime,
 ProcessInfo nvarchar(50),
 [Text] nvarchar(max)
);
INSERT INTO #TdeErrorLog
EXEC sys.xp_readerrorlog
 0, -- Current error log
 1, -- SQL Server error log
 N'Beginning database encryption scan',
 NULL,
 NULL,
 NULL,
 N'desc';
SELECT
 d.name AS database_name,
 dek.encryption_state,
 dek.encryption_state_desc,
 dek.percent_complete,
 dek.encryption_scan_state,
 dek.encryption_scan_state_desc,
 scan_start.operation_start_time,
 GETDATE() AS collection_time,
 duration.duration_seconds,
 CAST(duration.duration_seconds / 60.0
 AS decimal(18,2)) AS duration_minutes,
 CASE
 WHEN duration.duration_seconds IS NULL THEN NULL
 ELSE CONCAT
 (
 duration.duration_seconds / 86400, N'd ',
 RIGHT(N'00' + CONVERT(nvarchar(2),
 (duration.duration_seconds % 86400) / 3600), 2), N':',
 RIGHT(N'00' + CONVERT(nvarchar(2),
 (duration.duration_seconds % 3600) / 60), 2), N':',
 RIGHT(N'00' + CONVERT(nvarchar(2),
 duration.duration_seconds % 60), 2)
 )
 END AS formatted_duration,
 -- This DMV value is documented as UTC
 dek.encryption_scan_modify_date
 AS scan_state_modified_utc,
 dek.key_algorithm,
 dek.key_length,
 dek.encryptor_type
FROM sys.dm_database_encryption_keys AS dek
INNER JOIN sys.databases AS d
 ON d.database_id = dek.database_id
OUTER APPLY
(
 SELECT TOP (1)
 el.LogDate AS operation_start_time
 FROM #TdeErrorLog AS el
 WHERE CHARINDEX
 (
 N'''' + d.name + N'''',
 el.[Text]
 ) > 0
 ORDER BY el.LogDate DESC
) AS scan_start
OUTER APPLY
(
 SELECT
 CASE
 WHEN scan_start.operation_start_time IS NOT NULL
 THEN DATEDIFF_BIG
 (
 SECOND,
 scan_start.operation_start_time,
 GETDATE()
 )
 END AS duration_seconds
) AS duration
-- Only active, suspended, or aborted TDE operations
WHERE dek.encryption_state IN
(
 2, -- Encryption in progress
 4, -- Key change in progress
 5, -- Decryption in progress
 6 -- Protection change in progress
)
ORDER BY d.name;
DROP TABLE IF EXISTS #TdeErrorLog;
GO

What happens during a TDE scan?

What does this mean in practice?

What Triggers a Full TDE Scan?