February 23, 2011 at 4:27 am
We have a sql2000(sp4) db with a single primary filegroup. We have 4 seperate LUNs assigned from the SAN for data, and the database file structure is as below :
e:\msdata1.mdf 300gb
f:\msdata2.ndf 25gb
g:\msdata3.ndf 25gb
h:\msdata4.ndf 25gb
All i would like to simply do, is spread some load from the first lun to the other 3 without splitting filegroups, and end up with something like :
e:\msdata1.mdf 150gb
f:\msdata2.ndf 75gb
g:\msdata3.ndf 75gb
h:\msdata4.ndf 76gb
I have looked at using shrinkfile, but this would empty the mdf (almost), which is not what i would like to achieve.
My apologies, as i'm quite new to this, but can anyone offer any advice ?
Thanks
Andy
February 25, 2011 at 12:11 am
Why fear the FileGroup? It may be best to emptyfile the 25GB files and make a new file group, each at 75G on f, g & h. Then assign indexes too the new file group. BE SURE TO UPDATE the statistics after relocating indexes.
There are many strategies for doing what you need. Making 3 file groups, one on each drive to hold hot clustered indexes and non clustered. Or, CI on one fg and NCI on another fg. Or spread CI and NCI's 'equally' across a multi-file group. It all depends on the server config, CPU count, disk set performance, and on and on. Testing will yield the best results for performance, but there are other reasons besides performance for the decision point on how to structure the files. Including recoverability (it may be best to get as much data off of Primary as reasonable in order to do independent backup/restores and keeping more of the database online while restoring in the event of a disaster).
But for simplicity, create 3 equal sized files in a new fg, reassign some important tables indexes on to the new fg and update stats.
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply