Shrink and reclaim space for very huge database

Question

Shrink and reclaim space for very huge database

Malkesh Sheth 1

Hello,

We have a very huge OLTP database with 30 TB out of which only 8 TB is used. We want to reclaim the free space as it is cloud infra and want to save on cost. Since this is AG setup such way we could reclaim around 60TB. This database has also very big replication setup and work as a publisher. There are few tables with 1-4 TB sizes. The plan is to get new LUN with less size and move all objects with data over there and then remove the old lun. Can someone guide me on what is the best way to do so? Thanks in advance.

Answer 1

NikoXu-msft 1,901

Hi @Malkesh Sheth ,

It's going to take some time for the shrink operations on such large database files to run. Honestly, you should consider running the DBCC command and just letting it run as long as you can, on days and at times you can afford. Just let it do it's thing.

Best regards,
Niko

----------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Malkesh Sheth 1 Reputation point

2022-12-22T09:23:12.15+00:00

Thanks @NikoXu-msft , We were also thinking on the same line to run DBCC in chunks daily for few off hours. Another question, does it have any impact on active sessions using this db? There will be connections throughout the day so will this approach creates blocking or will it affect the execution plans or slow down the running queries? And last question does this command have any adverse impact if we stop and start on daily basis? Thank you again
Erland Sommarskog 67,821 Reputation points Microsoft MVP

2022-12-22T22:58:32.353+00:00

Before we go on:

1) Are there any LOB columns in the database. (That is (n)varchar/binary(MAX), xml, spatial etc?
2) Do you have heaps, that is tables without clustered indexes?
Malkesh Sheth 1 Reputation point

2022-12-23T04:52:03.983+00:00

Hello @Erland Sommarskog ,
Thanks for the reply. There are few tables with LOB data types and no heap table in database. We have around 28 data files in one file group which made primary. All these files have at least 50% free space say 200-300 GB free space in each file.
So as suggested by @NikoXu-msft and @Bjoern Peters we can consider to use shrink files in chunks or with emptyfile option. I need suggestion on which one would be better? We are considering picking up one file at a time.
Please let me know which one would be good or any other option. Please note that this db has around 300 tables and out of which around 200 tables are either replicated or change tracked so i guess shrink files one by one would be good option.

Answer 2

Hi @Malkesh Sheth

That are heavy operations that cause a long-running (additional) workload for your SQL Server.

I would recommend an additional step in the restructuring of the database... create additional data files for those big tables that can separate those tables from the others and make maintenance on these files much more effortless.

Recommended steps to be performed:

create FULL backup
pause data movement in AG
pause replication jobs
shrink datafiles with EMPTYFILE
reorg/rebuild indexes
update stats

Regarding "blocking" you can find some advice in the documentation.
https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql?view=sql-server-ver16#examples

I hope my answer is helpful to you,

Your
Bjoern Peters

If the reply was helpful, please upvote and/or accept it as an answer, as this helps others in the community with similar questions. Thanks!

Shrink and reclaim space for very huge database

2 answers

Activity