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)