SQL Server Encryption in progress stuck

Working with a couple of databases that needed TDE I noticed when I enabled one of them that it was stuck on “encryption in progress” for quite a while.

Using the following query I confirmed the following: (source: https://msdn.microsoft.com/en-us/library/bb677274.aspx)


SELECT DB_NAME(database_id) AS DatabaseName, encryption_state,
encryption_state_desc =
CASE encryption_state
         WHEN '0' THEN 'No database encryption key present, no encryption'
         WHEN '1' THEN 'Unencrypted'
         WHEN '2' THEN 'Encryption in progress'
         WHEN '3' THEN 'Encrypted'
 WHEN '4' THEN 'Key change in progress'
 WHEN '5' THEN 'Decryption in progress'
 WHEN '6' THEN 'Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed'
         ELSE 'No Status'
      END,
percent_complete, create_date, key_algorithm, key_length,
encryptor_thumbprint, encryptor_type  FROM sys.dm_database_encryption_keys

My ZoraDB database clearly stuck in an encryption in progress state.

stuck.JPG

With the database being small the encryption process should not take long at all, I was confused.

I then decided to try and turn off the encryption.

ALTER DATABASE [ZoraDB] SET ENCRYPTION OFF
 

Msg 33109, Level 16, State 1, Line 35 Cannot disable database encryption while an encryption, decryption, or key change scan is in progress. Msg 5069, Level 16, State 1, Line 35 ALTER DATABASE statement failed.

I sat back and thought about what could possibly cause the encryption process to be stuck? I was thinking what exactly happens during this process then I thought about maybe I had an issue with a page i.e. a corrupted page?

DBCC CHECKDB ('ZoraDB')
 

Object ID 245575913, index ID 0, partition ID 72057594040549376, alloc unit ID 72057594045792256 (type In-row data): Page (1:126) could not be processed.

It was never going to get encrypted right? How could it if it needed to process that page? Anyways I addressed the corruption and to confirm the state was still in progress. I had to re-run the ALTER DATABASE command as shown below.

ALTER DATABASE [ZoraDB] SET ENCRYPTION ON
 

Stuck1.JPG

Hopefully this blog post will help those stuck in a similar position.

Advertisements

3 thoughts on “SQL Server Encryption in progress stuck

  1. Pingback: True story: Encryption in progress stuck - SQL Server Blog - SQL Server - Toad World

  2. I have a similar problem, with the exception that I don’t get any page problems with DBCC CHECKDB. I would like to know what you did to address the corruption?

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.