Combining multiple data files into one

Question

Post reply

Combining multiple data files into one

thenewguy

Grasshopper

Points: 16
More actions
April 16, 2008 at 11:53 am

#71479

I have a db I recently moved to a new server. On the old server I had to setup multiple data files due to storage capacity. On the new machine I have plenty of storage and hp to run it all on one drive. How do I go back now and combine the 4 data files that exist. Thanks in advance.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

george sibbald SSC Guru Points: 104210 More actions · Answer 1

for each .ndf , one at a time-

dbcc shrinkfile(filename,emptyfile)

alter database dbname remove file filename

back the database up first!

---------------------------------------------------------------------

Vivien Xing SSChampion Points: 12297 More actions · Answer 2

A good solution George. Just wondering any impact on the current user activities while migrating all data from the specified file to other files.

george sibbald SSC Guru Points: 104210 More actions · Answer 3

good point vivien. The effect would be as any other shrink, there would be a performannce hit and the shrink would tend to get blocked out by other users. I would do it at a quite time and preferably when I had sole use. In this particular case i would do it as part of the process of moving the db before users got their hands on my lovely new database. 🙂

---------------------------------------------------------------------

george sibbald SSC Guru Points: 104210 More actions · Answer 4

.........and another point, this process will cause fragmentation, so rebuild\reorg your indexes afterwards

---------------------------------------------------------------------

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

Just because you can put it all on one drive, it doesn't mean you should. For optimum performance, you should put data and log files on physically separate disks. You may also consider putting tempdb on its own disk if it is heavily used. There are lots of different considerations, so make sure you read up on storage before you make any decisions.

John

Vivien Xing SSChampion Points: 12297 More actions · Answer 6

Thank you George. This clears my doubt. Bear with me one more question. How does SQL Server internally handle this?

John’s point is right. But it happens sometimes for various reasons.

george sibbald SSC Guru Points: 104210 More actions · Answer 7

Vivien Xing (4/17/2008)
Thank you George. This clears my doubt. Bear with me one more question. How does SQL Server internally handle this?
John’s point is right. But it happens sometimes for various reasons.

internally? I guess it must pick up extents and move them willy nilly to the other files whereever there is space, though I suspect if more than one other file it will try to move data proportionally depending on the space left in the other files.

---------------------------------------------------------------------

Vivien Xing SSChampion Points: 12297 More actions · Answer 8

Sounds reasonable. I like your “rebuild\reorg your indexes” part as well.

thenewguy Grasshopper Points: 16 More actions · Answer 9

Thank you everyone, this has been very helpful. One follow-up, after the shrink, the subsequent files still have data in them. Well, at least there is still size to the file. Is this to be expected and normal?

-Sean

george sibbald SSC Guru Points: 104210 More actions · Answer 10

Sean,

after this particular type of shrink option the files will still be physically the same size on disk, however they will contain no data. You will know they are empty because if they are not the alter database remove file command will fail saying files are not empty.

Read up on DBCC shrinkfile in BOL

---------------------------------------------------------------------