Sudden Increase in Database Size

  • Hi,

    There was a sudden increase in the size of the production database. The database size grew by around 1GB & the transaction log file size also grew by around 1.3 GB. Under normal circumstances the database grows at the rate of 10-20 MB per day. I did check the size of tables & the indexes, there was not much change. We make a backup of transaction logs every hour & i came to notice that between size of the backup file at 4PM was 1.38 GB whereas under normal circumstances size of backup file ranges from 1MB-16MB.

    How can I come to know what transaction has caused the sudden increase in the database size.

    regards,

    Nithin

  • Looping

    Thanks

  • Did an index rebuild job run around that time?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Nope. Did not rebuild the index yesterday.

  • to get an overall idea.

    what is the overal size of the database and transaction log?

    what are the autogrow values of the files?

    what is the free space of the database and the transaction log?

  • Database Size:10,417 MB Space Used:10238 MB

    Log File Size: 1,524 MB Space Used: 22 MB

    Auto Growth for Data File:By 10 percent, unrestricted growth

    Auto Growth for Log File: By 10 percent, unrestricted growth

    Data File Free Space: 177 MB

    Log File Free Space:1501MB

  • Can u give sql server Log and Transaction Log of that time...

    Thanks

  • Date,Source,Severity,Message

    05/02/2011 23:00:10,Backup,Unknown,Log was backed up. Database: This is an informational message only. No user action is required.

  • any operation performed that was not a part of daily routine

    Regards,

    Syed Jahanzaib Bin Hassan

    MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

    My Blog

    http://www.aureus-salah.com

    Regards,
    Syed Jahanzaib Bin Hassan
    BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

    My Blog
    www.aureus-salah.com

  • No operations were performed. All jobs are scheduled towards the End Of Day.

  • Some operation made your log grow until you filled the disk.

    Unless you were running a trace at that time there is no way to know what made it grow.

    The log full generated an error though, so the culprit most likely got an error.

    Since this is an exceptional growth you just have to find who/what did something out of the ordinary.

    While we are at it, change the 10% growth to a fixed amount in MB, that'll keep you from having bad surprise.

Viewing 11 posts - 1 through 10 (of 10 total)

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