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?
- sp_addpublication at MSDN
- How to: Initialize a Transactional Subscription from a Backup (Replication Transact-SQL Programming)
- Starway to SQL Server Replication
- Replication Without Creating a Snapshot
- similar question but different errorTransaction without snapshot?
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
Best Answer
Got it. See my code, there is an option when creating the publication
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.
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.