April 25, 2012 at 3:23 am
I have a sql server 2000 database and it is too large. I decided to create a secondary file, so if new data insertion happens it should go to the secondary file. I have created secondry file with the intial size as 1MB and in the same PRIMARY filegroup. But when I am inserting new data, log file size is increasing only and not the secondry file size. I also limited the maxsize of primary file and ran checkpoint manually.
Can anyone help me to test it out that new data will go to secondary file?
April 25, 2012 at 2:45 pm
When multiple files exist in a filegroup, space is allocated from each file proportionally by the amount of free space in each file. Your new second file is only 1MB. If there is significantly more free space in the first file, then most new allocations will come from that one.
For example, if there's 500MB free in the first file, then new allocations from the new file only once for every 500 allocations from the first file.
In order to get data in the new file, grow it so the amount of free space in it can compete with the amount of free space in the first file.
Additionally, unless you measure data growth in kB/month or less, 1MB is a miserable starting file size. estimate what you may need in the next 6-12 months, grow the file for that now, and be done with it.
Eddie Wuerch
MCM: SQL
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply