I need to move databases having TDE enabled to another instance and re-enable the TDE with a different certificate. Generally, this would not be a problem but I seem to have problems if the databases were in AlwaysOn Availability Group environment.

Steps I would normally follow would be:

  1. Remove database from Availability Group.
  2. Set encryption off for the database.
  3. Drop private key from the database.
  4. Take full and transaction log backup.
  5. Set the database to simple recovery mode.
  6. Shrink the transaction log file.

Here is the problem I faced with.

After enabling TDE for the database in the new SQL instance with a different certificate the backup fails with the error

Cannot find server certificate with thumbprint '0x***********************'.

I believe the reason is that there is a VLF (Virtual Log File) that is marked as active (status 2) and needs to be backed up. Step 4-5 are not resolving this. This VLF seems to be related to the Availability Group and is kept there for the secondary replica is my guess.


How can I clear the VLF status and shrink the log file?

  • Is there only ONE VLF with status 2? (you cannot get rid of 1 active VLF anyway, at any moment at least one VLF should bi active)
    – sepupic
    Jun 12 '17 at 9:40
  • There are two VLFs and one of them having status 2 and smallest I have been able to shrink the log file after full and log backup, changing to simple recovery mode is to 17 MB.
    – pesi
    Jun 12 '17 at 9:45
  • This is normal and it's a minimal configuration for log file. There cannot be less than 2 VLFs(otherwise the log will not have a circular structure) and one of them is always active. So there is no need to shrink the log file it's just cannot be less then it is now
    – sepupic
    Jun 12 '17 at 9:48
  • If you still suspect that your problem has to do with the log, you can just clearly shutdown your database (take it offline without specifying "with nowait") and attach this database using data file(s) only. The server will recreate the log for you, but I think it's not a log that causes the error
    – sepupic
    Jun 12 '17 at 9:51
  • Thanks. I had already tried to take them offline and shutdown the instance. I think the detach and attach with no log would work. That way the file gets recreated.
    – pesi
    Jun 12 '17 at 16:06

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.