SQL 2008 R2 Datafile Too Large To Empty or Remove

Question

Our primary database has three datafiles: 1TB, 400G and 500G. The 1TB file is just too dang big and isn't really even needed any more, so I want to empty it and get rid of it so we can use that drive space to partition the data. However, any attempts to empty the file will get to 16% or so and then drop to an absolute crawl: the last attempt, after 24 hours, showed the estimated time to complete (from sys.dm_db_exec_requests) was 4.5 days, and I will guarantee that would continue to increase out of proportion to the percent of work completed.

The file is currently 96% free so a lot of the data was moved out, but now we're stuck. We can't run a shrink 'cause it's apparently too big, and it won't let us remove it 'cause it was the initial datafile for the database and holds the sys data.

Is there a faster way (faster than, say, a week or two of downtime) to shrink this thing and/or get rid of it entirely and migrate everything to another datafile in the PRIMARY filegroup?

Edit: Found a solution! I can't shrink the whole thing at once; I just have to do it in dribs and drabs. Shrinking by 50G at a time is working like a champ!

Answer 1

Shot in the dark without any information on how the database is structured i.e. what tables are on what filegroups, is there an existing partition scheme?.

How about rebuild the tables on the big filegroup to one of the other existing filegroups, or a new smaller one?

CREATE UNIQUE CLUSTERED INDEX PK_MyTable ON dbo.MyTable
(
<column list> 
)
WITH (DROP_EXISTING=ON, ONLINE=ON)
ON <new filegroup>

Answer 2

Shrinking the file in stages, rather than going from 1TB to 50M, did the trick.