Sql-server – Removing TDE from server

sql serversql-server-2012transparent-data-encryption

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.

Best Answer

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.