SQL Server Performance Forum – Threads Archive

shrink primary database file

Hi, Can anybody tell, 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??
–shiji

---

You shoud use alter database modify file
see Alter database in transact sql help

---

You can not adjust the space usage within the files in the same file group. SQL Server will automatically handles this. If the second file was available from beginning, SQL Server will balance the usage so that no single file will be filled up. But, since the file was added later, it will start using the second file for new data inserts.

---

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

---

]]>