October 28, 2007 at 3:50 pm
We have a database with a 2 GB transaction log that is a publisher for non-updateable transactional replication. This database resides on a SQL 2000 EE Server SP4 on Windows 2003 SP2. The former DBA here created a job that shrinks this database's transaction log every three hours. Since disk space is not at issue here, I asked him the reason this job was created. He said that for our setup, once the tran log grew over 4 GB or so, he started having problems with replication.
I've looked on the internet to see if I could find anything on this, but I haven't had any luck so far. Has anyone else experienced this?
Thanks,
Aaron
October 28, 2007 at 8:38 pm
I have customers who regularly have multi-gigabyte transaction log files and they don't experience problems. I also have not noticed any posts from people having problems.
Are you able to find out what errors the DBA was actually experiencing ?
As for regularly shrinking the log - this is generally accepted to be poor practice in a production environment. If the file is actually needing to grow, you will be stopping transactions whilst that is happening - SQL Server must be able to write to the transaction log for any update/delete/insert transaction to proceed.
If the size of the log file is not managable, then you should back it up regularly (one option is to use a database maintenance plan) which makes the part of the log that had not been backed up available to be written to by transactions. This may reduce the maximum size of the log file.
You should also be aware that, transactions that are need for replication will remain in the log file until the logreader agent runs. Normally, this runs all of the time but you can change this. It might be worth checking what schedule the logreader agent uses.
October 29, 2007 at 5:13 am
I never understood his reasonings either, and I know that continually shrinking the log is not something I want to do unless there's a very good reason for it.
I know that there are many ways to adjust replication settings, and I can experiment with those. In your replication experience (or anyone else's), what are some of the more helpful/useful configuration settings?
October 29, 2007 at 1:48 pm
The only thing I had to change on replication was the batch-size used by the agents because of a slow subscriber.
October 29, 2007 at 2:09 pm
Aaron Sentell (10/28/2007)
We have a database with a 2 GB transaction log that is a publisher for non-updateable transactional replication. This database resides on a SQL 2000 EE Server SP4 on Windows 2003 SP2. The former DBA here created a job that shrinks this database's transaction log every three hours. Since disk space is not at issue here, I asked him the reason this job was created. He said that for our setup, once the tran log grew over 4 GB or so, he started having problems with replication.I've looked on the internet to see if I could find anything on this, but I haven't had any luck so far. Has anyone else experienced this?
Thanks,
Aaron
I've seen this sort of thing before.
In general, you have a particular process that affects the size of the transaction log. This process fails, and as a result, the transaction log grows. An analysis of the problem leads someone to conclude that the growth of the transaction log is the cause of the problem, when in fact it is the other way around.
Indeed, if the logreader stops working, the transaction log will want to grow, because these unread transactions have to stay in the log until the logreader can read them.
So, I would guess that the initial analysis of the problem was possibly incorrect. Given what you describe, I would start there.
In my transactional replication, I have a publisher with a 17GB log file, and I have no problems. My log file is that big because once a month, certain maintenance procedures create a large amount of logging. If I shrink it, it's just going to grow again so I leave it alone.
October 29, 2007 at 7:17 pm
I'm confused. Are you saying a particular process could fail and in turn cause the transaction log to grow? Or are you talking about a process that fails and causes the LogReader agent to stop working, thereby causing the transaction log to grow? Either way, I don't buy that the size of the transaction log has anything to do with our supposed replication problems.
October 29, 2007 at 7:36 pm
I think Jeff is saying that if the logreader agent fails, the transaction log will grow (which is true because transactions must remain in the trans log until they have been processed by the logreader).
Aaron, you need to get information on about the actual error you are experiencing. Everyone is guessing because the only information you have provided is that the DBA started to have problems with replication when the transaction log got to be more than 4GB. What error message or symptoms was he actually experiencing ?
October 30, 2007 at 6:25 am
Hi Aaron,
Sorry about the confusion. I was generalizing perhaps a bit too much. You are correct in your belief that the size of the transaction log should not create problems for transactional replication.
I would turn off the job that shrinks the log.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply