October 18, 2004 at 6:06 pm
Hi, Can anybody tell me, how i can flush data from the primary database file(.mdf) to another file in the same filegroup.
I have a db with 85GB space with only one data file and the disk is going out of space. I added a new file to the db on another disk to the primary filegroup and want to flush some data to the new file. But dbcc shrinkfile does't work, it shrinks the primary file to only hte minimum data size. dbcc shrinkfile doesn't even empty a primary datafile.
I have to do this process online, otherwise I could have employed backupa nd restore to different locations.
can anybody help??
Additional notes:- alter db doesn't work, because the new size must be larger than the current file size.
dbcc shrink file will do the trick with secondary files, but fails with primary file..but i have never seen any documnetation of this limitation.
so one round abt method that i am going to implement is as follows
1. limit the old file size to the current size.
2. 'select into' one or more big static tables with a different name. (this will cause the new tables to be created in the secondary file since the primary is limited in size.)
3. drop the old tables and rename the new tables.
4. shrink the primary file, dropping tables would have already reduced the current size of the primary file.
But concern here is this is a risky method, since select into and rename may cause result in data inconsistancy. Aslo is there any dependecy issues in rename process??
anybody can suggest a more straight forward method.
--Shiji
October 19, 2004 at 11:55 am
If you move the clustered index to another filegroup, it moves the table.
October 20, 2004 at 4:27 pm
thanks Steve, that solves my problem with less trouble... --Shiji
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply