transaction log shrinkage

Question

transaction log shrinkage

sam_mastermind

SSCommitted

Points: 1945
More actions
July 12, 2010 at 10:07 am

#237582

Can anyone help me out regarding the log truncation for the following scenarios :
scenario 1:
a.Database Autoshrink option is on.
b.Log file Autogrowth option is on.
c.Recovery model Full.
d.No log backup is happening.
Will the log file shrink(means the inactive log entries) after checkpoint occurs?
scenario 2:
a.Database Autoshrink option is off.
b.Log file Autogrowth option is on.
c.Recovery model Full.
d.No log backup is happening.
Willthe log file shrink(means the inactive log entries) after checkpoint occurs?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Homework? Exam? Interview?

If the recovery model is full, checkpoint is insufficient to mark log space as reusable. A log backup is required. A checkpoint only marks log space as reusable in simple recovery.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sam_mastermind SSCommitted Points: 1945 More actions · Answer 2

May be the way I framed it, looks like as you said(homework ?/ Exam ? / Interview ?). But truly I want to know it............... and i think that is the most important thing.

sam_mastermind SSCommitted Points: 1945 More actions · Answer 3

sam_mastermind

SSCommitted

Points: 1945

July 12, 2010 at 10:22 am

#1191576

Thanks a lot.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

sam_mastermind (7/12/2010)
But truly I want to know it............... and i think that is the most important thing.

If this is the case then 🙂

http://msdn.microsoft.com/en-us/library/ms179478.aspx

http://msdn.microsoft.com/en-us/library/ms190440.aspx

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

sam_mastermind (7/12/2010)
May be the way I framed it, looks like as you said(homework ?/ Exam ? / Interview ?). But truly I want to know it............... and i think that is the most important thing.

I disagree with your statement. Forums are properly used to help people with real world work-related problems and are "staffed" by people who are donating their personal time to help out with business solutions. I think it is inconsiderate to ask someone to help with homework, etc when they are volunteering their time in this manner. At a minimum a poster should explicitly state that they are looking for help on a homework, interview, etc question so that answerers can decide if they wish to help. Just my $0.02 here.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

prakskarry SSC Veteran Points: 202 More actions · Answer 6

Checkpoint in FULL Recovery Modle will not make log available for reuse or shrink it.

Sudeepta SSCommitted Points: 1646 More actions · Answer 7

In both cases, truncation of log will not occur. As said by Gila, a log backup is required to remove the inactive portion of the transaction log.

If you want to reduce the size of the transaction log, I suggested that you use Simple recovery model for the database.

Also, autoshrink option should not be enabled as it consumes a lot of valuable server resources.

Hope, this may help.

Thanks & Regards,
Sudeepta.
http://twitter.com/skganguly