0

Hello masters of MS SQL

I have a kind of a problem on my hands. We have quite big DB (5TB) and we've just "enabled" some software archiving which means that most of the data will migrate to other DB's. In other words, this big DB is now almost empty with data and will not grow anymore.

The issue here is I have a big file that I need to shrink (space problem). And I wonder how should I approach this problem? Shrinking will probably take quite a while and I am not sure what will happen if something goes wrong and if it's OK to do backups in the middle of shrinking.

Can somebody help me with this issue and shine a little light on this matter?

This is current situation (data is still being deleted, and I assume we will land at 300~500 GB size)enter image description here

Thank you in advance

Improve this question
2
  • Write a script and do it in steps of 1024 MB depending on the speed of your disks. if they are slow, take smaller steps, the shrink operation might freeze your IO, and in worst case restart SQL Server
    – Stijn Wynants
    Commented Jul 4, 2017 at 7:36
  • Do you intend to shrink the transaction log file? You don't have to; you could leave it alone, unless you want to release up to 200 GB of disk.
    – Robert Carnegie
    Commented Jul 4, 2017 at 13:39

1 Answer 1

Reset to default
0

Shrinking will probably take quite a while

It will definitely with that size of data, unless by some fluke most of the free space is at the end of the file.

and I am not sure what will happen if something goes wrong

The operation should be safe even if you experience a complete power-out and the server shuts down uncleanly, pages are copied then relinked as needed, then the old pages unallocated, in an atomic fashion.

and if it's OK to do backups in the middle of shrinking.

You can take backups during a shrink, though they will take longer as there will be extra IO contention. You could stop the shrink operation and restart it after the backups are complete - it should pick up (more or less) where it left off.

Another option is to shrink in small increments when the server is otherwise quiet so that it doesn't conflict with your users either (i.e. if most of your user activity is between 0800UTC & 1900UTC and your full/diff backups and other maintenance like ETL processes usually happen between 2200 & 2330, perhaps running partial shrinks between 0100 & 0700 would be best. If your app has global reach then this is less useful as you won't have quite the same distinct busy/quiet periods.

Don't shrunk right down to the data size though - leave yourself enough space for some months worth of expected data growth. No point fragmenting the data more than you need to as the space saved will just get reclaimed soon enough.

Improve this answer

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