Speedup SQL Database Shrinking Process

Question

I have to shrink and backup a database every week which is about 100+ GB of size.

Normally it takes 2-3 hours to shrink. This is quite frustrating especially when management wants this database to be deployed quickly.

My question is

1-Is there some way to shrink a huge database quickly. 2-Instead of shrinking, if I do a backup with shrink option enabled, does it do the same, like removing unnecessary pages.

Thanks

Why do you shrink the database? When the released space is used later again, you may run into serious fragmentation problems. — Stefan Steinegger
– Stefan Steinegger, Commented Jan 21, 2013 at 7:27

TomTom · Accepted Answer · 2013-01-21 04:14:34Z

4

1: no, in generally you do not shrink "real" databases (of size). specially given that SQL Server backup will not back up pages in the database not used, so a backup of an empty 1000gb database is VERY small, it makes no sense to shrink. How you think people do real backups of IMPORTANT stuff (where you run a delta backup like every 15 minutes)? Generally do not use autogrow, do not use shrink on anything that has a large size.

2: moot as per 1. Do not shrink.

Why do you think you need to shrink the database in the first place? Btw., 100gb is quite small - things get runny once you hit 1000gb and larger.

answered Jan 21, 2013 at 4:14

TomTom

62.4k11 gold badges96 silver badges152 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Muhammad Sharjeel Ahsan Over a year ago

Actually I need to ftp this database, that's the reason I want to shrink as much as possible.

TomTom Over a year ago

@MuhammadSharjeelAhsan Sorry, that is a non reason at all. Take backup, compressed, ship it. No need to shrink the database.

Collectives™ on Stack Overflow

Speedup SQL Database Shrinking Process

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related