shrink log file and do a transaction backup after transaction log is full

Question

shrink log file and do a transaction backup after transaction log is full

sqlfriend

SSC Guru

Points: 52794
More actions
March 27, 2011 at 10:45 pm

#147621

when I got a transaction log full, I usually immediately do a transaction log backup.
Should I do a shrink log also?
If so, shall I do a shrink log file before the backup, do a shrink log file after the transaction log backup?

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

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

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

Please read through this - Managing Transaction Logs[/url]

Shrinking a log is for when there has been some unusual, once-off activity that has made the log far larger than usual. If you get full transaction logs during normal operation then your log needs to be larger than what it it.

Also, log backups are not the only thing that cause a full log.

http://www.sqlservercentral.com/articles/Transaction+Log/72488/

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

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

Question: are you performing regular transaction log backups, or are you only doing them when you get the "log full" problem?

sqlfriend SSC Guru Points: 52794 More actions · Answer 3

We do regular transaction backup like every 3-6 hours.

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

Transaction log backups every 3-6 hours???? You realise with log backups 6 hours apart you're in a position to lose up to 6 hours of data in the case of a disaster. Is that acceptable?

Most important databases get log backups every hour, half hour or 15 minutes, some even less.

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

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

What do you mean by every 3-6 hours anyway? If you're backing the logs up via a SQL Agent job then those should be happening at a much more precise interval than that...I'm a bit confused as to how the gap between your backups can vary, unless you *are* only ever doing them manually.

sqlfriend SSC Guru Points: 52794 More actions · Answer 6

Sorry, I should make this more clear. we have 30 servers.

For some important databasse with lots of transactions we do transactionlog back up every one hour.

some servers are setup to do every 3 hours for less important one.

For more static one and less important one we do every 6 hours.

homebrew01 SSC Guru Points: 55553 More actions · Answer 7

If a database is in FULL recovery, and you are taking t-log backups, there's not really a good reason to only take them every hour, or 3 hours. Just change the schedule to take them every 15 minutes and you will be better off: Better recoverability and smaller log file .... win-win.