if we shrink log file daily after tlog backup ...will there any issue

Question

Post reply

if we shrink log file daily after tlog backup ...will there any issue

dastagiri16

Hall of Fame

Points: 3544
More actions
December 2, 2014 at 8:00 am

#313510

if we shrink log file daily after tlog backup ...will there any issue

Viewing 15 posts - 1 through 15 (of 17 total)

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

Siva Ramasamy SSCarpal Tunnel Points: 4026 More actions · Answer 1

It depends. Can you explain why are you planning for a shrink?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Are you only doing a once a day tlog backup?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

dastagiri16 (12/2/2014)
if we shrink log file daily after tlog backup ...will there any issue

It's a total waste of time because it regrows every day. If you want it to stay smaller, do more frequent log file backups.

--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)

joshdbguy SSCertifiable Points: 7707 More actions · Answer 4

This is a bad idea. Depending on transactional load on the server, it could grow large even after a shrink. The best option would be to do a shrink to a smaller size (you would need to figure this out), say 1GB, with a 1GB growth rate and then run a t-log backup every hour. On previous servers I worked on, my t-log was around 10 GB with a 5 GB growth rate and I would run t-log backups every 15 minutes with minimal performance hit. So the log would always stay under 10 GB. You want to make sure you minimize growths or it could cause a significant performance hit.

dastagiri16 Hall of Fame Points: 3544 More actions · Answer 5

we have a dataware house db and number of transactions going on and we have every one hour TLog but the log file of that dB growing huge size

joshdbguy SSCertifiable Points: 7707 More actions · Answer 6

Not sure what you mean by a huge amount of transactions. If it's growing a lot and your transaction log is set to say 20 GB you can either increase the amount of t-log backups or you can try changing the initial size to 30 or 40 GB with the same backup timeframe. Or you can do both.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

JoshDBGuy (12/2/2014)
Not sure what you mean by a huge amount of transactions. If it's growing a lot and your transaction log is set to say 20 GB you can either increase the amount of t-log backups or you can try changing the initial size to 30 or 40 GB with the same backup timeframe. Or you can do both.

+1

Shrinking the tlog daily is not going to help solve the problem. The tlog will just grow again.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

dastagiri16 Hall of Fame Points: 3544 More actions · Answer 8

hi ,

can I set file growth to 10GB...I have every one hour log backup is there.

my TLog comming arround 10gb

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

Set the log to the size it needs to be for regular operations, configure a sensible autogrow, then leave the file alone. Stop shrinking it and forcing it to regrow.

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

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

But, if it's a data warehouse, does it need to have point in time recovery? If not, maybe Simple would work for you.

You'll still need to set the transaction log to an appropriate size, otherwise it will just grow again. And, that growing slows down all your operations while the grow occurs. That's why everyone is saying the same thing.

"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

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 11

Simply put: The transaction log is growing because you have a lot of a combination of insert, delete, updates going on. By shrinking the log you free up space on your server, but will cause fragmentation within the .ldf file. If you have exposive growth then the log file will grow again and then you are back to the same thing.

The transaction log file needs to be sized for what work you have going on. Some ways to possibly not have it grow so large is by running Trans log backups more, unless you have one massively long delete, insert, update going on without small commits, or if you have a lot of batch updates jobs running at the same time you can possibly help out the cause by not scheduling them at the same time. Thus spreading out the activity in the transaction log.

dastagiri16 Hall of Fame Points: 3544 More actions · Answer 12

I have created a job once TLog happened then immediately my sub plan job execute with Shrink log job.

So my TLog backup is in limited size other wise the log backup size is comming more..

If any issue doing like this in future ....i tested no problem while restoring the backups..

will be there any log corruption issue?

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

Stop shrinking your log file!

You're just wasting resources, both in the shrink and when the file regrows (which it will have to do), as well as slowing your app down because the log has to grow.

Set the log file to the size it needs to be for regular activity and leave it alone.

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

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 14

Why do you feel you should shrink the transaction log every time you run a trans log backup? You are not gaining anything by doing this. If it needs to grow it will and slow down your transactions as it grows the file....