Sql-server – Is it a good idea to shrink prior to a SQL Server upgrade to a new instance

indexshrinksql serverupgrade

We currently have a 100GB database which is in simple recovery mode, and it is running on 2008 R2. We have a new server with SQL 2016 Enterprise installed, ready to go – and we're building a migration plan, for how to do the upgrade.

We've ran a test run using SQL 2016's Data Migration Wizard (https://www.microsoft.com/en-us/download/details.aspx?id=53595), which we used to point to our current, live, production DB instance and to restore the DB to '16. This took 15 minutes, which is very quick considering the other approach (backup currently-running DB, copy over to new server) — mainly because we can't back up / restore over a UNC path.

We've realised that we have a lot of old/unneeded data in our DB, and have kicked off some scheduled tasks using the SQL Agent to perform some deletes, so by the time we're near to our "go live" date then we'll have less data residing in the DB, with the idea being that we should be able to restore the live instance over to '16 quicker with less data.

However, obviously the actual size of the database FILE on disk will not shrink until we perform a DB shrink.

We're thinking of shrinking the DB for these reasons:

  • faster backup times
  • less files to restore when we take a copy of production, scrub it and restore to a test environment, for a like-for-like copy of production to perform tests against
  • there will be a lot of residual space after we've completed these deletes, so makes sense to reclaim it (I'm thinking ~30-35GB, so a third of the DB itself)

I'm aware that by shrinking the DB then the DB size will just grow again in some time, but I think we've previously overlooked some aspect of data growth, to which we've put some processes in place to reduce excessive DB writes. We currently have a 75MB autogrow which happens around 2-3 times a day.

Will this be a good idea? We'll be restoring our DB over, upgrading the SQL compatibility level and then running an index rebuild (using Ola Hallengren's index script) & updating stats. I'm curious if the shrink will massively spike these index operation times as it'll leave the DB pretty fragmented from what I've understood. I'm not sure what the best approach is here – perhaps don't shrink and only rebuild the index on '16 after the data migration?

Thanks

Best Answer

I hope following information will help you to make your final decision.

faster backup times

You will not have a faster backup time. See this answer by Aaron Bertrand.

The backup process does not back up empty pages, only pages with data, so typically, the backup file will be a lot smaller.

One important thing to note: when using compression, the initial size needed on the destination drive will be the uncompressed size; the backup will only be fully compressed at the end of the process.

less files to restore when we take a copy of production

You are not getting rid of any files so I am not sure what you are gaining here. Yes you can save your allocated space by shrinking the database.

there will be a lot of residual space after we've completed these deletes, so makes sense to reclaim it (I'm thinking ~30-35GB, so a third of the DB itself)

It all depends, can your company afford to spare 30~40 GB of space for few weeks/Months which you will need anyway. You mentioned your auto-growth is set to 75MB and it happens 2-3 times a day. Based on this information you will use 30GB space in in next 4.5 months.

If you are rebuilding index please do not update all statistics after that. Read this.

I will highly recommend you to increase the autogrowth size and avoid growth 2-3 times a day. Read this.

Related Question