Search This Blog

Thursday, September 10, 2026

How to Check TDE Progress and Elapsed Time in SQL Server

How to Check TDE Progress and Elapsed Time in SQL Server
Transparent Data Encryption (TDE) protects SQL Server data and log files while they are stored on disk. SQL Server handles encryption and decryption automatically, allowing applications and users to continue working normally. Database backups are also encrypted and cannot be restored without the correct certificate or key.

In practical terms, TDE prevents someone who steals or copies your database files, backups, disks, or storage snapshots from simply restoring the database and reading your data. However, TDE is not a complete security solution. It does not protect against SQL injection, compromised administrator accounts, misuse by authorized users, unencrypted exports, network attacks, or data already loaded into memory.

If your database contains sensitive or personal information, TDE is worth considering. It is especially useful when encryption at rest is required by standards such as HIPAA or PCI DSS, or by your organization’s security policies.

However, don’t enable TDE everywhere without a plan. It introduces some performance overhead and requires careful management of encryption keys and certificates. Use it where the security and data-protection requirements justify the extra work.

But that is not the main focus of this post. Instead, it looks at a practical challenge: TDE encryption and decryption scans can take hours, even on a moderately sized SQL Server database.

The scan runs in the background and must read and rewrite every database page. Its speed depends heavily on storage performance, and SQL Server provides no supported MAXDOP, priority, or speed setting to make it finish faster. TDE may be an online operation, but it is not always a “start it and forget it” job. On a large database, the scan can keep storage busy for hours. 

TDE works the same way in SQL Server Standard and Enterprise editions. Enterprise can use greater CPU, memory, and high-availability capabilities, but it does not provide a different form of TDE or a special setting that makes the scan faster.

This can be frustrating, the waiting can feel endless, especially during your first TDE implementation. You watch the percentage creep forward and wonder whether something is wrong. Even a modest 300–400 GB database can take hours when server activity is high or storage is struggling to keep up.

While you wait, you naturally want answers: How much is complete? How long has the scan been running? Is it running normally, suspended, or aborted? 

The following practical query helps answer those questions. By viewing the percentage complete alongside the elapsed time, you can get a useful sense of its average pace and whether it is moving normally. It is not a precise finish-time prediction because workloads and storage activity can change, but it helps you see whether the scan is making steady progress or may need attention.


/*
    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?


Now that we can monitor the scan, it helps to understand what SQL Server is doing behind the scenes.

TDE encryption and decryption scans are not necessarily single-threaded. When you enable TDE, SQL Server performs a few checks and starts a background encryption worker. This allows the original command to finish while the real work continues in the background.

The encryption worker creates disk workers to scan the database files. Each worker processes 8 KB database pages in batches of 32. The pages are loaded into memory, marked as changed, and logged so the operation can also be replayed on an Availability Group secondary.

After the scan finishes, SQL Server performs a checkpoint. During encryption, the changed pages are encrypted and written back to disk. Decryption and encryption-key rotation use the same basic scanning process, although the pages are handled according to the requested operation.

SQL Server generally creates one disk worker per storage volume, not one worker per processor. Ten data files on one volume may still use only one worker, while files spread across ten volumes could use ten workers. This helps control the impact on storage, but it also explains why some TDE scans appear almost single-threaded.

TDE scan parallelism is managed internally by SQL Server and is not controlled by MAXDOP. Even when multiple workers are active, the scan can still be limited by storage performance.

Because every page must be read, processed, and written back, a TDE scan consumes storage, memory, and CPU resources. It may therefore compete with the database’s normal workload.

A 2019 Microsoft’s TDE scan internals article describes SQL Server creating approximately one disk worker per storage volume. The core scan process remains relevant to SQL Server 2025, but worker allocation and batching are internal implementation details that could change in future versions or cumulative updates. 

What does this mean in practice?


TDE scans depend heavily on storage speed. If several databases on the same storage volume are being processed at once, their scans will compete for I/O and may all take longer.

Adding more CPU or changing MAXDOP is unlikely to help. Adding extra data files on the same storage volume will not necessarily make the scan more parallel either.

While a scan is running, try to reduce other storage-heavy work such as backups, CHECKDB, index maintenance, and ETL processes. Avoid moving database files or changing the storage layout because some file operations are restricted during a TDE scan.

For future large databases, placing files on separate physical storage volumes may allow SQL Server to use additional workers. However, this will only help when those volumes provide genuinely independent storage throughput.

That said, there is no need to redesign every database just for an occasional TDE scan. A more practical approach is to provide sufficient storage performance and run only one major TDE scan per shared volume at a time.

What Triggers a Full TDE Scan?


A full TDE scan occurs when SQL Server must change the encryption of every page in the database. Three main operations cause this:

  • Enabling TDE: SQL Server reads every database page, encrypts it, and writes it back to storage.
  • Disabling TDE: SQL Server performs the journey in reverse, reading and rewriting every page without TDE encryption.
  • Regenerating the database encryption key (DEK): SQL Server creates a new DEK and re-encrypts every page with it. This includes changing the DEK’s encryption algorithm 

There is an important difference between regenerating the DEK and changing the certificate that protects it. During the initial TDE setup, the certificate is created and stored in the instance’s master database before the DEK is created in the user database. Changing this certificate or asymmetric key normally re-encrypts only the DEK, not every database page. This is much less work and does not require a full database scan.

Pausing, resuming, or restarting SQL Server during a scan does not start a brand-new scan. SQL Server saves the progress and continues the existing operation when it resumes 

See also