July 23, 2003 at 7:48 am
I have a snapshot & a transcation publication on Server A, and a pull subscription for each on Server B.
The distribution agent on ServerA has the error:
"The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated."
Any idea what caused this ..... Everything was fine for a week or so.
Does anyone know of any good books or publications on Replication ? I've got some DBA books that cover the basics, but I'd like something that helps explain the oddball situations.
SQL 2K
July 23, 2003 at 4:06 pm
No good books that I've seen. Usually happens because pending transactions have been picked up and applied. Query msrepl_command to see what is open, or sp_browsereplcmds (I think).
Andy
July 24, 2003 at 9:03 am
According to my experience. It happens when no transactions are applied for a while in a particular publication. So Server marks them as 'inactive' since it sees no activity. To set the time it takes before SQL Server marks the publication as inactive right the publication on SQLServer 7.0 EM and select the tab 'general'. There a 'Expiration time' textbox where you set the time according to your needs.
The way I solve the problem is dropping and recreating the subscription to the article marked as inactive, *not to all* the articles in the publication.
The script I use to find the article marked as inactive:
USE DISTRIBUTION
print '*** Publicaciones ***'
select pubid, name from prod_central..syspublications where pubid in
(select pubid from prod_central..sysarticles where artid in
(Select article_id from mssubscriptions where status = 0))
print '*** Articulos ***'
select artid, pubid, dest_table, name from prod_central..sysarticles where artid in
(Select article_id from mssubscriptions where status = 0)
print '*** Subscripciones ***'
Select * from mssubscriptions where status = 0
order by publication_id
print '*** Servidores Subscritos ***'
select * from master..sysservers where srvid in
(Select subscriber_id from mssubscriptions where status = 0)
Hope it helps.
Robin
July 24, 2003 at 10:20 am
Another solution we have used is to force one transation to be replicated each day in order to prevent the subscription from being inactivated.
Once a day, we insert a dummy row into the source table with a unique key like "dmy20030724". Adding the date as part of the key will prevent duplicate key errors if replication were to fail etc.
Any rows with a key of "dmy%" are then deleted out of the destination table.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply