Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
add comment

2 Answers

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>
share|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
add comment
up vote 1 down vote accepted

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

share|improve this answer
    
can you mark this as the answer to close the question out? –  Shawn Melton Oct 17 '11 at 0:47
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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