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.




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.



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

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.

  • 1
    Did you look at the post sqlservercentral.com/blogs/sqlballs/2012/08/13/… to see if it helps you. – Kin Shah Mar 26 '13 at 22:47
  • Kin, thanks! I didn't see that earlier. I'm working with @MrDenny right now, he seems to have found corruption on the GAM page (!). I will update this thread. However, what you posted is very helpful. Thank you again. – Ali Razeghi Mar 26 '13 at 23:38
  • Not GAM corruption :( Currently going to try to shrink the DB to get rid of the page after moving everything to a new filegroup and try again. – Ali Razeghi Mar 27 '13 at 18:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.