Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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? Any information is greatly appreciated.

share|improve this question

migrated from stackoverflow.com Dec 18 '12 at 18:36

This question came from our site for professional and enthusiast programmers.

    
Is the new server also running SQL Server 2000? If not, what version and edition? –  Aaron Bertrand Dec 18 '12 at 18:46
add comment

2 Answers 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.

share|improve this answer
add comment

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

share|improve this answer
    
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 '12 at 18:41
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.