Split Large .MDF File into Separate Files/Locations 1

[dlongnecker]

Hello!

I've recently been brought onto a project where their existing database is a 18GB monster located on a single hard drive. As expected, the performance of the application is horrible.

I would like, for management and performance's sake, to split the database up into multiple 4.0GB files on the system. I did, what I thought, would resolve the problem by doing the following:

1. Created multiple files within the same file group; assigned each of the files a maximium file size fo 4.0GB...
So, I have data_1, 4.0GB, data_2, 4.0GB, etc.

2. Went into Shrink Database, and "Empty the file (data will migrate to other files in the file group)" and sat through that very long process.

After this, I noticed it did distribute data to those other files.... a whopping 4.0 _MB_.

Where am I going wrong and/or is there a better solution to resolve these performance hassles and save this Goliath of a 18GB DB?

Thanks for your time and consideration!

-David


---

David R. Longnecker
CCNA, MCSA, Network+, A+
Management Information Services
Wichita Public Schools, USD 259

 

[Corran007]

so why are you spliting it into seperate files? are you going to put in a new machine with multiple hard drives?

and 18g is really not all that large.

but as to the question at hand. I thought creating it initionally was the only way. but it seems you can switch them

place an existing table on a different filegroup
Expand a server group, and then expand a server.

Expand Databases, expand the database in which the table belongs, and then click Tables.

In the details pane, right-click the table, and then click Design Table.

Right-click any column, and then click Properties.

On the Tables tab, in the Table Filegroup list, select the filegroup on which to place the table.

Optionally, in the Text Filegroup list, select a filegroup on which to place any text, image, and ntext columns.

Seems to be one table at a time. hopefully you dont have too many.

 

[dlongnecker]

Hmm, cool. I'll give that a shot.. I saw it, but, in my own ignorance, didn't try that. The Shrink seemed to be verbatium what I wanted... typical of Microsoft! Hah!

As far as the 18GB DB... honestly, I need more information about the program to determine what it should be. From what the technicians in our department have told me, it has about 30 tables with one table having about 12000 rows... which, frankly, is nothing. I'm planning on calling the application's tech support to see if that's normal for their DB. Also, yes, due to the insane load this application receives, the plan was to move it onto multiple drives--the drives are running 80-90% and multiple spindles would at least solve a bit of that performance hit.

Thanks and I'll post back what I find out after trying this!

-David

---
David R. Longnecker
Web Developer
CCNA, MCSA, Network+, A+
Management Information Services
Wichita Public Schools, USD 259