Post reply

Trying to determine why database size grew from 108mb to 27GB

  • April 15, 2005 at 11:09 am

    #164043

    Greetings,

    I manage about 15 SQL Servers all at various locations.  All the machines,configuration and patching are pretty much the same.

    One SQL server last night, running on Win2K3 rolled over and died because the database grew to 27gb.  I ended up truncating the transaction log to get the machine back and running.  Any idea on how I could possibly go about trying to determine what would cause this?   There were no errors that I could see in the SQL log, nothing to show that some process might be in a loop, etc.  The database is perfectly valid so its not like 27GB of crap data was written... it was strictly the transaction log.  I realize that I probably reduced my options when I fixed the issue but the site needed to be back up.

    I DO have a backup of the database before I truncated the data but it will recreate back to the full 27GB size.  Anything I could look for/at would be appreciated.

    Thanks,

    Chris

     

  • April 15, 2005 at 11:18 am

    #552967

    Do you have high transaction going on.  I have the same problem on our datawarehouse server but it's because we set the transaction log to auto growth.  It just keeping growing as it need space without going looping back to used the space that was already commited.  The rule of thumb is now to not allow auto growth on your transaction log.  I would rather be paged on it and go back to work or set what you think is appropriate for the size of transaction or database activities.

     

    mom

     

  • April 15, 2005 at 11:27 am

    #552969

    It is turned on, but then its turned on for all of them as auto growth.  I can understand that it COULD grow to huge sizes I just cannot understand HOW.  There is a single user who accesses the database for MAYBE 1hr, 3 times a week.   Defintally not alot of data entry/manipulation volume.

    I will go back and change the setting to cap the size though just to make sure it doesn't happen again

    Thanks,

    Chris

     

  • April 15, 2005 at 12:41 pm

    #552989

    Is your database in full recovery mode and there is no transaction log backup at all? You may also have a maintenance job to rebuild the indexes.

    dbcc sqlperf(logspace) will tell you the size of your database log file and the percentage of used.

Viewing 4 posts - 1 through 3 (of 3 total)

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