Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Secondry File creation in sql server 2000 Expand / Collapse
Author
Message
Posted Wednesday, April 25, 2012 3:23 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 28, 2014 6:42 AM
Points: 24, Visits: 129
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
Posted Wednesday, April 25, 2012 2:45 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 1:18 PM
Points: 442, Visits: 1,124
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
Post #1290273
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse