January 21, 2010 at 3:20 am
I am hoping that someone can give me some pointers as to how to get out of the sticky situation I am about to describe. I suspect I might need to call MS directly but you never know!
Setup:
An instance of SQL 2005 SP3 Transaction replication to another instance SQL 2005 SP2. Separate machines, with the subscriber acting as distributer.
This has been running for several years, now. Not doing anything special with replication, just using the wizard / gui tools to create and drop the publication every now and again.
What happened this time:
I usually drop the publication and recreate to enable upgrades on the publication database. Yesterday I started to do this as normal, here is what happened.
1. Deleted subscription via SSMS, usually takes a few minutes, this time a few seconds
2. Went to publisher to drop publication - Error 14005 [SQL Server] Unable to drop publication. A subscription exists on it.
3. SSMS didn't show a subscription under replication - subscriptions on the subscribing server.
4. Now started getting error messages from a replication agent, one a minute or so:
Cannot insert the value NULL into column 'snapshot_seqno_flag', table 'distribution.dbo.MSsubscriptions'; column does not allow nulls. UPDATE fails
Same error message was reported in Replication Monitor, I forget which agent was reporting it (I can probably find out if this is relevant)
5. As per 'How to manually remove a replication in SQL Server 2000 or in SQL Server 2005', I then executed this @ publisher
EXEC sp_dropsubscription @publication = N'<Publication name>', @article = N'all', @subscriber = N'all', @destination_db = N'all'
EXEC sp_droppublication @publication = N'<Publication name>'
USE master
GO
exec sp_replicationdboption @dbname = N'<Publication database name>', @optname = N'publish', @value = N'false'
GO
At this point neither the publisher or the subscriber were showing the pub/sub in SSMS- replication.
However, Replication Monitor no longer works. When starting it it briefly shows my publication in an error condition, in the pane on the right, after that my publisher, and two others that share the distributer are shown with a red x. The other two publications have continued to function fine throughout this episode. One of the publications that is still working, is between the same two servers. This leads me to conclude, there is something wrong with just one particular replication db, rather than the whole replication infrastructure.
6.
This morning I needed to alter an sp in the publication DB, it would not let me drop and recreate, saying that the sp was an article in
a publication. It would let me alter it though, which I did.
7.
I then executed sp_removedbreplication on the publisher. At this point 4 client PCs errored with
The definition of object 'a stored proc that has not changed for several years' has changed since it was compiled
I altered the proc (just some white space) via SSMS and then clients started erroring on another sp with the same error message (the next logical
sp for client to run; several run in sequence, ie I don't think the problem was isolated to one or two sps)
8.
As we have quite a few sps and I didn't want to manually recompile them all
I was forced to execute DBCC FREEPROCCACHE to force recompilation of them all. Since then the 'changed since compiled' error message has not re-occurred and the DB is functioning normally (as far as normal client activity is concerned)
However, I am a bit nervous about whats going to happen next!! I need to get replication up and running as it was before, but I am convinced that residual state is going to prevent this and don't want to do anything until everything is nice and tidy again.
Help!
January 21, 2010 at 8:54 am
go to publisher and execute
exec sp_removedbreplication @dbname = 'dbname'
go to subscriber and execute
exec sp_removedbreplication @dbname = 'dbname'
Recreate your replication from the wizards as usual.
--good luck
* Noel
January 22, 2010 at 1:41 am
Thanks for that!
I have just tried this. Replication Monitor is still broken but at least the procs don't need recompiling again...
January 22, 2010 at 2:36 am
Ok, further investigation has resulted in a partial fix which I'm going to write up here so if anyone else experiences this they can try what I have done.
My symptoms have now settled down to replication monitor not working for any publication that shares a distributer with my corrupted publication. I get a list of all my publishers with a red x similar to what this guy has posted a screenie of.
When I look in the distribution DB I found lots of records relating to the supposedly deleted publication + subscription. This Db does not appear to have referential integrity so I wasn't man enough to start deleting rows randomly from it, I suspect this would void my warranty... These tables at least definitely had rows relating to the dropped pub/sub:
[dbo].[MSarticles]
[dbo].[MSdistribution_agents]
[dbo].[MSpublications]
[dbo].[MSpublicationthresholds]
[dbo].[MSsnapshot_agents]
[dbo].[MSpublisher_databases]
Instead what I did was recreated a publication on the original publication database with the original publication name. I only included one article of a small reference data table so this was very quick. The wizard completed successfully, and after this replication monitor started functioning again with green ticks across the board.
I then dropped the pub, and everything still appears to work. The distribution DB still contains the orphaned pub/sub data. I am going to schedule a full snapshot + subscription tonight, hopefully this will restore full functionality.
January 26, 2010 at 5:24 am
Recreating the full publication + subscription has restored full functionality. 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply