Simple queries intermittantly timing out.

Question

Simple queries intermittantly timing out.

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

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

Antares686 SSC Guru Points: 125444 More actions · Answer 1

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.

kangarolf Hall of Fame Points: 3432 More actions · Answer 2

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

kangarolf Hall of Fame Points: 3432 More actions · Answer 3

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

Antares686 SSC Guru Points: 125444 More actions · Answer 4

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

kangarolf Hall of Fame Points: 3432 More actions · Answer 5

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

kangarolf Hall of Fame Points: 3432 More actions · Answer 6

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