I have been monitoring file growth via the data collector in sql server 2008 r2 for two weeks. The database is growing consistently at around 35(MB)/Day. The DB has not yet hit the initial size of 2 GB.
The DB files auto growth is set to 5MB and I would like to try a different approach, so I am looking for suggestions and or comments.
There is a tuning task that runs every-week on Sunday night at 1:30 AM. The task will:
- Check Database Integrity
- Shrink the Log File – (This is ok because logging mode is Simple)
- Shrink Database
- Reorganize Index
- Rebuild Index
- Update Statistics
- Clean Up History
I would like to add two more steps to the weekly tuning plan:
- Grow the database file by 500 MB if the used space reaches a certain threshold or total size.
- Grow the log file by 250 MB (after the shrink) if the used space reaches a certain threshold of total size.
By placing the growth burden in offline hours, I hope to gain performance by reducing the number of auto-growth events during heavy loads.
I have two questions relating to auto growing files.
- The best place to put the file grow steps would be prior to the current steps or after?
- If I use the
ALTER DATABASE|MODIFY FILE
to grow the file then how can I determine ifSpaceUsedInFile >= (TotalFileSpace-@AllowanceThreshold)
?