May 9, 2006 at 1:47 am
Hello,
I have a problem during the database optimization that is the part of the database maintenance plan. This part of the maintenance plan is scheduled to work once a week (Sunday morning), and after that, even if it completes successfully, the transaction log expands to an unacceptable limit.
As this server is replication publisher, and the distributor is another computer, after the transaction log growing there is an error at the log reader agents :
‘The process could not execute ‘sp_replcmds’ on ‘SERVER1’…
After executing ‘sp_repldone’ at the publisher everything is OK, but I am not sure that this is good solution (fortunately, this happens on the Monday morning, and there are no new changes in the database before executing sp_repldone).
What is the reason of transaction log growing and how can I solve it?
Kindest regards,
Bojana
May 9, 2006 at 7:40 am
Hi, Can you give us some detail of the processes that are selected in the optimization task ?
and .... how long goes the TL (number of times in comparission with the data) ?
May 9, 2006 at 8:22 am
In the Optimization tab of the database maintenace plan there is : Reorganize data and index pages, and further Change free space per page percentage 10%.
Database is backed up daily, and transation log is backed up every 15 minutes for the purposes of Log Shipping.
May 9, 2006 at 11:07 am
The maintenance plan is probably reindexing your database tables. This can cause the log file to grow 1.5 times the size of the database itself. This is normal and is one of the reasons most DBA's don't use maintenance plans. Most create jobs and schedule the maintenance themselves. That way you know what is being done and when. You can also tailor the jobs for your needs and make changes as needed.
For example: you could create separate backup jobs for user databases and system databases. Since system databases usually don't need to be backed up as often as user databases, this lets you control when they are backed up. You could then create a job that does your reindexing or index defragging and run it less frequently. Another job could shrink the database files if needed.
-SQLBill
June 12, 2006 at 5:03 pm
SQLBill seems to be hinting at a performance problem. I would have to concur, I only started seeing this particular error in replication when we migrated to new disk. I saw it on 3 out of 4 of my high transaction database servers that were in replication. TERRIBLE performance. Any query or process that ran made it substantially worse. I did make a new profile for my logreader and set the readbatchsize to 1. It got it going again, but I have to baby it everyday.
You might want to investigate any extra load being placed on your disk. We'll hopefully be going to Tier 1 disk soon.
😎
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply