Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

4 Answers 4

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

share|improve this answer
    
+1 Thanks this cleared some things up for me as well. –  Sean Howat Dec 6 '10 at 16:21

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

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.