December 17, 2008 at 11:45 pm
Hi All,
Can someone answer the following questions please?. I have no idea about these concepts before. It would be very helpful to me if any one of you suggest any links relating to these topics.
1. when will the log shrink for simple recovery model?
1.1. after the transaction is commited?
1.2. after the log is written to data file (log switch)? or after back up?
2. what is the meaning of "log switching" and when does "log switch" happen?
3. when does the commited data in the log files move to data file.
--Kiran.
Regards,
-Kiran
December 18, 2008 at 12:15 am
Kiran (12/17/2008)
1. when will the log shrink for simple recovery model?
Never. The log is truncated on a checkpoint, meaning the inactive log records are discarded and the space made available for reuse. The log file won't shrink though
The only way to shrink the log file is to manually run a shrink file. This should not be done regularly due to the cost of regrowing the file. Only if the log has grown excessively large and it's not likely to ever get that big again.
3. when does the commited data in the log files move to data file.
When a checkpoint occurs or when the lazywriter writes the page out due to memory pressure.
p.s. why is this "Urgent"?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 18, 2008 at 11:17 am
unless you have a good reason, don't shrink any of the db files. it may cause performance issues
December 18, 2008 at 8:47 pm
Is the question....When does log truncate happen in Simple Recovery model?......It think you probably need to modify the question ............
3. when does the commited data in the log files move to data file.
Well, when a checkpoint occurs. Checkpoint writes all dirty pages to the disk. Dirty pages are those that are entered in the cache and modified, but not written to the disk yet and Checkpoints save time during a later recovery by creating a point at which all dirty pages are guaranteed to have been written on the disk
What does log switching mean??? I am not aware of it.....any answers peeps.........probably a resource would be great if you could post it.....
cheers!!
December 19, 2008 at 12:01 am
Krishna_DBA (12/18/2008)
3. when does the commited data in the log files move to data file.Well, when a checkpoint occurs. Checkpoint writes all dirty pages to the disk.
The lazy writer also writes dirty pages to disk. The checkpoint process does it to keep recovery time low, the lazy writer because of memory pressure.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply