3

All,

I'm familiar with the good reasons not to shrink a database and I am in the process of shrinking a 2TB one. We have a large database that gets backed up each day (by policy) and because of the size, the backup runs most of a day. We have been running for some time with over 60% free and that shouldn't change significantly. I'm reducing the table size to mitigate what the backup is doing to our performance.

The table is large and shrinking is quite slow so I am running a DBCC SHRINKFILE in a looping script where I shrink 250MB at a time. I'm doing this because when the backup starts SHRINKFILE errors out saying it can't change the file system. I'm assuming this is because the backup has the file locked so the file isn't changing in the middle of the backup. I am doing the small shrinks as I been in the middle of one when the backup starts. So I loop it so I can see the shrinkfile errors and kill my script - the backup can start at different times.

My question is that although the DBCC SHRINKFILE can't change the file after the backup starts and I get the error message, the first iteration of the script when the backup is done goes quite quickly and then settles into a normal cadence. As folks have said if you kill a SHRINKFILE, you "don't lose your work". Is the results of the shrink sitting out in tempdb somewhere ready to go? To get this shrink done a little quicker, does anyone think running a larger shrinkfile during the backup that I know won't complete is a good idea? It would give me a jump on the next day.

Thanks,

Pat

Improve this question
6
  • Hey Pat, not really going to offer a good answer for your question, but I do want to ask why you can't do incremental backups instead of a Full (what it sounds like you are doing) everyday? The Shrink regime you are working on now might not be sustainable as the DB grows further. I know this is as you said Policy based, but might be worth considering having a different backup approach.
    – Henrico Bekker
    May 19, 2017 at 12:37
  • 1
    Pat, you could probably sped up your backups quite a bit if you backup to multiple files simultaneously. Performance will depend on your storage I/O but with a 2TB database, I'm assuming you have decent hardware. I would start with backing up to 40 files and see how that helps you. I've seen improvements on the order of 60% even with large databases.
    – Jonathan Fite
    May 19, 2017 at 12:52
  • First, thanks all. And let me edit my question (database vs. table). I'm shrinking to reduce the file size of the database, not the table size like I said.
    – pyearick
    May 20, 2017 at 12:13
  • Henrico, I asked again about the need for a full everyday yesterday - that is just what we do. Jonathan I thought the same and I've got a dev database with these big BI tables partitioned onto different files in filegroups. I just haven't done that to the production database yet.
    – pyearick
    May 20, 2017 at 12:21
  • hey @pyearick, did you backup you database while it was shrinking? It worked?
    – Racer SQL
    May 24, 2017 at 20:40

1 Answer 1

Reset to default
3

Are you shrinking before backup in the hope that it will reduce your backup time? If so that will not make any difference. But if you are doing it so it allocates less space on your target (when you restore) then it will help.

I did not understand if you are doing this every time before backup. If yes you want to resolve this permanently and not do this.

Now to answering your question.

Is the results of the shrink sitting out in tempdb somewhere ready to go?

It does not have to keep the result. It will check everything again once you restart.

To get this shrink done a little quicker, does anyone think running a larger shrinkfile during the backup that I know won't complete is a good idea?

No, because it does 32 pages at one time and commit. See below.

Reference: https://blogs.msdn.microsoft.com/psssql/2008/03/28/how-it-works-sql-server-2005-dbcc-shrink-may-take-longer-than-sql-server-2000/

Shrink works in transaction batches of ~32 pages. Once 32 pages are moved below the truncation point the transaction is committed and a new transaction started. This avoids an older issue with shrink* that kept a transaction open for a long time and caused the log files to grow because you can’t truncate past an active transaction. It also makes shrink* restart capable. If you cancel/kill a shrink* operation only the current active transaction is rolled back. This means you could schedule a shrink* operation during a maintenance window and limit its runtime to that maintenance window. You can restart it during the next maintenance window and it will pick up from where it left off. The only disadvantage to this is that allocations could occur after the target once the shrink is terminated. This may require shrink to do additional work for newly allocated space but this should be reasonably limited.

On a side note backing up 2TB should not take a full day. There are switches in native backup, can speed up/optimize your backup duration.

Improve this answer
1
  • SqlWorldWide - thank you too. This shrinking process (the loop) is going to take a couple of weeks at the rate it's going. To answer your question about the goal, I think that over time, the backup should go quicker. On the 2TB taking most of the day, we do have a shared network for the backup and there might be contention as the process used to complete (same file size) in about 9 hours and we're up into the mid-teens now.
    – pyearick
    May 20, 2017 at 12:28

Your Answer

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

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