I'm having a heck of a time with this issue and can't figure out what is wrong. I'm not sure how long this tiny DB has been in 'encryption_state = 2' from query:
SELECT * FROM sys.dm_database_encryption_keys
but it won't budge past 0%. State 2 means that it is currently trying to encrypt the DB. No other DB has any issues, encrypted or unencrypted.
ALTER DATABASE x SET ENCRYPTION OFF
Msg 33109, Level 16, State 1, Line 2
Cannot disable database encryption while an encryption, decryption, or key change scan is in progress.
Msg 5069, Level 16, State 1, Line 2
ALTER DATABASE statement failed.
ALTER DATABASE x SET ENCRYPTION ON
actually returns a command completed, but percentage still stays at 0%.
There's no DB corruption from
DBCC CHECKDB. There's no locking/blocking going on (not that the logical blocking would affect TDE since it affects the DB on the page level). I'm at a loss short of calling MS on this. Anyone have any ideas? I'm going to try to restore this to a different DB server and test unencrypting the backup there.
SELECT DB_NAME(database_id), percent_complete, * FROM sys.dm_database_encryption_keys go
Edit & update:
Restored to a diff server with the cert, it goes to 1.187865% for 'percent_complete' then immediately reverts back to 0% in about 1 seconds time. Firing up profiler now to catch something in the background perhaps, checking to see if extended events would help.
Oh boy, profiler shows error 824 suspect DB page.
DBCC CheckDB consistently shows no errors. Time to brush up on my CHECKDB internals, I know Paul Randal blogs about error 824. Will update this for others who might have this issue.
Final Edit: Resolution to this problem was that unused sections of the PRIMARY filegroup had SGAM corruption. CHECKDB doesn't check empty blocks so it never found it out. Although there was no chance of anything being written there, figured best COA was to move all the data to a new DB during a brief outage using RedGate and testing it a few times. Just made it in time. Lesson learned: To test corruption on unwritten sections of the database, turn on and off encryption as CHECKDB won't.