how to partition mdf file?

Question

Post reply

how to partition mdf file?

Noman Tariq-346298

Old Hand

Points: 328
More actions
July 24, 2006 at 11:11 am

#169765

hi,
im new in SQL Server 2005, and i want to partitioned my datafile.
can anyone explain how to devide .mdf file into two filegroups.

Thanks,
Noman

Viewing 5 posts - 1 through 4 (of 4 total)

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

Hans Lindgren SSChampion Points: 10496 More actions · Answer 1

Hi,

You cannot devide one datafile between two filegroups.

One filegroup can have many files.

One file can belong to maximum one filegroup.

One DB can have many filegroups.

One filegroup can belong to maximum one DB.

Happy hunting,

Hanslindgren

Stephen Turner Mr or Mrs. 500 Points: 540 More actions · Answer 2

You have two levels in the hierarchy for each database, you can split a DB into filegroups and those filegroups into files. Normally a DB has one filegroup called PRIMARY and two files, Data and Log.

It sounds like you want to create a new filegroup for your database, create a similar pair of Data and Log files in it, then move some of the objects from the PRIMARY filegroup to the new filegroup. This is all done with ALTER DATABASE, or the management studio front end.

Can I ask why you want to do this? Most databases work well with a single data file and a single transaction log. Unless you have multiple disk systems (big hardware) and want to improve performance or want to do piecemeal backups multiple filegroups are not worth the effort.

Noman Tariq-346298 Old Hand Points: 328 More actions · Answer 3

thanks for your replies, yes you are right i want to do same as you asume. i want performance and want move our history tables into the other filegroup. in order to get the high speed and max performance for our website users.

please keep me posted if you have other best practices or suggestions.

Thanks

Noman

Stephen Turner Mr or Mrs. 500 Points: 540 More actions · Answer 4

Just moving old data out to another filegroup won't give a performance boost. The performance comes from SQL server being able to overcome a disk I/O bottleneck by reading from two totally separate disk subsystems.

If there is no disk I/O bottleneck, or if you don't put the separated files on separate drives with separate controllers then you won't get a big performance boost.

If you want performance there are a whole load of other practices that are best tried first before splitting filegroups. What else have you tried?

My hit list started with optimising long running queries, then optimising frequently running queries. With that I reduced server cpu load from 45% to about 16%. My next step is to recode a lot of the front end...