I have a database which grow to 20 GB, after archiving some data, the real size of tables is just 5GB.
Backup script does save a copy of the backup to a different location.
Moving 20GB or 5Gb does make a difference, so I would like to reduce the physical size, but everywhere I read to do not shrink if I don't want to affect performance.
But it this case of (periodically/quarterly/annually) archiving, is it still recommended not to shrink?
Best Answer
What you are seeing on net is mostly a copied advise where people actually want to say that "please don't make shrinking data file or log file a daily routine operation". Had it been so bad Microsoft would have removed it but it is still there and even most experienced DBA's and developer use it but they are aware about the after affects so they know what to do after. Only shrinking can give you back free space
Yes of course you can shrink data and log file because you want to reclaim space and if reclaiming space is very much required. If you see Paul's article he has given a method like
The method I like to recommend is as follows:
Create a new filegroup
Move all affected tables and indexes into the new filegroup using the
CREATE INDEX … WITH (DROP_EXISTING = ON) ONsyntax, to move the tables and remove fragmentation from them at the same timeDrop the old filegroup that you were going to shrink anyway (or shrink it way down if its the primary filegroup)
You can use it if it suits you. Since you are aware shrinking is bad activity and would do it once in a year may be twice this is actually not bad. Shrinking do causes logical fragmentation so dont forget to rebuild indexes after shrinking.
Backup only includes data and few amount of transaction log(if required) to bring back database in consistent state after restore. So again backup would be around 5 G not 20G but yes when you restore it back it would take 20 G space
PS: I am not supporter of shrinking the database or database files but in extreme scenario and knowing the repercussions you can do it.