4

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?

Improve this question
1
  • Is the new server also running SQL Server 2000? If not, what version and edition?
    – Aaron Bertrand
    Dec 18, 2012 at 18:46

2 Answers 2

Reset to default
2

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.

Improve this answer
0

The negative effect of shrinking a database is fragmentation. Overall I try to avoid shrinking because of this. If you do shrink the database make sure that you reindex the whole think right afterwards. Storage is cheap but if you are already at 3TB you may not have a lot of choice. Just know that right after you do it the performace with greatly suffer and until the indexes are rebuilt (which BTW will bloat it back up quite a bit). I agree with @Norla the folk over at http://dba.stackexchange.com will have better answers for you

Improve this answer
1
  • Shrinking and then reindexing is in general poor advice since you are essentially undoing one with the other - your reindex will increase slack space but reduce fragmentation while shrinking does the opposite
    – JNK
    Dec 18, 2012 at 18:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.