July 28, 2011 at 9:53 am
opc.three (7/27/2011)
David Benoit (7/12/2011)
Great. I will follow-up later with a bit on subscriptions and the impact of removing them.Let me know if you have any problems with the article drop. You shouldn't. I have had to remove MANY using both sp_droparticle and the GUI and it works every time.
More to follow.....
Unfortunately issuing a call to sys.sp_droparticle failed with this error:
Could not drop article. A subscription exists on it.
So, I dropped the article from the publication using the GUI :sick: and it worked fine.
I [assume] that the GUI issued a call to sys.sp_dropsubscription in the background, before dropping the article, but I did not run a trace so cannot confirm it.
I am going to setup a test environment so I can get to the bottom of this but I wanted to post back the state of affairs.
Thanks for the follow-up and sorry for the bad information. I didn't notice that you were specifying the article in the sp_dropsubscription which is why I was so firm against dropping the subscription.
I will say that I typically use the GUI when dropping replication pieces mainly due to the fact that in many cases it needs to connect to the subscriber in order to do metadata clean-up there. So, that should be considered, even if not applicable to this particular situation.
Again, I apologize for any confusion that my responses brought.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
July 28, 2011 at 10:03 am
David Benoit (7/28/2011)
opc.three (7/27/2011)
David Benoit (7/12/2011)
Great. I will follow-up later with a bit on subscriptions and the impact of removing them.Let me know if you have any problems with the article drop. You shouldn't. I have had to remove MANY using both sp_droparticle and the GUI and it works every time.
More to follow.....
Unfortunately issuing a call to sys.sp_droparticle failed with this error:
Could not drop article. A subscription exists on it.
So, I dropped the article from the publication using the GUI :sick: and it worked fine.
I [assume] that the GUI issued a call to sys.sp_dropsubscription in the background, before dropping the article, but I did not run a trace so cannot confirm it.
I am going to setup a test environment so I can get to the bottom of this but I wanted to post back the state of affairs.
Thanks for the follow-up and sorry for the bad information. I didn't notice that you were specifying the article in the sp_dropsubscription which is why I was so firm against dropping the subscription.
I will say that I typically use the GUI when dropping replication pieces mainly due to the fact that in many cases it needs to connect to the subscriber in order to do metadata clean-up there. So, that should be considered, even if not applicable to this particular situation.
Again, I apologize for any confusion that my responses brought.
No worries David. No harm, no foul. It happens. I appreciate your time and the information you provided.
This is starting to become a bit more clear. Your comment about metadata cleanup on the subscriber is mentioned here: How to: Delete a Push Subscription (Replication Transact-SQL Programming) Item 2 under To delete a push subscription to a snapshot or transactional publication. I am guessing the GUI also does this for us on our behalf, how nice 🙂
I have added a comment to MysteryJimbo's script which I saved to make sure I run sp_subscription_cleanup on the subscriber after dropping the subscription on the publisher.
"Invalidating the snapshot" is the only piece I am still a bit fuzzy on. I am curious as to what that means when managing the publication going forward, adding new subscribers, etc.
Thanks for everyone's comment on this!
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 28, 2011 at 10:07 am
opc.three (7/28/2011)[hrI always welcome a T-SQL approach first. I just added this to the toolkit. Thanks for sharing this!
Always the best approach! Never more so than with replication
July 28, 2011 at 11:25 am
opc.three (7/28/2011)
"Invalidating the snapshot" is the only piece I am still a bit fuzzy on. I am curious as to what that means when managing the publication going forward, adding new subscribers, etc.
There is a good bit that goes into this and I only know a few pieces. For instance I know that when you invalidate a snapshot while adding an article to an existing publication WITH active subscriptions then it will only generate a snapshot for the new article, not all, in the publication to apply at existing subscribers. I have recently tested this to confirm this is how replication works in this situation.
I am fairly certain that when you add a new subscription to a publication that has had the snapshot invalidated it will still create a full snapshot for that subscription, assuming that you specify to initialize it with a snapshot. I would need to test this to confirm though.
Not sure that this helps but hopefully it will fill in a few of the missing pieces.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
July 28, 2011 at 7:44 pm
opc.three (7/28/2011)
MysteryJimbo (7/28/2011)
I generally use this to generate the TSQL.
select 'exec sp_dropsubscription @publication = N''' + sp.name + '''' + ', @article = N''' + sa.name + '''' + ', @subscriber = N''' + ss.srvname + '''' + char(13) +'GO'
from syssubscriptions ss
inner join sysarticles sa on sa.artid = ss.artid
inner join syspublications sp on sp.pubid = sa.pubid
where sa.name in ('')
select distinct 'exec sp_droparticle @publication = N''' + sp.name + '''' + ', @article = N''' + sa.name + '''' + ', @force_invalidate_snapshot = 1' + char(10) + 'GO' + char(10)
from syssubscriptions ss
inner join sysarticles sa on sa.artid = ss.artid
inner join syspublications sp on sp.pubid = sa.pubid
where sa.name in ('')
I always welcome a T-SQL approach first. I just added this to the toolkit. Thanks for sharing this!
The distribution database has the MSsubscriptions, MSarticles and MSpublications tables that holds the replication information on a server-wide level.
July 29, 2011 at 1:10 am
The warning about the snapshot being invalidated is to do with the current snapshot (if still available - it can expire and be deleted too).
The current snapshot before dropping an article still has this article in it, so when/if that snapshot gets used the dropped article would be included. It would not break anything per se, but would generate an object on the subscriber that never got updates/inserts/deletes.
It is obviously a little annoying that the current snapshot is invalidated, but it makes no sense to have a snapshot with articles in it that are "invalid".
Just so you know, you can help yourself out in terms of snapshot creation if you want to add articles to a running publication in the future. You need to make sure that the publication has the property "immediate_sync" is set to 0. This will make sure that when you add an article to the publication and run the snapshot agent, only the new article will be put into the snapshot and not everything in the publication. A full explanation of the process can be found here: http://replicationanswers.com/TransactionalOptimisation.asp
Regards,
WilliamD
August 22, 2011 at 8:39 am
Hello All,
I know this post about removing a article from publication. I tried opposite of this as this is a requirement at my work.
Adding a article to the existing publication. But this doesnot take any snapshot (don't copy records from pubs to subs). The table is relatively very small so I push the records manually.
Here is the script on how I added a article to the publication.
So far I didn't run into any issues. Is this the correct approach to add an article. Also, can we do the same to drop the article?
Another question is how to add the article with BCP?
--Added article to the existing publication with NO BCP
EXEC sp_changepublication
@publication = 'PubName1',
@property = N'allow_anonymous',
@value = 'false'
GO
EXEC sp_changepublication
@publication = 'PubName1',
@property = N'immediate_sync',
@value = 'false'
GO
EXEC sp_addarticle @publication = 'PubName1'
, @article = 'Customers'
, @source_table = 'Customers'
DECLARE @filtername AS nvarchar(386)
SET @filtername = N'filter_out_discontinued'
-- Adding the article filter
exec sp_articlefilter @publication = N'PubName1',
@article = N'Customers', @filter_clause = N'Location IN (0,1)',
@filter_name = @filtername;
-- Add all columns to the article.
EXEC sp_articlecolumn
@publication = N'PubName1',
@article = N'Customers'
exec sp_articleview @publication = N'PubName1',
@article = N'Customers', @filter_clause = N'Location IN (0,1)'
exec sp_addsubscription @publication = N'PubName1', @subscriber = N'SubscriberName1',
@destination_db = N'DatabaseName', @subscription_type = N'Push', @sync_type = N'replication support only',
@article = N'all', @update_mode = N'read only', @subscriber_type = 0
exec sp_addpushsubscription_agent @publication = N'PubName1', @subscriber = N'SubscriberName1',
@subscriber_db = N'DatabaseName', @job_login = null, @job_password = null, @subscriber_security_mode = 0,
@subscriber_login = N'XXXXX', @subscriber_password = 'XXXXX', @frequency_type = 64,
@frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0,
@frequency_subday = 4, @frequency_subday_interval = 5, @active_start_time_of_day = 0, @active_end_time_of_day = 235959,
@active_start_date = 0, @active_end_date = 0, @dts_package_location = N'Distributor'
GO
--set to publication options to true
EXEC sp_changepublication
@publication = 'PubName1',
@property = N'immediate_sync',
@value = 'true'
GO
EXEC sp_changepublication
@publication = 'PubName1',
@property = N'allow_anonymous',
@value = 'true'
August 24, 2011 at 7:42 am
amita pallod (8/22/2011)
Hello All,I know this post about removing a article from publication. I tried opposite of this as this is a requirement at my work.
Adding a article to the existing publication. But this doesnot take any snapshot (don't copy records from pubs to subs). The table is relatively very small so I push the records manually.
Here is the script on how I added a article to the publication.
So far I didn't run into any issues. Is this the correct approach to add an article. Also, can we do the same to drop the article?
Very long winded. You only need 3 statements to add an article followed by a snapshot (you can do it in two). If you changed the immeadiate sync to get away with the snapshot there is still an overhead and table locking with bcp.
sp_addarticle
sp_addsubscription
[sp_addsubscriptionagent]
The reverse doesnt require a snapshot or any downtime
sp_dropsubscription ....... @article='tablename'
sp_droparticle
August 24, 2011 at 9:14 am
Thanks.
I never used the script in production but did try in development by turning OFF the immeadiate sync.
It worked without any issues.
How would I use filter on article if 3 commands below? Why is the 3rd one in brackets?
sp_addarticle
sp_addsubscription
[sp_addsubscriptionagent]
August 25, 2011 at 5:55 am
amita pallod (8/24/2011)
Thanks.I never used the script in production but did try in development by turning OFF the immeadiate sync.
It worked without any issues.
How would I use filter on article if 3 commands below? Why is the 3rd one in brackets?
sp_addarticle
sp_addsubscription
[sp_addsubscriptionagent]
Apologies, I forgot the filter. 3 or 4 proc calls then. 😀
The 3rd statement is in square brackets as it is optional. Most of the functionality is in sp_addsubscription and this will also create an agent job for you. sp_addsubscriptionagent adds additional configurability.
August 25, 2011 at 9:07 am
Thank you.
This morning I encountered another issue on all the publicatios which I had built yesterday with the 'replication support only' subscriptions as I didn't want to generate new snapshot. I then manually added the records.
But now I am getting error on all the publications at the Log Reader Agent.
The process could not execute 'sp_replcmds' on 'Server1'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)
Get help: http://help/MSSQL_REPL20011
Execution of filter stored procedure 3421344551 failed. See the SQL Server errorlog for more information. (Source: MSSQLServer, Error number: 18764)
Get help: http://help/18764
The Log-Scan Process failed to construct a replicated command from log sequence number (LSN) {00018413:00002c60:000b}. Back up the publication database and contact Customer Support Services. (Source: MSSQLServer, Error number: 18805)
Get help: http://help/18805
The process could not execute 'sp_replcmds' on 'Server1'. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)
Is there an easy fix for this without loosing the pending transaction and re creating the pubs and subs.
Please let me know.
August 25, 2011 at 9:59 am
Based on your error it looks like the LSN's for the transactions are out of sync. I don't know of a way to fix that without losing the transactions that are already in the distribution database. If the table is not too big you could do a manual sync afterward. You should be able to fix it though without re-initializing.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
August 25, 2011 at 10:02 am
Do you know what transaction caused the error and therefore the table effected?
You can see if you can alter the replication fltr procedure to work. You can find it in the database under system stored procedures.
August 25, 2011 at 10:27 am
Thanks.
Should I create all the pubs and subs? Or just the one having an issue and that will fix all the pubs.
How do I find which publication had an issue? I get the Filterid from the error. Is that some kind of hint?
August 25, 2011 at 11:16 am
Can you see the failed subscription in replication monitor?
There are a couple of ways to fix it - the easiest being to drop the subscription, recreate it WITHOUT initializing. That should solve the issue.
All options that I can think of require that you run a manual data sync.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 15 posts - 16 through 30 (of 47 total)
You must be logged in to reply to this topic. Login to reply