March 22, 2011 at 12:17 pm
Sachin Nandanwar (3/22/2011)
GilaMonster (3/22/2011)
Sachin Nandanwar (3/22/2011)
Do shrinking of database logged even though the recovery model is SIMPLE ?All data modifications are logged regardless of the recovery model.
So when a checkpoint occurs the log file should come to its normal size.
A checkpoint doesn't change the size of the log files. It just marks a section of the log as ready to be reused by the transaction log process. Logs don't shrink on their own unless you have AutoShrink on (bad idea).
Gail, correct me if I mis-stated this please.
March 22, 2011 at 12:27 pm
Sachin Nandanwar (3/22/2011)
GilaMonster (3/22/2011)
Sachin Nandanwar (3/22/2011)
Do shrinking of database logged even though the recovery model is SIMPLE ?All data modifications are logged regardless of the recovery model.
So when a checkpoint occurs the log file should come to its normal size.
No. Why do you think that?
Nothing reduces the size of a SQL file (log or data) other than a shrink operation
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
March 22, 2011 at 12:29 pm
Brandie Tarvin (3/22/2011)
A checkpoint doesn't change the size of the log files. It just marks a section of the log as ready to be reused by the transaction log process. Logs don't shrink on their own unless you have AutoShrink on (bad idea).
Gail, correct me if I mis-stated this please.
I think it does.Found this from MSDN
If log records were never deleted from the transaction log, it would eventually fill all the disk space that is available to the physical log files. Log truncation automatically frees space in the logical log for reuse by the transaction log.
Except when delayed for some reason, log truncation occurs automatically as follows:
* Under the simple recovery model, after a checkpoint.
* Under the full recovery model or bulk-logged recovery model, after a log backup, if a checkpoint has occurred since the previous backup. For more information, see "Log truncation under the full and bulk-logged recovery models," later in this topic.
http://msdn.microsoft.com/en-us/library/ms189085.aspx
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
March 22, 2011 at 12:29 pm
Yes I did mention that the database is in Simple recovery mode when I started this thread...
March 22, 2011 at 12:34 pm
Sachin Nandanwar (3/22/2011)
Brandie Tarvin (3/22/2011)
A checkpoint doesn't change the size of the log files. It just marks a section of the log as ready to be reused by the transaction log process. Logs don't shrink on their own unless you have AutoShrink on (bad idea).
I think it does.Found this from MSDN
If log records were never deleted from the transaction log, it would eventually fill all the disk space that is available to the physical log files. Log truncation automatically frees space in the logical log for reuse by the transaction log.
Except when delayed for some reason, log truncation occurs automatically as follows:
* Under the simple recovery model, after a checkpoint.
* Under the full recovery model or bulk-logged recovery model, after a log backup, if a checkpoint has occurred since the previous backup. For more information, see "Log truncation under the full and bulk-logged recovery models," later in this topic.
You are misreading that entry. It says it truncates, not shrinks. Truncate allows SQL to reuse the portion of the log that was just freed. It's like eating the cookies off a plate. You allow that section of the plate to be used for more cookies, but the action itself does not alter the size of the plate.
EDIT: To further extend the analogy (re: the first sentence about filling up disk space), if you don't eat any cookies off the plate, but you get more cookies, you fill up your plate by constantly putting more cookies on it. Until the plate is so full that cookies no longer fit, in which case, you have to add more plates to hold the additional cookies or the cookies just can't go anywhere (SQL Server stops).
March 22, 2011 at 12:38 pm
You are misreading that entry. It says it truncates, not shrinks. Truncate allows SQL to reuse the portion of the log that was just freed. It's like eating the cookies off a plate. You allow that section of the plate to be used for more cookies, but the action itself does not alter the size of the plate.
Really need to do some serious reading to understand the log file architecture better and post questions in case of any doubts.
Thanks for all the help Gail and Brandie.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
March 22, 2011 at 12:43 pm
You're welcome. I hope the analogy made things easier to understand for you.
March 22, 2011 at 12:49 pm
Brandie Tarvin (3/22/2011)
You're welcome. I hope the analogy made things easier to understand for you.
Yes it was perfect.
Now I am much more clearer on the basics of log files than I was before I hijacked 😉 this thread.
Thanks once again Brandie.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
March 23, 2011 at 2:55 am
Sachin Nandanwar (3/22/2011)
Brandie Tarvin (3/22/2011)
A checkpoint doesn't change the size of the log files. It just marks a section of the log as ready to be reused by the transaction log process. Logs don't shrink on their own unless you have AutoShrink on (bad idea).
Gail, correct me if I mis-stated this please.
I think it does.Found this from MSDN
If log records were never deleted from the transaction log, it would eventually fill all the disk space that is available to the physical log files. Log truncation automatically frees space in the logical log for reuse by the transaction log.
Except when delayed for some reason, log truncation occurs automatically as follows:
* Under the simple recovery model, after a checkpoint.
* Under the full recovery model or bulk-logged recovery model, after a log backup, if a checkpoint has occurred since the previous backup. For more information, see "Log truncation under the full and bulk-logged recovery models," later in this topic.
Oh...great conversation going on.
Just now i opend SSC.
Sachin, Check out my article for better clarification Truncating vs shrinking the log file [/url]
Form blog
Shrinking the log file will reduce the physical log file size. Shrinking the log file is not best practice until you don’t have any other option.
Truncating a log file will change one or more VLF file’s status from active to inactive. By default, the SQL server has changed the VLF from active to inactive and reuses it automatically (Just like a circular).
Edit: To correct the link
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
March 23, 2011 at 4:33 am
Thanks for the link muthukkumaran
Following is the extract from your blog which is your definition of truncation of log
SQL server run the CHECKPOINT truncates the inactive portion of the log file....
While the extract below is from an article posted by Gail
The act of marking one or more VLFs as inactive is generally called log truncation.
http://www.sqlservercentral.com/articles/Transaction+Log/72488/
Are they both same ?Because I am not to sure what did you meant by "truncates the inactive portion of log files..."
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
March 23, 2011 at 5:05 am
Sachin Nandanwar (3/23/2011)
Thanks for the link muthukkumaranFollowing is the extract from your blog which is your definition of truncation of log
SQL server run the CHECKPOINT truncates the inactive portion of the log file....
It's not the inactive portion of the log that gets truncated. That's not necessary, the inactive portion is the portion of the log that can be reused. It doesn't need anything else doing to it to make it reusable.
Truncation is when one or more active VLFs (virtual log files) is marked as inactive (and hence reusable). Checkpoint does that if and only if the VLF is not needed for anything (active transaction, backup, replication, etc, 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
March 23, 2011 at 5:07 am
muthukkumaran (3/23/2011)
Truncating the log file will reduce the logical log (VLF) file size. By default, SQL server truncates the transaction log file OR deletes the logical file (VLF) automatically and reuses the log file again and again (Just like a circular).
Truncating does not reduce the VLF size. It just changes the status of one or more VLFs from active to inactive. Truncation also does not delete any VLFs. Deleting a VLF would result in a change in the size of the log file and its only shrink which does that.
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
March 23, 2011 at 5:15 am
What Gail said.
Re-read the cookie analogy.
March 23, 2011 at 7:52 am
If I understand this correctly let me get this right in terms of cookie analogy.
The cookie plate is your physical log file.
The good cookies are your inactive VLF.
The bad cookies are your active VLF.
The cookie plate contains a mixture of good and bad cookies.
The process of replacing the bad cookies(active VLF) with good cookies(inactive VLF) is log truncation.
The process of breaking some part of the plate so that it can hold less amount of cookies(good or bad) is log shrinking.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
March 23, 2011 at 8:03 am
An extreme simplification, but accurate enough. Though there's nothing 'good' or 'bad' about active or inactive VLFs.
I'd maybe say rather that the cookies are the active VLFs, the empty spaces on the plate are inactive VLFs and eating the cookies like log truncation and then logging (from data modifications) would add more cookies to the plate.
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 15 posts - 16 through 30 (of 69 total)
You must be logged in to reply to this topic. Login to reply