We have a database with multiple filegroups. Out of which, there are 4 filegroups(Non-Primary) which has most of the data. These filegroups have partitioned tables into it. It also contains LOB Data as well.
Now as per customer request, we have purged 50% of data. Because of it, these 4 Filegroups are half empty.
For example these FGs are of 400 GB each and around 200 GB is free.
My requirement is to reclaim the space from these 4 FGs as we are expecting that it will not grow that much in near future. Shrinking is not an option as it is time consuming. Any other faster way to do it.
Can someone please help me. Thanks in advance.
Best Answer
The time-consuming part of shrinking is moving the data out of the latter parts of the file into spaces nearer the beginning so that the rear parts can be truncated, you have to move the data before reclaiming the space because SQL Server doesn't support sparse files (it can't just tell the OS "I don't need that bit, feel free to allocate it elsewhere for now"). This means that there is nothing you can do in-place that will be any faster.
You may be lucky though and find that most of the back end of the files is empty, allowing you to simply truncate them to release some space to the filesystem (
DBCC SHRINKFILE (<file>, TRUNCATEONLY);).You could migrate the data to fresh filegroups then drop the existing filegroups. If you do this on the same volume though, given that you are trying to release 50% or less this will be no quicker, and possibly much slower (it must move 200Gb to the new files, where a shrink will move at most 200Gb around the files). If you do it between volumes though you might see this work much faster if you are using spinning-disk based media, because there is likely to be a lot less head movement latency compared to the shrink. If you are using low-latency storage this benefit will not be significant.
One thing to note is that shrinking is effectively a resumable process - you do not lose all your progress to a rollback if you stop it part way through. So if you have short maintenance windows where you can accept the performance impact of running shrink operations you could start the process and cancel it at the end of your window, and start it again next time. This may take several such windows but would eventually get the job done.
Another issue to mention is that both shrinking and moving data between filegroups work at the page level so will not release unallocated space within pages. This is not likely to be a massive problem for you if most of the free data was off-page LOBs, but if a low of row data was removed to you might want to rebuild your indexes (and heaps, especially heaps, if you have any such tables with no clustered index) before shrinking/migrating the data. You may even find that this will effectively move the data mostly to the front of the files anyway allowing you to truncate them significantly without needing to do a full rearrange and shrink. Depending on your SQL Server version and edition, you can use resumable online index rebuilds to split this process over multiple maintenance windows as described for the shrink operation above.