none
TDE Encryption_State = 2 But Percentage = 0 For A Long Time On A Small DB & Recycling Keys

    Question

  • Hello,

    I wanted to recycle some encryption keys so I went through to completely remove TDE and recreate it on the DBs.  This is probably the 'hard' way to do it, but wasn't sure what the best way to do it to ensure it works completely.  If someone has a better idea on how to do that feel free to let me know on the best way to recycle keys.

    Main issue I want to resolve is that 1 DB shows a encryption_state of 2 and percentage -.  I am not able to execute a SET ENCRYPTION OFF as I get this error:

    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.

    Other DBs are fine, 1 already worked.  Any thoughts?  Thanks.

    Edit: DBCC CHECKDB returns 0 errors.

    Edit 2:  
    -I'd rather not restart the service/de-attach reattach the DB if possible. Trying to avoid any downtime but if we need to, then we need to.

    Edit 3:
    ALTER DATABASE x SET ENCRYPTION ON says 'command completed' but it stays at 0% and still stuck on state 2.


    • Edited by Ali Razeghi Tuesday, March 26, 2013 7:46 PM
    Friday, March 22, 2013 6:51 PM

Answers

  • Hi Ali,

    The following the steps will take a database out of TDE and then clear the log file:

    1. Alter the database to have the ENCRYPTION option set to the value of OFF. This decrypts the database and can take some time if the database is large. If there are no other databases using TDE then an unencrypted TempDB will be created next time the instance starts.

    USE MASTER
    GO
    ALTER DATABASE {Database Name}
    SET ENCRYPTION OFF
    GO

    2. Wait until the decryption process is complete. Use the sys.dm_database_encryption_keys DMV to determine its status. A value of "1" returned in the encryption_status column indicates that the decryption is complete.

    3. Drop the database encryption key for the database.

    USE {Database Name}
    GO
    DROP DATABASE ENCRYPTION KEY
    GO

    4. Truncate the database log file.  This will remove all of the data contained within the log file, including any data that is still encrypted.

    5. Set the database recovery mode to simple and then shrink the log file of the database. This removes any encrypted headers that are in the database. Once this has been done, the recovery mode can be set to Full if required. This step will cause the header of the log file to be rewritten, this is important as the header may still be encrypted even after TDE is removed. Note: If you switch back from Simple to Full logging, you should take a full back up immediately to re-establish the log chain.

    6. Restart the instance in which the database resides. If there are not any other user databases on the instance that have TDE implemented, this action will force the recreation of the TempDB database in an unencrypted format.

    For more information, please refer to http://www.sqlservercentral.com/articles/Security/76141/

    Thanks.

    If you have any feedback on our support, please click here.


    Maggie Luo
    TechNet Community Support

    Thursday, March 28, 2013 8:10 AM

All replies

  • Encryption_state =2 means "Encryption in progress". You would need to wait for it to change to 3 after which you will be able to run the command.
    Saturday, March 23, 2013 10:15 AM
  • It's a tiny DB and it's been in this state for several days and 0% completed though.
    Saturday, March 23, 2013 10:49 PM
  • Bump!  Any replies are appreciated.
    Monday, March 25, 2013 8:33 PM
  • Hi Ali,

    The following the steps will take a database out of TDE and then clear the log file:

    1. Alter the database to have the ENCRYPTION option set to the value of OFF. This decrypts the database and can take some time if the database is large. If there are no other databases using TDE then an unencrypted TempDB will be created next time the instance starts.

    USE MASTER
    GO
    ALTER DATABASE {Database Name}
    SET ENCRYPTION OFF
    GO

    2. Wait until the decryption process is complete. Use the sys.dm_database_encryption_keys DMV to determine its status. A value of "1" returned in the encryption_status column indicates that the decryption is complete.

    3. Drop the database encryption key for the database.

    USE {Database Name}
    GO
    DROP DATABASE ENCRYPTION KEY
    GO

    4. Truncate the database log file.  This will remove all of the data contained within the log file, including any data that is still encrypted.

    5. Set the database recovery mode to simple and then shrink the log file of the database. This removes any encrypted headers that are in the database. Once this has been done, the recovery mode can be set to Full if required. This step will cause the header of the log file to be rewritten, this is important as the header may still be encrypted even after TDE is removed. Note: If you switch back from Simple to Full logging, you should take a full back up immediately to re-establish the log chain.

    6. Restart the instance in which the database resides. If there are not any other user databases on the instance that have TDE implemented, this action will force the recreation of the TempDB database in an unencrypted format.

    For more information, please refer to http://www.sqlservercentral.com/articles/Security/76141/

    Thanks.

    If you have any feedback on our support, please click here.


    Maggie Luo
    TechNet Community Support

    Thursday, March 28, 2013 8:10 AM

  • Hi Ali,

    I’m writing to follow up with you on this post. Was the problem resolved after performing our action plan steps?

    If you are satisfied with our solution, I’d like to mark this issue as "Answered". Please also feel free to unmark the issue, with any new findings or concerns you may have.  

    Thanks.


    Maggie Luo
    TechNet Community Support

    Monday, April 01, 2013 8:55 AM
  • Hi Maggie,

    I have exactly the same issue: "alter database <dbname> set encryption off" triggers the following error message

    Msg 33109, Level 16, State 1, Line 1
    Cannot disable database encryption while an encryption, decryption, or key change scan is in progress.
    Msg 5069, Level 16, State 1, Line 1
    ALTER DATABASE statement failed.

    the database has "is_encrypted" to 1, "encryption_state" to "2" and "percent_complete" to 0. This for a few days now, with a test database almost empty.

    Would you please help?

    Thanks a lot

    Thursday, October 31, 2013 2:52 PM
  • Has anyone found a workaround for this problem?  I am having the same experience.

    I attempted to encrypt (TDE) 2 smaller databases in a test environment, both show

    "is_encrypted" as 1, "encryption_state" as 2, "percent_complete" 0.  Also, tempdb

    shows "is_encrypted" as 0, "encryption_state" as 3, "percent_complete" 0 which seems inconsistent.

    When I attempt to "set encryption off", either with the databases in single_user or multi_user,

    I get the above "Msg 33109, Level 16, State 1, Line 1" error.  DBCC checkdb shows no

    corruption, a query to msdb.dbo.suspect_pages returns no rows. 

    Both databases were upgraded from SQL 2008 by backup restore.

    joe

    Tuesday, December 17, 2013 2:04 PM