transaction log growing

Question

Post reply

transaction log growing

inevercheckthis2002

SSCommitted

Points: 1819
More actions
September 29, 2010 at 1:38 pm

#148910

database recovery model = full
nightly backup of database and transaction log via a scheduled maintenance plan in sql server management studio
shouldn't that shrink the transaction log?
Thanks...
Marge

Viewing 11 posts - 1 through 10 (of 10 total)

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

JeremyE SSCoach Points: 16200 More actions · Answer 1

Log backups do not shrink the transaction log.

Please read through Gail's article on transaction logs. It should clear up any questions you have.

http://www.sqlservercentral.com/articles/64582/[/url]

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 2

The Full recovery model is set up to allow the transaction log to retain data over time. You can then backup the log on a regular basis, say, once every 1/2 hour, for example. This ensures that, in the event of an emergency, you will have all completed transactions available in the log backup up to 1/2 an hour prior to the problem.

So, if you set the database to Full, you must also schedule log backups. If you don't, the log will just continue to grow until it fills the drive(s) allocated to it.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

inevercheckthis2002 SSCommitted Points: 1819 More actions · Answer 3

Edogg (9/29/2010)
Log backups do not shrink the transaction log.
Please read through Gail's article on transaction logs. It should clear up any questions you have.
http://www.sqlservercentral.com/articles/64582/[/url]

Edogg said: "Log backups do not shrink the transaction log."

http://www.sqlservercentral.com/articles/64582/ says:

"Full Recovery model

"...Inactive log records are retained in the transaction log until a lob backup occurs.

"...Because log records are not discarded until they have been backed up..."

So, my question remains. I'm doing a transaction log backup nightly. Shouldn't that shrink the transaction log??

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 4

inevercheckthis2002 (9/30/2010)
Edogg (9/29/2010)
Log backups do not shrink the transaction log.
Please read through Gail's article on transaction logs. It should clear up any questions you have.
http://www.sqlservercentral.com/articles/64582/[/url]
Edogg said: "Log backups do not shrink the transaction log."
http://www.sqlservercentral.com/articles/64582/ says:
"Full Recovery model
"...Inactive log records are retained in the transaction log until a lob backup occurs.
"...Because log records are not discarded until they have been backed up..."
So, my question remains. I'm doing a transaction log backup nightly. Shouldn't that shrink the transaction log??

Shrink the log? No. Empty the log, yes.

But full recovery and only a nightly log backup... that's not the way to go. If you're going for full recovery you should be backing up more frequently. Otherwise, you're facing a situation where you may only be able to recover to the last full backup (which runs at the same time as the nightly log backup, right?). This is because, depending on the failure you're dealing with, you can't always take a tail log backup before you start the recovery process.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

paul.knibbs SSCoach Points: 15320 More actions · Answer 5

I agree. If you're only doing one transaction log backup a day you might as well put the database in Simple recovery mode and skip the log backups entirely.

Oliiii SSCertifiable Points: 5328 More actions · Answer 6

If you want your log to shrink after the backup you can add a step to shrink it, but if it's going to grow again during the day you'll just end up wasting IOs.

Either switch to simple recovery or do what is described above.

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 7

But, you really don't want to shrink your log over & over. If you had one abnormal set of transactions that caused it to grow, maybe, but if it's because you're not backing it up regularly, the answer isn't to shrink it. The answer is to either change the model or get the backups in place.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

inevercheckthis2002 SSCommitted Points: 1819 More actions · Answer 8

Grant Fritchey (10/1/2010)
the answer isn't to shrink it.

I agree that either the model or the backup strategy needs to be addressed.

However, I think I have a short term need to shrink an 11 GB transaction log. Looking for a way to do this. I see a "Shrink Database Task" in the Maintenance Plan toolbox- would that do it?

paul.knibbs SSCoach Points: 15320 More actions · Answer 9

If you absolutely have to shrink it you'd be better off using Shrink Files and selecting the specific ones you want to work on--Shrink Database would attempt to shrink the data files as well, and you *really* don't want to be doing that!

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 10

Yeah, use the shrink file function, not shrink database.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning