I am in the process of purging data from a multi-terabyte database which only has two data files on the PRIMARY file group.  I expect to free up at least 50% of space within the data files.  I need to reclaim that space and am looking for the most efficient way to do it.

The tables are a combination of heaps and clustered tables.  I am aware of the fragmentation issue by using the shrink command and will handle that separately if needed.  Unfortunately, I prefer not to create a secondary file group and move each table individually.  However, the shrink command either reorganizing or empty file options are running 16+ hours.

What is the recommended solution for this scenario?

• Edited by J I M B O 16 hours 28 minutes ago

what is database recovery model ?

what is database size ?

check any job running on database.

The recovery mode is in SIMPLE.  I am not worried about the log size.  The log size appears to be adequate at the moment.  Unless I find out otherwise, I do not intend on resizing it.

The test database that I am working with is 2 TB on a server with no activity beyond my own.

What Paul suggests is

The method I like to recommend is as follows:

• Create a new filegroup
• Move all affected tables and indexes into the new filegroup using the CREATE INDEX WITH (DROP_EXISTING = ON) ON syntax, to move the tables and remove fragmentation from them at the same time
• Drop the old filegroup that you were going to shrink anyway (or shrink it way down if its the primary filegroup)

See shrinking is for sure not advisable but you can shrink if you want to reclaim space after data purge

What Paul suggests is

The method I like to recommend is as follows:

• Create a new filegroup
• Move all affected tables and indexes into the new filegroup using the CREATE INDEX WITH (DROP_EXISTING = ON) ON syntax, to move the tables and remove fragmentation from them at the same time
• Drop the old filegroup that you were going to shrink anyway (or shrink it way down if its the primary filegroup)

See shrinking is for sure not advisable but you can shrink if you want to reclaim space after data purge

Yeah, as I stated in my original post, I am trying to avoid that method.  Not because it doesn't work, but it requires a lot more effort and introduces plenty points of failure.  I am hoping for a simple set of commands, but am finding nothing online so far to support it.

You said you don't prefer to add another file group but I don't know why :) but assuming it is a TEST server then nothing to worry at all.

Anyway try adding space on Disk at least because its not just fragmentation that will be caused by data file shrinking.

you may have contention on both I/O and Lock, log file growth and space issue on that drive. So you could consider at least these while playing with Production.

Again try to shrink in smaller chunks rather than doing it at

Jinu,

I am attempting to reclaim the free space from the data file.  The freed up disk space will be used for other servers.  The SQL Server and processes have been updated to only contain a fraction of the data it has now, so I am not worried about removing large amounts of disk space.

The reason that I do not want to add a secondary file group was stated in one of my previous comments.  Moving each table one by one is error prone.  Even if I scripted it, there is a chance that I may miss something.  It would be great if SQL Server provided a function to recreate indexes or move tables to different file groups, but they don't.  Seeing that this process needs to be performed in production, I rather not take a chance with code I found on the internet.

Unfortunately, the shrink command is ridiculously slow.  To free up 1 GB of space took 40 minutes on a 500 GB file with 40% free space.  It looks to be faster to just move the tables to a secondary file group.

Normally people go on for shrinking when they ran out of disk space and have no other way to get back the space rather than the one residing inside data file. Anyways ..Its not unfortunate that Shrink is slow but it is the nature of operation. Data page movements are heavy. Do u know if any LOB's are involved inside the shrink area of your file.

There were scenarios where I have performed shrink in 10 and 20GB chunks. Even though you have nothing on I/O activity, still it takes it time to work through all stages. Also each data movement is recorded inside transaction log file. Its fast when compared to SQL Server 2000 where it runs as a single batch. From 2005 its been running in a set of 32 page batch. So I believe nothing much can be done on your case.

Unfortunately, the shrink command is ridiculously slow.  To free up 1 GB of space took 40 minutes on a 500 GB file with 40% free space.  It looks to be faster to just move the tables to a secondary file group.

If you dont want to follow what Paul stated then you have no option but to run shrink in small chunks. Yes shrink operation is single threaded and takes lot of time there is no way to speed it up it will take its time