Post reply

Simple queries intermittantly timing out.

  • November 11, 2002 at 12:45 pm

    #440994

    I would think the distributor. I would completely drop the publication and start again to see if clears. Also, I would run DBCC CHECKDB against the distribution database whereever you have it. If still continues, consider stopping all publishing and dropping the distributor, then start from scratch.

  • November 11, 2002 at 12:55 pm

    #440998

    Have dropped the publication/subscription and recreated them with the same effect.

    I have removed all the subscriptions and none of the tables etc are enabled for distribution but when I run sp_dropdistributor I get the error;

    Could not drop. This Distributor has associated distribution databases.

    Also installing another instance at the minute to try it out with different tables etc..

    Have to try and get this working over night so its ready in the morning...arghhh

  • November 11, 2002 at 1:02 pm

    #441000

    I ran the DBCC checks no errors. Have dropped the publication/subscription and recreated same error. If I try to run sp_dropdistributor I get an error telling me;

    could not drop. This Distributor has associated distribution databases.

    I noticed whilst trying to track the commands the log reader was passing down that it was deleting the whole table row by row and then inserting the same information. That cant be right!

    Rolf

  • November 11, 2002 at 3:27 pm

    #441019

    For transactional replication it should not. Only, snapshot does that. Also, are there supposed to be any other publications?

  • November 11, 2002 at 4:32 pm

    #441057

    At last I think I may have worked out what was going wrong.

    Somewhere along the powercut restore line an old test trigger had been reapplied to one of the replicated tables. This updating trigger seems to be the little bugger causing all the problems.

    I only found it because I was trying to drop the table in EM and it was bombing, so I went into QA and used the DROP TABLE command which also failed, then i checked for dependancies and that revealed the trigger. Couldnt drop either the table or the trigger in EM but the drop trigger and drop table command got rid of the them. Rebuilt the table without the trigger and it now seems to be going ok again and managed to replicate half the table after a quick test with no problems.

    Thanks for all your help...going to bed now!

    Rolf

  • November 12, 2002 at 3:58 am

    #441092

    Another quick update.

    Although removing the rogue trigger helped prevent the timeouts, the replication agents still weren't happy. Eventually they just gave up and reported they werent registered properly. So back to SQL installation and a quick registry rebuild (after a scary moment when I thought I had forgotten the passwords that the agents use on the domain) and everything was fine again.

    Cant believe how long it took me to track down the causes for this problem!

    Thanks for all the help

    Rolf

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply