March 12, 2009 at 6:06 am
We have installed SQL 2005 for a Help Desk database. I don't have any experience with SQL, so would like to know how SQL allocates more free space. I noticed that my free space for my database is getting close to 0. Will SQL reallocate more space or do I have to tell it to and how would I do so? Thanks.
March 12, 2009 at 8:59 am
You can choose whether to have SQL files grow automatically, either by a percentage or a fixed amount. Best practice is to monitor file sizes and allocate more space manually as needed, and leave autogrow on to catch exceptional situations. You should also monitor the remaining drive space.
You can modify the settings in SSMS through the "files" tab in database properties.
March 12, 2009 at 9:44 am
Thanks. Looks like it grows by 1 meg? Our database has almost 3 Gigs allocated and it has about 150 megs free space. It started out with 300 megs free space in Jan 09. It looks like it takes about 20 megs a week. Should I up the growth to maybe 20 megs???
March 12, 2009 at 10:03 am
1MB is the default.
20MB for autogrowth is fine, you can set it to less if you wish as SQL will autogrow as many times as necessary until it runs out of space or reaches the maximum file size (if you set it). However by growing the file in small increments you are introducing fragmentation, which is generally bad. That is why I mentioned allocating the space manually - at the present rate you will be out of space in 7 weeks or so, so it's better to just allocate that space now. If the drive is shared with other processes (operating system etc) then make sure you leave enough space free.
What do you plan to do when the drive is almost full?
March 12, 2009 at 10:10 am
We have 8 Gigs free on that drive. If we have problems with space I guess I can move the database to a different drive on that server, but I believe we will be ok until we purchase a new server. Thanks for your help!
March 12, 2009 at 10:24 am
Ah, my mistake. Just as a reference, I like to allocate enough free space for at least a year on my databases - apart from lower fragmentation it's easier to maintain.
March 14, 2009 at 6:21 am
One other thing. Do I have to stop SQL before I change the free space settings???
March 14, 2009 at 6:41 am
No, any change in database properties does not require a server restart.
--Ramesh
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply