Transparent Data Encryption (TDE) stuck in State 2 – Encryption in Progress


Another day, another interesting troubleshooting scenario with SQL Server 2012.  In this case, a customer was attempting to enable Transparent Data Encryption (TDE) on a couple SQL Server 2012 databases.  The process went smoothly until they encountered an issue with a single database.  The problem database in this case was about 50 GB in size, similar to the others that succeeded.  When they queried sys.dm_database_encryption_keys, the database showed 0 percent complete, along with encryption_state = 2 (Encryption in Progress).  After waiting several hours with no apparent progress, we decided to dive in. 

In the past, I’ve encountered this issue as a result of corruption in the database preventing TDE from being enabled and leaving the scan “stuck”, requiring the use of trace flag 5004 to reset the scan.  Bradley Ball has a great article with the details of this specific issue here.  In our case, running DBCC CHECKDB and querying msdb.dbo.suspect_pages showed no corruption, so we headed down another troubleshooting path. 

The SQL Server error logs revealed that the encryption scan had been aborted with the error message "Database Encryption scan for database <name> was aborted. Reissue ALTER DB to resume the scan".

If the scan is still in progress, running a SET ENCRYPTION ON/OFF statement will fail.  However, in our case, the scan had been paused, so it was as simple as reissuing the command again.

ALTER DATABASE <name> SET ENCRYPTION ON

After running this command and monitoring sys.dm_database_encryption_keys, we saw the encryption scan pick up where it had paused and completed to 100%.  Once complete, the encryption_state was correctly set to 3 (Encrypted) and we were all set.

Hope it helps,
Sam Lester (MSFT)

Comments (5)

  1. Parikshit says:

    Thanks! I faced similar issue, DB was stuck with status 2 and got it fixed after rerunning encryption which fixed and changed status to 3  :). Thanks a lot for the post.

  2. Brandon says:

    Having a similar issue testing an EKM solution, but re-applying encryption, nor the steps listed in the article worked either – there is no corruption in the database. SQL immediately just logs that the scan was aborted.

  3. Nick says:

    Thank you so much. quickly identified my issue. I had run the alter to update snapshot_committed state in the middle of the encryption, that cause encryption to pause. Running this picked back up and it finished with success.

  4. Hi Nick, thanks for the feedback. I’m glad it helped out.
    Sam

  5. George Tr says:

    Monitor percentage_complete in sys.dm_database_encryption_keys when you your status = 2

Skip to main content