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!


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?

<column list> 
ON <new filegroup>
| improve this answer | |
  • This would work in theory, if there were enough available space to add datafiles and new filegroups. Unfortunately in this case, no dice, because this huge file is eating up all the drive space. Shrinking it piecemeal and occasionally rebuilding indexes fixed the issue, though. – Valkyrie Oct 16 '11 at 19:56

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

| improve this answer | |
  • can you mark this as the answer to close the question out? – user507 Oct 17 '11 at 0:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.