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:
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.