Member Avatar for joewazen

Hello,

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

  • 2 Contributors
  • 3 Replies
  • 119 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by sknake
Member Avatar for sknake

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.

This article should shed some light on what you need to do:
http://doc.ddart.net/mssql/sql2000/html/createdb/cm_8_des_05_9ak5.htm

Member Avatar for joewazen

Thanks a lot for the reply

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

Member Avatar for sknake

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.