I apologize for the long question. I am trying to learn a lot on the fly as I have become the defacto SQL Admin. I have been tasked with moving a production database to a new server. My goal is to take a full backup and restore the database on new server. My question is focused around the concept of shrinking a database.
I have a production database almost 3 terabytes in size. There are 5 tables no longer needed. Combined the 5 tables account for almost 700GB. My thought process was to remove the 5 tables to save myself a considerable amount of space (700gb) before taking a full backup. I am learning real quick it is not as easy as it sounds. I removed the 5 tables using the drop command but then learned deleting tables will not actually free up any space. In order to free up space I must shrink the database. This is where I run into conflicting opinions. I am reading you do not want to shrink a database unless you have to.
Can someone please point me in the right direction. Now that I have deleted the tables should I shrink the database before I perform the full backup? Will shrinking the database have a negative effect?
Best Answer
Let's go through a few points.
1- Why Even Shrink? What Are The Benefits And Cons?
First is the 700GB extra space causing any issues? If the DB is already at 2.3TBs, it will probably continue to grow, if so, then consider just leaving the space. In fact, you WANT free space in your DB if it's still growing! You don't want it to expand often, as that causes physical file fragmentation and causes blocking/performance issues when it's growing unless you have Instant File Init. enabled, then the blocking/locking issues are largely negated.
When you take backups those free 700GB are not actually copied in the backup, just pointers to empty pages which will then be populated on restores, thus doing this will not reduce backup time, restore time, backup space, but will take up restore space. The only time it'll affect restore time is if you do not have instant file initialization enabled and your SQL Server Service user is not an administrator.
Reindexing again will increase the free space for it to hold temp info and such, but again, if you end up growing and using that free space then there's no problem.
Perhaps if you are restoring to a smaller dev server that doesn't have as much space, then this would be warrented.
2-To Shrink Or Not To Shrink
Now assuming you absolutely need to reduce your DB size, then read Paul Randal's post on moving to a different filegroup instead of shrinking. This ensures that your shrink does not create additional free space. If you have a clustered key it also reorders the base clustered index as well.