January 28, 2015 at 2:36 am
Do you want me to try it again via the GUI and see what the SPID is doing? What is best way to troubleshoot this?
Steve.
January 28, 2015 at 5:23 am
check your running processes by selecting from sysprocesses or run sp_who2, etc
To clarify, have you already run this and it is still going? or is this more of a curiosity question? (Sorry if that wasn't clear)
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 28, 2015 at 5:29 am
Hi,
Tried to run it yesterday and it ran for five minutes - causing timeouts etc... before I killed it..
January 28, 2015 at 5:31 am
Also since you're using the GUI, on your distribution server what is the result of running the query below?SELECT publisher_id, publication_id,
publisher_db,
publication, allow_anonymous, immediate_sync
FROM distribution.dbo.MSpublications WHERE publication_type = 0 --> 0 for transactional, 1 for snapshotIf you see ones for the immediate_sync option, then as soon as you add the article, it will generate a snapshot (which will take longer to complete depending on the size of the table)
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 28, 2015 at 5:35 am
This is the results, the bottom one is the publication in question.
So I guess it is trying to generate a complete snapshot straight away? If I disable this option then will it just generate a snapshot with the new articles in it?
publisher_idpublication_idpublisher_dbpublicationallow_anonymousimmediate_sync
04Boohoo Live2BoohooLive2-Corrected00
05Boohoo Live2BoohooLive2-November201411
January 28, 2015 at 5:36 am
steve.roberts 86619 (1/28/2015)
Hi,Tried to run it yesterday and it ran for five minutes - causing timeouts etc... before I killed it..
Sounds like the result of the query I just added will show 1's for the immediate_sync (which means generate a full snapshot now for the publication) - you must set this to zero USE YOURDB
GO
EXEC sp_changepublication @publication = N'YOURPUBLICATIONNAME', @property = N'allow_anonymous', @value = 'false'
GO
EXEC sp_changepublication @publication = 'YOURPUBLICATIONNAME', @property = N'immediate_sync', @value = 'false'
GO BEFORE running adding the article.
There's no harm in running the code above (it will only help)
It is possible the snapshot agent will pickup where it left off...so there may be some cleanup needed (unsure of this)
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 28, 2015 at 5:40 am
steve.roberts 86619 (1/28/2015)
This is the results, the bottom one is the publication in question.So I guess it is trying to generate a complete snapshot straight away? If I disable this option then will it just generate a snapshot with the new articles in it?
publisher_idpublication_idpublisher_dbpublicationallow_anonymousimmediate_sync
04Boohoo Live2BoohooLive2-Corrected00
05Boohoo Live2BoohooLive2-November201411
Correct (which is what I thought). If you use the code I provided to set the immediate_sync to zero, the snapshot will not get created right away (which inadvertently will create a snapshot for EVERY article in your publication.
Setting this to 0 stops this from happening.
Once you make this change, you can add articles as you want, with no ill-effect.
Then when you want to create the snapshot, you get use the GUI (in the replication monitor), select your publication, go to the "agents" tab, right-click on the "Snapshot agent" and click "Start Agent" - this will then create ONLY THE SNAPSHOT for the article you added, and not the entire publication
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 28, 2015 at 5:46 am
How long should running these commands take? It has been running for three minutes now? Should it be instant?
Steve.
January 28, 2015 at 5:50 am
It's ok it has finished now.
So adding an article now should be fast via the GUI? Am I best adding via T-SQL?
January 28, 2015 at 6:14 am
adding the article should be quick, providing the immediate_sync and anonymous setting are set to 0 (using the script provided)
You can then start the snapshot agent at an appropriate time to generate the actual snapshot data (whereby it will write to the file system location you specified, then copy it from there to your subscriber, etc). the snapshot agent will only generate for the articles you added
I would recommend you wait for a schedule maintenance window to test if you don't have a DEV environment to test it in
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 28, 2015 at 6:43 am
OK I can run the schedule agent in the day - will it definitely just snapshot the new articles or will it attempt to finish off any snapshots that might of been aborted before I changed the sync option?
January 28, 2015 at 8:18 am
Hi,
I think it has now broken replication... I haven't tried to run a new snapshot...
Date27/12/2014 21:29:42
LogJob History (BHSRVDB007-Boohoo Live2-BoohooLive2-November2-UKMANPSRVRPT01-17)
Step ID2
ServerBHSRVDB007
Job NameBHSRVDB007-Boohoo Live2-BoohooLive2-November2-UKMANPSRVRPT01-17
Step NameRun agent.
Duration31.17:44:23
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
2015-01-28 15:11:44.038 Copyright (c) 2008 Microsoft Corporation
2015-01-28 15:11:44.038 Microsoft SQL Server Replication Agent: distrib
2015-01-28 15:11:44.038
2015-01-28 15:11:44.038 The timestamps prepended to the output lines are expressed in terms of UTC time.
2015-01-28 15:11:44.038 User-specified agent parameter values:
-Subscriber UKMANPSRVRPT01
-SubscriberDB Boohoo Live2
-Publisher BHSRVDB007
-Distributor BHSRVDB007
-DistributorSecurityMode 1
-Publication BoohooLive2-November2014
-PublisherDB Boohoo Live2
-Continuous
-XJOBID 0x86F079251D10EC4E9AA47FB649A58CBB
-XJOBNAME BHSRVDB007-Boohoo Live2-BoohooLive2-November2-UKMANPSRVRPT01-17
-XSTEPID 2
-XSUBSYSTEM Distribution
-XSERVER BHSRVDB007
-XCMDLINE 0
-XCancelEventHandle 0000000000000AC0
-XParentProcessHandle 0000000000000C88
2015-01-28 15:11:44.054 Startup Delay: 2774 (msecs)
2015-01-28 15:11:46.831 Connecting to Distributor 'BHSRVDB007'
2015-01-28 15:11:46.878 Parameter values obtained from agent profile:
-bcpbatchsize 2147473647
-commitbatchsize 100
-commitbatchthreshold 1000
-historyverboselevel 1
-keepalivemessageinterval 300
-logintimeout 15
-maxbcpthreads 1
-maxdeliveredtransactions 0
-pollinginterval 5000
-querytimeout 1800
-skiperrors
-transactionsperhistory 100
2015-01-28 15:11:46.878 Connecting to Subscriber 'UKMANPSRVRPT01'
2015-01-28 15:11:47.205 Initializing
2015-01-28 15:11:47.408
42000 The row was not found at the Subscriber when applying the replicated command. 20598
2015-01-28 15:11:56.768 Disconnecting from Subscriber 'UKMANPSRVRPT01'
2015-01-28 15:11:56.768 Connecting to Subscriber 'UKMANPSRVRPT01'
2015-01-28 15:11:56.784 Error executing a batch of commands. Retrying individual commands.
2015-01-28 15:12:10.575 Disconnecting from Subscriber 'UKMANPSRVRPT01'
2015-01-28 15:12:10.575 Connecting to Subscriber 'UKMANPSRVRPT01'
2015-01-28 15:12:10.637 Error executing a batch of commands. Retrying individual commands.
2015-01-28 15:12:29.545 Disconnecting from Subscriber 'UKMANPSRVRPT01'
2015-01-28 15:12:29.545 Connecting to Subscriber 'UKMANPSRVRPT01'
2015-01-28 15:12:29.545 Error executing a batch of commands. Retrying individual commands.
2015-01-28 15:12:53.507 Disconnecting from Subscriber 'UKMANPSRVRPT01'
2015-01-28 15:12:53.507 Connecting to Subscriber 'UKMANPSRVRPT01'
2015-01-28 15:12:53.523 Error executing a batch of commands. Retrying individual commands.
2015-01-28 15:13:22.555 Disconnecting from Subscriber 'UKMANPSRVRPT01'
2015-01-28 15:13:22.555 Connecting to Subscriber 'UKMANPSRVRPT01'
2015-01-28 15:13:22.571 Error executing a batch of commands. Retrying individual commands.
2015-01-28 15:13:56.518 Disconnecting from Subscriber 'UKMANPSRVRPT01'
2015-01-28 15:13:56.518 Connecting to Subscriber 'UKMANPSRVRPT01'
2015-01-28 15:13:56.533 Error executing a batch of commands. Retrying individual commands.
2015-01-28 15:14:05.831 Agent message code 20598. The row was not found at the Subscriber when applying the replicated command.
2015-01-28 15:14:05.909 Category:COMMAND
Source: Failed Command
Number:
Message: if @@trancount > 0 rollback tran
2015-01-28 15:14:05.925 Category:NULL
Source: Microsoft SQL Server Native Client 10.0
Number: 20598
Message: The row was not found at the Subscriber when applying the replicated command.
January 28, 2015 at 10:20 am
Also getting this
Cannot insert explicit value for identity column in table 'KsPOPOrder' when IDENTITY_INSERT is set to OFF
this is the new article I added in.
January 28, 2015 at 11:52 am
I'm pretty confident it" did not break anything, but your previous snapshot that you created (yesterday) might not have completed yet so it has some lingering actions that it's trying to perform/complete.
Drop the articles that you are getting the errors for, remove any commands for those articles in your distribution database, ensure that everything runs fine without the articles you just removed. Re-add the articles to the publication, start the snapshot agent
Out of curiosity, in the article properties what do you have set for the "Action if name is in use" and "Copy clustered index" options?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 29, 2015 at 10:36 am
I started again, deleted the subscriptions and created a new publication restored a copy of the DB to the subscriber and added into the replication with initialise with backup option.
Now I get this and the new one isn't working either....
2015-01-29 17:29:53.393 Agent message code 547. The INSERT statement conflicted with the FOREIGN KEY constraint "FK_BinItem_StockItem". The conflict occurred in database "Boohoo Live2", table "dbo.StockItem", column 'ItemID'.
???
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply