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.
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
Hopefully this blog post will help those stuck in a similar position.