CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Muhammad Sharjeel Ahsan
Muhammad Sharjeel Ahsan

Reputation: 672

Speedup SQL Database Shrinking Process

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

Upvotes: 1

Views: 1230

Answers (1)

TomTom
TomTom

Reputation: 62157

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.

Upvotes: 4

Related Questions