Automatic File Growth

Question

Automatic File Growth

Jared Metzger

Right there with Babe

Points: 766
More actions
October 19, 2004 at 12:30 pm

#163598

I have a database that is set to automatically grow by 20%. When I run sp_spaceused on the database the database size was 86665 MB and the space reserved was 59312 MB. Even though sp_spaceused was still reporting 28092 MB of unallocated space, the Database grew to 104592 MB last night. What am I missing here? Shouldn't the database have had 28 gig of room to grow? I am perplexed as to why it preformed the auto grow function. Any thoughts?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Taj Mr or Mrs. 500 Points: 596 More actions · Answer 1

Well, First things first. I think to let automatically grow DB by 20% is a big number. You need to bring it down to about 5%. Secondly you need to set a Maximum Growth limit in the DB propeties and monitor its growth, and the numbers you are mentioning doesnt make sense and I hope u have multiple GB's disk installed. You need to back up the database and then back up transaction logs and then run DBCC Shrinkfile..

Hope that helps...

Thanks

Taj

Tajammal Butt

Jared Metzger Right there with Babe Points: 766 More actions · Answer 2

The data files are on a SAN so there really isn't any worry about running out of space. The reason I have the auto grow set so high is that the database grows about 1 gig a week. Since frequent expansions of the data file size aren't a good thing, I let it grow in spurts so that it only has to grow several times a year. I do admit that I am rather perplexed by the numbers being reported by sp_spaceused. I am very hesitant to shrink the database, because it is just going to grow again and I fear that may cause fragmentation.

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 3

Does anyone have more detailed explanation on sp_spaceused then in BOL?

Regards,Yelena Varsha

iLoveSQL SSCrazy Points: 2888 More actions · Answer 4

What Taj suggest is correct. Even though you have enough room to growth, you shouldn't let the SQL server to take care of the growth because it might grow in the middle of business hour and it can take a lot of resource while expending. In our environement, I have a job to check if free space getting out or not and expend memeory but just in case I miss that, I set the db growth to fixed number of MB depends on database.

Back to sp_spaceused problem, first try sp_spaceused @updateusage = 'TRUE' to get the accurate the data.

Search the "sp_spaceused" in this forum, you will get enough information about this.

Taj Mr or Mrs. 500 Points: 596 More actions · Answer 5

Well, I have worked with sp_spaceused and frankly speaking the numbers dont make much sense to me either. but to get better picture of the database space being used, I use taskpad view of the database from enterprise manager. These numbers actually match the size of the ldf and mdf files. The view will also tell you free space and used space for both the data and ldf files.

Taj

Tajammal Butt

Jared Metzger Right there with Babe Points: 766 More actions · Answer 6

Thanks for the advice. I will have to investigate more. In the mean time I have set an alert to notify me when the Database is within 10 gigs of its limit, and I turn off the auto gorw feature.

Thanks again!