Distribution Hangs on | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Distribution Hangs on

Hi, I have two SQL 2000 server. The first one is used by the production and the second one is used for reporting. There is a transactional replication between the servers.
The 2nd server is responsible for the distribution and contains the distribution db. On every saturday evening maintenance jobs are running on the first server and delete data from the database.
On saturday after this deletion the distribution agent frozen (or something like that) and nothing delivered from the distribution db to the subscriber database. The agent sad "Delivering replicated transactions" but nothing happened. The Logreader agent worked fine. When I restarted the agent nothing happened just the same message. The solution was to rebuild the replication between the two servers. I’m sure that the logreader agent worked fine and the subscription did not expired.
The application log or the agent history did not contains any error or warning message. Do you know what could be the cause of thisproblem? thanks in advance
Zsolt Zsolt
First of all confirm level of service packs on the machines. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

sql sp3 (on Windows 2000 Sp4) is installed on each server. I read about the sp3a but as Microsoft sad it is not important to apply this sp to sql sp3.
Zsolt
Any information from log reader agent, sql agent logs? _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

The logreader worked fine. I saw that the distribution database growing. The status of the agent was running and the last action sad
"15 transactions with 103 commands delivered" so it look like the logreader worked.
There was no error message in the logreader agent histrory. The distribution agent status was "running". Last action was "delivering replicated transacions".
there was 1 "timeout expired" entry in the agent histroy but nothing else it is very strange for me, the subscriber is set to "never expires"
I know that the large mount of data was deleted during the maintenance but it should not be the reason
Each server is a dual P4 2,8 GH2 with 4 GB RAM. Zsolt
Probably I guess on mass insertion in a single transaction. This means which may deliberatly specifying the message of …replicated transactions from log reader agent. And I think publication must be unsubscribed and resubscribed before replication will continue. Seen similar behaviour in SQL 7 version but which was corrected with SP3 SQL 7.
_________
Satya SKJ
Moderator
SQL-Server-Performance.Com

yes it was the solution. <br />but these jobs are running on every weekend. I don’t want to come in on every sunday morning an re-create the replication <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /><br /><br />I checked the duration of the jobs and they did not take more time like one week before<br /><br />whn we used SQL7 for the same task we have to stop and restart the distibution agent in every 8 hour (during the maintenace jobs in every 30 minutes) because it caused a memory leak on the 2nd server<br /><br />do you have any idea how can I monitor the process?<br /><br /><br />Zsolt
May use PROFILER during this job session. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

Hi ya,<br /><br />The timeout setting is the query timeout setting. By default this is set to 300 seconds. It can be changed by going to the agent properties for the distribution task and adding -QueryTimeOut 1200 to the end of the parameters (this will change the query timeout to 1200 seconds. Choose a number that is appropriate for your database <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /> )<br /><br />Cheers<br />Twan
Hi, Just for your information… I have created stored procedures on the publisher that delete the unecessary data from the db. The stored procedures are replicated and when it is executed only the call is replicated and the distribution don’t hangs up. Zsolt
]]>