0

I'm creating a new publication for replication using the @allow_initialize_from_backup option to avoid the snapshot immediate creation and therefore locking all objects replicated on articles. The issue is that, either using SSMS wizard or tsql, when I start the publication (no subscription created yet), immediately I get lot of deadlocks on the tables been replicated. I'm checking all this information and more but I can't manage to do what I need. Haven't found a clear answer online. What I'm missing here?

And this is the code I'm trying to run:

EXEC sp_replicationdboption @dbname = N'db_name',
     @optname = N'publish',
     @value = N'true'
GO

EXEC sys.sp_addlogreader_agent @job_login = NULL,
     @job_password = NULL,
     @publisher_security_mode = 1
GO

EXEC sys.sp_addqreader_agent @job_login = NULL,
     @job_password = NULL,
     @frompublisher = 1
GO

-- Adding the transactional publication
EXEC sp_addpublication @publication = N'db_name',
     @description = 
     N'Transactional publication of database ''db_name'' from Publisher ''distributor''.',
     @sync_method = N'native',
     @retention = 0,
     @allow_push = N'true',
     @allow_pull = N'false',
     @allow_anonymous = N'true',
     @enabled_for_internet = N'false',
     @snapshot_in_defaultfolder = N'true',
     @compress_snapshot = N'false',
     @ftp_port = 21,
     @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',
     @allow_queued_tran = N'false',
     @allow_dts = N'false',
     @replicate_ddl = 1,
     @allow_initialize_from_backup = N'true',
     @enabled_for_p2p = N'false',
     @enabled_for_het_sub = N'false'
GO

EXEC sp_addpublication_snapshot @publication = N'db_name',
     @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 = 1

EXEC sys.sp_addsubscription
    @publication = N'db_name',
    @subscriber = N'subscriber server',
    @destination_db = N'db_name',
    @subscription_type = N'Push',
    @sync_type = N'initialize WITH backup',
    @backupdevicetype = N'Disk',
    @backupdevicename = N'F:\Backup\db_name\',
    @update_mode = N'read only',
    @subscriber_type = 0;

-- Add subscription agent
EXEC sys.sp_addpushsubscription_agent
    @publication = N'db_name',
    @subscriber = N'subscriber server',
    @subscriber_db = N'db_name',
    @job_login = NULL,
    @job_password = NULL,
    @subscriber_security_mode = 1,
    @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
  • We tested this in stage first,we also faced the same issue.So when we compared this with prod database which was replicated with snapshot,we could see lot of schema differences. – TheGameiswar Sep 23 '14 at 14:28
1

Got it. See my code, there is an option when creating the publication

EXEC sp_addpublication @publication = N'db_name',
       ...
    @immediate_sync = N'true'
    ...
GO

That's the equivalent of checking on the publication creation wizard the checkbox Create a snapshot immediately and keep the snapshot available to initialize subscriptions.

enter image description here

That's why when running it, tried to automatically create the snapshot. And as last snapshot is from couple of months, there is lot work to do in the middle. Ran with false value and now everything is ok, runnig smoothly and without issues.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.