June 22, 2011 at 10:56 am
0 1 2 3 4 5 6 7 8 9
|______X_______|
*X is the active portion of the log*
I have a question regarding how the Tlog is written to. Please pretend for a moment that the above numbers represent a log file.
You know the issue when you shrink a file you sometimes have to backup the log file twice to get the active portion of the log moved to the begining of the log? Well, I was curious.... if the active portion of the log above is at 4, does that mean nothing can be written on 0-4 and only 4-9 is available for logging? So if the active portion was actually at 8 then only 8-9 would be available and the log would quickly become full?
I don't know if I'm looking at this the right way, so please let me know if I have it all wrong. I just saw an issue where a log was filling fast and after every scheduled log backup the reuse_wait_desc was still indicating that it was waiting for a log backup. I manually kicked off the backup job in between scheduled backups and that cleared it out. So I'm just trying to get a true understanding of how the log is being written to.
Thanks!
Kimberly
June 22, 2011 at 11:09 am
Provided the log was a fixed size, then yes, you would run out of space.
Also, it is a good practice to size log files so that you don't have to either shrink or grow them.
CEWII
June 22, 2011 at 11:51 am
The log file is circular. So if the minLSN was in VLF 4 and the current point of writing (the maxLSN) was in 8, then VLFs 9, 0, 1, 2 and 3 would be available. Once the log records in VLF 4 become not needed and the VLF is marked inactive, then 4 can be reused too, etc, etc.
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 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply