StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Even in Simple Recovery mode, SQL Server will populate the log file in order to maintain ACID compliance and permit rollbacks (or "roll-forwards" in certain-disaster recovery scenarios). So you need to have enough room in there to at least handle the transactions that it may receive at one time and if not it will either autogrow or present an error.</p> <p>Now as a practical matter you have a couple of options. The simplest is to just give it sufficient space to handle the transactions. Another is to break it up into fewer transactions since in Simple Recovery it will permit that space to be reused once the transaction is fully committed and finalized. </p> <p><strong>Edit for clarity and responding to comments:</strong> SQL Server will place almost all actions (there are some exceptions, but they aren't material here) in an implicit transactions even if an explicit one is not called. So, if you say execute a command that inserts a million rows it will have an implicit transaction for that insert and even in simple recovery mode all of those million rows will impact the transaction log until the transaction is fully committed and complete. If you want it to release the space faster, rewrite your code so that instead of one statement inserting a million rows you have ten statements inserting a hundred thousand each.</p> <p>It wasn't clear from your question that it was <em>only</em> growing during the full backups, but yes the backup process does affect its ability to release space in the log while it is occurring. That is because a full backup also includes the data from the log file so the Server needs to make sure that information is available during the backup process. There is a related discussion at <a href="http://www.sqlskills.com/blogs/paul/post/debunking-a-couple-of-myths-around-full-database-backups.aspx" rel="nofollow">In Recovery</a>.</p> <p>I would generally be extremely reluctant to postpone a scheduled backup, but it may help in this particular scenario. It also sounds like breaking your transactions up into smaller pieces may ultimately be the way to go though.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload