Home / server / Questions / 209596
In Process
Nai
Asked: 2010-12-07 06:50:18 +0800 CST

Managing Log File in SQL Server 2008

  • 772

These are my current settings:

Recovery mode: Simple Logfile initial size: 10 mb Logfile autogrowth: None Database auto shrink: Off

The logfile autogrowth is set to None because I don't want the logfile to grow. My objective is to keep the log file consistently small as I do not need it for recovery purposes.

Are the both settings okay for that?

Also,what happens if I have a big transaction involving numerous inserts and deletes (over 10 mb worth of change). Will the operation not complete because the log file ran out of space?

sql-server log-files sql-server-2008 transaction-log

4 Answers

    • In somple recovery model,. the log content is discarded every time a commit is done with no open transactions. This hsould happen quite often.

    • IF the log overloads, the db can not commit. Changes will be rolled back. Note that in simple model that would be RARE, but it MAY be theoreitcallys possible. I would call that ery theorettical, though.

    • At the same time, you can not use log backups to make point in time recovery.

    • 3

  2. Just to clear things up:

    In Simple recovery model, Log file truncation is not directly related to commit. Log file is truncated making Virtual Log files re-usable for other transactions only when a checkpoint occurs. Checkpoints are done as frequent as SQL thinks it's necessary to write dirty pages to disk. Also at checkpoint the inactive part of the log file (not used by any transaction) is truncated making the Transaction log re-usable for other transactions. After the log was truncated, the Log file than can be shrinked in order to reduce the physical size if needed.

    So, the commit does not generate a checkpoint if you don't specify explicitly it in your transaction after commit. On the other hand the checkpoint which actually generates the log truncation will not have any effect if the Log contains information about uncommitted transactions.

    To answer your questions:

    • If the inserts and deletes are treated as bulk operation or commited once at the end of the transaction than yes...Log file will fill as much as it needs claiming all the space is has available. I recommend though enabling auto growth option of the Log file. You can specify a limit, but restricting the Log file to 1000 MB is quite risky.

    Here more about Log file management: http://technet.microsoft.com/en-us/library/ms189085.aspx

    • 0

  3. Yes, if the log fills up due to a larger transaction and the log file is set to NO autogrow, then the transaction will fail and roll back.

    This might not be as rare as you think. Index rebuilds are a common maintenance activity that use a large amount of log space.

    You didn't mention the size of your data file, or the kind of activity your database handles, so I have no way of knowing whether 10MB is big enough.

    I would never leave a log file with autogrow disabled. That's just asking for trouble. Sure, you'd like to be notified when it grows, and you could make a monitor that does this. You just don't want a page in the middle of the night because some user ran a big report or something.

    • 0

  4. if this is your production DB then its is not a good idea to have a Simple Recovery model. Autogrowth should help u out unless u run out of disk space.

    • -3