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!