0

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.

2 Answers 2

1

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.

Shrinking is not an option as it is time consuming.

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.

1

I'd just like to add to David's answer that there are things that can cause shrink to take a very very long time. Here are some things, off the top of my head:

LOB. For each LOB page moved, you'll have a table scan. Yes, you read that right. A table scan for each moved log page. This is because SQL Server doesn't know which row this LOB page belongs (no back-pointer).

Heaps. When you move a data page in a heap, all the rows on that page gets a new RID. That results in all the NC indexes need to be updated with this new rid. I.e., all the NC indexes, and such an update for every row that was on this heap page.

Blocking. Shrink need an X lock (I believe it is) and it'll wait forever.

I'm not saying that moving to a new filegroup is necessarily better, since that has disadvantages as well (as mentioned by David). So in the end it is up for you to determine which method is more attractive to you. Shrink is definitely the simpler one, but it might take a very long time.

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.