July 17, 2002 at 11:46 am
Since the upgrade of the the data center Db Server to SQL Server 2000, we have been experiencing an increasing number of replication errors. What appears to be happening is that the Distribution Agent at the sites times out when attempting the connection to the data center. After the several attempts to connect one of two things occurs, 1 - the number of retry attempts reaches the set value (after 1 to several hours), or 2 - SQL Server runs out of locks and fails. Also, we have begun to see errors where the stored procedures associated with replication are failing to execute (e.g. sp_MSadd_Repl_Commands27hp), although the timeouts are currently the major issue we are facing.
To try to reduce the number of time outs, we currently have disabled, or not restarted, the replication for many of the Raw Databases. I'm not sure if this is really helping the situation. Also, the timeouts although they sometimes occur when other activities are taking place (e.g. backups at Data Center), appear to occur randomly. For example one site was running fine this morning, then at around 11:30, the replication began to time out.
What could be causing these problems. Please help.
Christine
Aurora
July 17, 2002 at 2:48 pm
Blocking on the subscriber? Heavily indexed and timing out because of it? Might try adjusting your batch size...after you research further. Lots of changes up front make it even harder to figure out what is causing the problem. Might also check the fragmentation of your subscriber db's.
Andy
July 17, 2002 at 2:48 pm
One other thing, have you updated all your stats since upgrading?
Andy
July 17, 2002 at 3:26 pm
Thanks Andy, actually we have run sp_updatestats on all dB's on both the publishers and the subcriber. Where would I check the batch size that's currently running? (I am not the original DBA who set up the 7.0 dB's, but being the DBA I have now inherited all db servers).
By the way we are running 7.0 with SP2.
It is difficult to administer our remote servers as they are in various parts of the country. They are not a part of a WAN. We are researching a more efficient and appropriate way to administer our remote db servers. Any suggestions would be greatly appreciated.
Aurora
July 17, 2002 at 3:28 pm
Also found the size was off when we tested a DB upgrade from 7 to 2000 in the way of indexes. They got larger on one of our test and starting acting odd. We tried the standard stuff and ultimately dropped and recreated the indexes which fixed. If all else fails try this as a last ditch.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
July 17, 2002 at 5:26 pm
Batch size is a param of the distribution agent. It's a bandaid fix unless you have a really slow link. Im doing ok with defaults over a 256k line. Have you checked for network issues, bad NIC, lots of collisions, that kind of thing?
I use VPN over internet for remote admin from home, works fine on a cable modem.
Andy
July 19, 2002 at 6:39 am
Thanks gentlemen, I think given my circumstances I will check the indexes first. Occassional I will get error 1204 and I am not really sure what's causing it? I'll let you know how it all worked out.
Christine
Aurora
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply