True story: 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.

3 thoughts on “True story: 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?

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s