Proactive DB Size Monitoring

  • All,

    I'm reviewing our databases and percent free space on the files. If a db is in simple recovery mode, do I need to worry about free space for a transaction log since it's maintained by the system?

  • Yes you will. Even in a simple recovery model every change gets logged first before the change is applied, so if your log file has insufficient space and cannot grow, processing will essentially stop.

    That said - you may not need a HUGE amount of log space for that DB if the transactions are small. Since the transaction will be in the log only long enough to make sure everything is consistent and the transaction can be committed, the log file may not ever need to get big.

    How much will be required is purely driven by your individual DB's activity. If you're not sure how big is enough, create a reasonable autogrowth process (so the growth factor are not so small they fragement your diskdrive excessively, but not so big that they create major delays each time it needs to grow), and let the DB tell you how much it needs.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Thanks for the info.

  • check this out for the disk space....

    http://www.sqlservercentral.com/scripts/disk+space/66656/

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

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

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