June 16, 2009 at 6:20 am
We setup 3 servers for sql 2005 database backup, primary server, backup server and witness server. We do full backup every night, after a short time we noticed that are log files became very large, we truncated to 100 mb, but it grow fast. Several databases are for support only, we only read data, but those are the biggest problem. What is the best way to handle transaction files? I created maintenance plan to backup log every hour, is this enough? Is mirroring the reason for transaction files to grow so quickly?
June 16, 2009 at 3:06 pm
ggrimberg (6/16/2009)
We setup 3 servers for sql 2005 database backup, primary server, backup server and witness server. We do full backup every night, after a short time we noticed that are log files became very large, we truncated to 100 mb, but it grow fast. Several databases are for support only, we only read data, but those are the biggest problem. What is the best way to handle transaction files? I created maintenance plan to backup log every hour, is this enough? Is mirroring the reason for transaction files to grow so quickly?
some of these statements seem contradictory so please expand on them.
You say the logs are growing fastest on databases that are read only, that does not make sense.
You truncated log files to 100mb, do you mean shrink or backup with truncate_only? I presume shrink as databases are mirrored (are they all mirrored)
sounds like you are in high availability mode so you will not be able to backup transactions until they are committed on both servers. Is mirroring up to date? Any warnings in error log, does the mirror database show correct sate of mirror, synchronised\restoring?
Did you set up log backups after you noticed the logs grew? If so hourly log backups in most cases are frequent enough to control log growth. To be sure check it prevents further growth of the logs and monitor the size of the .trn files produced to get a feel for the amount of update activity.
mirroring of itself does not cause more log growth, if there are problems with it it can prevent the log being backed up and therefore lead to excessive growth. Fast log growth is just a result of a lot of update activity in the database. Maintenance jobs like reindexing can produce large amounts of log activity.
ensure the growth factor on your transaction logs is set to a sensible MB value (about 50 - 100MB) and not a percentage or tiny MB value. Also do not shrink the transaction logs, let them grow to the size they need to be to support normal activity and leave them there.
---------------------------------------------------------------------
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply