Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

Command:

ALTER DATABASE x SET ENCRYPTION OFF

Result:

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.

Running:

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.

Thanks.

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.

share|improve this question
1  
Did you look at the post sqlservercentral.com/blogs/sqlballs/2012/08/13/… to see if it helps you. –  Kin 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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.