Secondry File creation in sql server 2000

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Notification Services » Administration » Secondry File creation in sql server 2000

Secondry File creation in sql server 2000

Rate Topic

Display Mode

Topic Options

Author

Message

anand.mind

Posted Wednesday, April 25, 2012 3:23 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, October 09, 2012 11:46 AM
Points: 17, Visits: 47

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?

Post #1289669

Eddie Wuerch

Posted Wednesday, April 25, 2012 2:45 PM

SSC-Addicted

Group: General Forum Members
Last Login: Saturday, May 11, 2013 12:25 PM
Points: 433, Visits: 1,048

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

Post #1290273

« Prev Topic | Next Topic »

Permissions