I have a 4 GB DB on SQL 2005 and the same on on sql 2008 that is growing 60MB per day, My concern is that should i allocate an unused space to the DB so that the DB wont create space for itself everyday (load on the server), what should be the size of the unused space for my DB
You need to measure your database growth, which you seem to have done, and use that to extrapolate for how much free space you need. Is this purely database growth and not the transaction log? If your database grows very fast you can reduce the fill factor so the SQL server will leave more space when organizing table data pages and indexes but the extra space can also have a negative impact on the read speed so you have to balance the tradeoff.
1. i am using sp_spaceused to get the DB size That's how i got the average growth per day 60 MB
2. what i need to know is that if my DB is growing by 60 MB/day, is it healthy to keep the default Auto Growth value of SQL 2005 which is 1MB? or do i need to adjust that? or do i need to increment the unused data size manually? i am just trying to find the proper way to keep my SQL Fast and Healthy
Thanks again for the reply and for the negative impact on the read speed hint because i didn't know there was drawbacks
Really I just leave the hardware related settings of SQL Server alone as they tend to be pretty good by itself. Unless this growth is causing problems there is no need to fix something that isn't broke ;)
Are you having any problems with it or are you just looking to optimize the database?
The only exception to what I mentioned above is splitting the database files up to different disks. This can enhance disk I/O performance if you're using scsi/ide. Otherwise just have a smoking fast setup and let SQL server do its thing.