November 22, 2007 at 8:40 am
We have a database which is split into two files File.mdf and File_1.ndf.
This was done as a stopgap measure when we ran out of space on the drive that the main file was on.
The Database has now been moved to another server with plenty of free space. What I want to do is take the data out of File_1.ndf, put it in File.mdf and delete File_1.ndf.
On a backup system I tried the following:
- Resumed the autogrow on File.mdf
- Stopped the autogrow on File_1.ndf
- Used the shrink database dialog to remove all data from File_1.ndf and reallocate it to other files
- (After this, File.mdf grew, and File_1.ndf reported 99.94% free space)
- Then I removed the File_1.ndf file
My question, did I do this correctly, or is there a better way to perform this kind of operation?
Cheers!
November 22, 2007 at 9:19 am
Check out BOL for
DBCC SHRINKFILE
(
{ 'file_name' | file_id }
{ [ , EMPTYFILE ]
| [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]
}
)
It's the emptyfile option you're looking for !
Once you've emptied it, you can remove the file from the filegroup.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 23, 2007 at 8:54 am
Cheers, think I understand that now.
I was using SQL Server Manangement Studio to do the "EMPTYFILE" without realising it.
Pressing the script generation button on the option I tried before generates precisely the script!
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply