1

I have following script for replication that I am running in source database (subscription is not added yet, target database does not exist yet in target server):

use [myDatabase]
exec sp_replicationdboption @dbname = N'myDatabase', @optname = N'publish', @value = N'true'
GO

use [myDatabase]
exec sp_addpublication @publication = N'myDatabase_pub', 
    @description = N'Transactional publication of database ''myDatabase'' from Publisher ''DATABASE-SERVER''.', 
    @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', 
    @allow_anonymous = N'true', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', 
    @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', 
    @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', 
    @immediate_sync = N'true', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', 
    @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', 
    @enabled_for_het_sub = N'false'
GO
exec sp_addpublication_snapshot @publication = N'myDatabase_pub', @frequency_type = 1, @frequency_interval = 0, 
    @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, 
    @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, 
    @active_start_date = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 0, 
    @publisher_login = N'username', @publisher_password = N'password'
use [myDatabase]
exec sp_addarticle @publication = N'myDatabase_pub', @article = N'simple_Table', @source_owner = N'dbo', 
    @source_object = N'simple_Table', @type = N'logbased', @description = null, @creation_script = null, 
    @pre_creation_cmd = N'drop', @schema_option = 0x000000000803609F, @identityrangemanagementoption = N'manual', 
    @destination_table = N'simple_Table', @destination_owner = N'dbo', @vertical_partition = N'false', 
    @ins_cmd = N'CALL sp_MSins_dbosimple_Table', @del_cmd = N'CALL sp_MSdel_dbosimple_Table', 
    @upd_cmd = N'SCALL sp_MSupd_dbosimple_Table'
GO

use [myDatabase]
exec sp_addarticle @publication = N'myDatabase_pub', @article = N'simple_Table_class', @source_owner = N'dbo', 
    @source_object = N'simple_Table_class', @type = N'logbased', @description = null, @creation_script = null, 
    @pre_creation_cmd = N'drop', @schema_option = 0x000000000803609F, @identityrangemanagementoption = N'manual', 
    @destination_table = N'simple_Table_class', @destination_owner = N'dbo', @vertical_partition = N'false', 
    @ins_cmd = N'CALL sp_MSins_dbosimple_Table_class', 
    @del_cmd = N'CALL sp_MSdel_dbosimple_Table_class', 
    @upd_cmd = N'SCALL sp_MSupd_dbosimple_Table_class'
GO

This script worked fine by few months. However today when I run it I got error like:

Could not add new article to publication 'myDatabase_pub' because of active schema change activities or a snapshot is being generated.

Do you know what how can I check what is blocking replication? What kind of schema changes?

  • As the error says, either a schema change is happening or likely a snapshot is being created / scheduled to be created. You can check Replication and the Snapshot Agent to rule Replication out. – clifton_h Apr 21 at 0:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.