Question about simple recovery model and tran log size.
-
I have read all BOL docs RE: recovery models. Now, in some instances I am seeing the log sizes increase fast and substantially in databases with simple recovery model.
Could the log increase be explained solely by huge implicit transactions - aka lousy sql - or, could this have something to do with the recovery model; as in, switching to full recovery model would keep the log from growing out of control?
Is there another setting that could help keep the log nicely trimmed?
{Francisco} -
This is the first of your 2 choices: huge implicit or explicit transactions or DTS packages or bulk insert or something like that. Simple Recovery Model is what you want to keep a log "nicely trimmed". But Active Transactions could not be deleted from the log, so it grows. Try to switch from logged operations to non-logged operations like from Delete (logged) to Truncate in the case where you have to delete all rows from the table or use tricks to limit the number of rows for one operation. There was several discussions on this site how to limit a number of the rows affected by individual transaction, you probably can search this site's discussions. Read about the Bulk-Logged recovery model that should not log bulk operations like Bulk Insert, maybe you may switch to Bulk-Logged for the short time and then back to Simple.
Regards,Yelena Varsha
-
Actually, there are no "non-logged" operations in SQL Server. There are operations, such as BULK INSERT and TRUNCATE, that are minimally logged. But all operations are always logged, meaning any operation can be rolled back.
-
Although I could easily be proved wrong, I was thinking implicit because i don't think anyone (experienced) intentionally will act on millions of rows - filling up the logs explicitly.
TRUNCATE table is non-logged; besides housekeeping of space allocated.
How about how often Sql Server issues a checkpoint in simple mode?
At first looks, it doesn't seem like a good idea.
{Francisco} -
TRUNCATE table is non-logged; besides housekeeping of space allocated.
No, like I wrote, TRUNCATE is minimally-logged. It logs which pages are deallocated, instead of deleting each row and logging each operation. You can rollback a TRUNCATE TABLE statement anyway, whichever recovery model you are using (as long as you are in a transaction of course).
How often a checkpoint is issued depends on the time set for the recovery interval. The longer recovery may take, the more time between checkpoints. No difference between recovery models there. With simple recovery however checkpoints will be issued in some other cases as well. One of those cases is when a minimally-logged operation is performed.
-
Ah, well; that is true for just about anything inside the scope of a tran. I would not use truncate as part of a long tran though: that kind of SQL possibly is what was filling up the log. Besides, when it comes to using Truncate I want minimal logging as expected.
Thanks for the help.
{Francisco} -
As far as i know when the database in Simple Recovery Mode , check point is issued depending on the recovery interval and when the T-Log is 70% full..
..hema
-
Ahhh ... the actual info on recovery interval is,
" SQL Server estimates how many data modifications it can roll forward in the recovery time interval. SQL Server typically issues a checkpoint in a database when the number of data modifications made in the database after the last checkpoint reaches the number SQL Server estimates it can roll forward in the recovery time interval. Sometimes SQL Server will issue the checkpoint when the log becomes 70 percent full, if that is less than the estimated number. For more information, see Checkpoints and the Active Portion of the Log.
The frequency of checkpoints in each database depends on the amount of data modifications made, not on any time-based measure. A database used primarily for read-only operations will not have many checkpoints. A transaction database will have frequent checkpoints. "
So it won't necessarily issue a checkpoint when the log is 70% full.
--------------------
Colt 45 - the original point and click interface
-
So it won't necessarily issue a checkpoint when the log is 70% full.
Actually, if the database is in simple recovery mode and the database is in log truncate mode, it will issue a checkpoint when the log is 70% full.
-
Yes, rapid transaction log growth can be the result of implicit transactions, but such transactions are not necessarily "lousy sql". A single, simple insert-select query from a large table might require millions of row inserts to be logged. While this is progressing, the active portion of the log cannot be truncated, even in Simple recovery mode.
Expect transaction log growth in Simple recovery mode to roughly equal the sum of all concurrently-executing write operations, i.e. if you use sql statements to copy 3 tables of 2GB each, and the execution of these statements overlaps in time, expect the log to grow by 6GB and plan accordingly.
There are ways to reduce the impact of such operations, but log growth does not necessarily imply bad sql.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply