5

I have removed Transparent Data Encryption (TDE) from my server, dropped the key and switched the databases to Simple, shrunk the log and then back to Full.

The LOG backups are now smaller as well as the full backups but the main mdf files are still the same size after removing TDE.

Is this normal? Is there a way to get these back to what they were before TDE?

I know the MDF files will stay the same size until you shrink them but the space available inside I would have expected to increase significantly but it didn't. TDE seemed to double the size of them.

7

Yes this is normal. SQL Server will only automatically reduce the size of data files if you have AUTO SHRINK switched on and there is space available in the files. This setting is not recommended as it will introduce considerable fragmentation and consume considerable IO resources.

You could do a one time shrink with DBCC SHRINKDATABASE. See here for more info. This will introduce fragmentation but it can be fixed by rebuilding your indexes.

IMHO the main considerations would be:

  • Can you live with the current size of the database? If yes then shrinking is probably not worth the effort.
  • Do you have a long enough maintenance window to shrink and fix the fragmentation? You could test this process by restoring a backup to a similar specced machine and running through the process.

Currently shrinking a databse will fail if you have any columnstore indexes. These need to be dropped if you want to shrink the DB as much as possible.

  • When I look at the space available for the database, it doesn't match up to the space that should be available. With TDE removed I would have assumed the file size will be the same of course but the space inside that can be written would be significant amount. – Tsukasa Dec 16 '16 at 8:52
  • 2
    I would try restoring a backup of the DB somewhere else and running a shrink to see what the actual results are. You may get better results if you rebuild any fragmented indexes before the shrink. – James Anderson Dec 16 '16 at 8:57
0

Answer originally left in a comment

TDE doesn't always double the data file sizes. It will increase because of the post-encryption data format. but depending on the original data types and sizes, you might have a much bigger (double) or just somewhat bigger data file.

If your data prior to TDE was already not highly compressible and/or was at some size that was close to the ciphertext size, then the expansion would not be as significant (it's never zero expansion).

Rebuild indexes for your large tables to ensure you're at the desired page density. – SQLmojoe

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.