June 24, 2003 at 7:52 am
SQL7 sp4
I have a large db in 2 filegroups.
I run a shrink job every week to remove the freespace.
After the weekly load using bulkinsert, the amount of freespace is 38% in one filegroup and 32% in the other.
I autogrow one filegroup by 10% and the other by 20MB.
The extra freespace eats up the space I need for my backups.
Does anyone know how I can stop sql from adding so much extra freespace?
Thanks.
June 24, 2003 at 9:40 am
I guess you are totally heading in wrong direction, from your question it is my understanding that your data file or log file and the back file are on the same disk. Which is very bad practice. You need your backup file to restore when your hard disk crashes, so you definitely need a separate disk for your backup also considering the performance issue you need separate disk for your data file to reduce unwanted disk I/O. Come to your question, you can restrict the file growth by un-checking the AutoGrowFile property in the database properties. But again this will lead to problem; the database will run out of space and the user process will be delayed or killed (Now you need to worry about your Error log file growth). The best way is to allow the auto grows and shrink the database file periodically when there is a low activity on the server.
Shas3
June 24, 2003 at 9:53 am
Thanks Shas3,
This is a reporting db and I have additional backups on the VAX/Alpha platform. My mission-critical dbs use separate drives to store the data, the logs, and the backups.
Loading is done once per week overnite so I/O is not a consideration.
I am just trying to find out why the datafiles grow to 31 - 38% when I set them to grow at 10% for one filegroup and 20MB for the other one.
I am finding nothing in the MS Knowledge Base or SQL BOL.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply