Transaction log is not truncated even after log backup in FULL recovery model

Question

Transaction log is not truncated even after log backup in FULL recovery model

Narine M

SSCrazy

Points: 2672
More actions
October 13, 2016 at 1:25 am

#313798

Hi All,
I have backed up the log file using SQL Server 2012 SP1 backup option. In the wizard options the truncate log after backup is selected. The backup is completed successfully but the transaction log not truncated.Kindly help on this..
Thanks in advance!
NM

Viewing 6 posts - 1 through 5 (of 5 total)

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

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

Why do you say the log isn't being truncated? Where are you looking and what are you expecting to see that you aren't?

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

naren.ece2012 (10/13/2016)
Hi All,
I have backed up the log file using SQL Server 2012 SP1 backup option. In the wizard options the truncate log after backup is selected. The backup is completed successfully but the transaction log not truncated.Kindly help on this..
Thanks in advance!
NM

If you're looking at the size of the log file, be advised that "log truncation" is NOT something that decreases the size of the LDF file itself.

That, notwithstanding, there's really not enough information in your post to say anything definitive. Please answer Gail's post above to give us a better idea.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Narine M SSCrazy Points: 2672 More actions · Answer 3

Actually in our environment, we have 310 Gb of production database in that we have 200 Gb of data files and 110 GB of log files.The log space has been increased due to the rebuild index JOB ran. The log backup is taken every onehour, but the log space is not truncated.Kindly help on this, i am new to DBA.

Regards

NM

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

Why do you say the log isn't being truncated? Where are you looking and what are you expecting to see that you aren't?

It sounds like you're expecting the file size to change. That's not what truncate does. Truncate marks space in the file as reusable.

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

naren.ece2012 (10/15/2016)
Actually in our environment, we have 310 Gb of production database in that we have 200 Gb of data files and 110 GB of log files.The log space has been increased due to the rebuild index JOB ran. The log backup is taken every onehour, but the log space is not truncated.Kindly help on this, i am new to DBA.
Regards
NM

You're mixing up some terms. "Log truncation", for most of us, means that data in the transaction log file has been deleted because whatever transaction caused it has been completed and committed AND a log file backup has occurred. That process does not decrease the size of the log file.

What you're talking about is the "shrinking" of the file-space used and that doesn't happen automatically just because data has been deleted from the log file.

If this is a regular problem (and, for restore purposes, I do agree that the log file is much bigger than it should be), consider temporarily switching the database to the "BULK LOGGED" Recovery Model during the index rebuilds of large tables so that you can take advantage of "Minimal Logging". There is a caveat to that, though. You need to read up on the effect that minimally logged operations can have on Point-in-Time restores.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)