Transaction log size issue in log shipping at the idle time

Question

Transaction log size issue in log shipping at the idle time

Rajat Jaiswal-337252

SSCommitted

Points: 1779
More actions
August 10, 2010 at 4:37 am

#236471

Hi,
I have implemented log shipping and its working fine
but the only issue which i have find that when system is idle nothing is going on database but still its create log file.
some time the size of the log file is very large
why ?
how to make it constant.
Thanks
Rajat

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

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

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 1

You cannot make transaction log file constant size. The size depends on how many transaction were done between the time you made the last transaction log. When you mean that the server was idle, no background Jobs were running? No SQL Server Agent job? No scheduled Jobs?

-Roy

Rajat Jaiswal-337252 SSCommitted Points: 1779 More actions · Answer 2

Hi Roy,

Thanks for response.

Yes job is running but that is only log shipping related nothing else.

but sometime transaction log size is very high in that case.

any specific reason for that?

thanks

Rajat

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

There must be something else running, simple as that--if nothing were happening then the transaction log backups would consistently be the same fairly small size. Something like a reindexing job that's set to run as a scheduled task would result in a lot of transaction log traffic.

katy.park Old Hand Points: 337 More actions · Answer 4

I agree with the above posters that there must be some sort of background job running. There is no way to make the log files constant. However, if you need to make them smaller for space reasons, you could schedule them more frequently. Or if there is a specific time that you are seeing the files become large, you could run the log backups more frequently at only that time.

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 5

I would suggest you to try running a trace and see if there is anything running. You do not want to be in position where something is running on the DB and you have no clue about it.

-Roy

Derrick Smith SSChampion Points: 10833 More actions · Answer 6

You also might have an open transaction on the DB. Run DBCC Opentran and see how old the oldest one is. Active transactions are not flushed out of the log during a log backup.