Post reply

Automatic File Growth

  • 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?

  • October 19, 2004 at 2:31 pm

    #527246

    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

  • October 19, 2004 at 2:55 pm

    #527253

    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.

  • October 19, 2004 at 4:04 pm

    #527256

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

    Regards,Yelena Varsha

  • October 20, 2004 at 9:55 am

    #527332

    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.

  • October 21, 2004 at 8:23 am

    #527470

    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

  • October 21, 2004 at 12:02 pm

    #527520

    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!

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

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