3

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

Improve this question
2
  • Is there a storage concern? Data will eventually grow again. It also depends on the type of storage you have if fragmentation will be extreme or very minimal.
    – jstexasdba
    Commented Jun 20, 2017 at 21:28
  • Currently, no, space isn't an issue, I was under the impression that it'd make the backup/restore faster, but as pointed out below, empty pages are ignored. I'm thinking about not doing the shrink now, and just rebuild indexes in SQL '16.
    – Steven Sproat
    Commented Jun 21, 2017 at 14:08

1 Answer 1

Reset to default
3

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.

Improve this answer
5
  • "less files to restore when we take a copy of production" - When restoring, it will restore to the same size that it was at the time of backup. If you're restoring to a new location, the OS will have to allocate all of the 'unused' data space as well. If you have Instant File Initialization turned on, it should be much quicker to allocate the 'unused' portion of the DB when restoring.
    – Zac Faragher
    Commented Jun 21, 2017 at 5:43
  • thanks, I've enabled IFI on the new '16 servers. This answer was very helpful, doesn't seem like we stand to gain much from a shrink, then. I think we'll do the data cleanup and then backup/restore that (with lots of free space available to the DB)
    – Steven Sproat
    Commented Jun 21, 2017 at 14:03
  • You are welcome. I am glad that you find it helpful. Please do not forget to accept the answer so others can be benefited.
    – SqlWorldWide
    Commented Jun 21, 2017 at 14:05
  • @SqlWorldWide : backups could be a bit quicker, at least with snapshot backups (and/or 3rd party backup manager/server). But with 100GB database and 30GB free space I don't think it's worth the hassle on any modern hardware, especially with mentioned predicted growth.
    – Marcin S.
    Commented Jun 22, 2017 at 8:00
  • About "update statistics" - I think your linked article says that rebuilding one or all indexes rebuilds statistics associated with the indexes, i.e. on those sets of key columns, but does not rebuild "non-index statistics" which also may be wanting an update. So that seems like not a waste of time, at least.
    – Robert Carnegie
    Commented Jun 22, 2017 at 23:24

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.