Snapshot Agent Acquires schema modification (SCH-M) lock on all the articles of a publication -

Snapshot Agent Acquires schema modification (SCH-M) lock on all the articles of a publication

DHARMENDRA KESHARIAugust 06, 2018SQL LINUX, SQL WINDOWS0 comments

As the business requirement, It is very common to see a request for adding a new article or subscription to the existing publication. After adding the new article or subscription to the existing publication, we use snapshot agent to initialize it so that data can flow from Publisher to Distributor to Subscriber(s).

But, the snapshot agent requires a very short duration schema modification (SCH-M) lock on all the articles of the publication. If the articles are being queried thousand times per second or queried for a long time, they will block the snapshot agent because of SCH-M lock. The schema modification is not compatible with any other lock. In the result of this, you may see massive blocking on the system, and it can cause production impact.

Demonstration

I am going to use the Extended Event to show you the SCH-M lock is being acquired on all the tables of the publication even we initial only newly added article of the existing publication.

Create a Publication Database on the Publisher Server.

----Run on the publisher Server
USE master
GO
CREATE DATABASE Forest
GO

Create two tables called “Repl1”, “Repl2”, and insert 5000 rows into each table under the publication database on the Publisher Server.

----Run on the publisher Server
USE Forest
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
----Create first table
CREATE TABLE [dbo].[Repl1](
							[sr] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
							[GUIDNumber] [nvarchar](100) NULL,
							[CapturedDatetime] [datetime] NULL,
							PRIMARY KEY CLUSTERED (	[sr] ASC) ON [PRIMARY]
						) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Repl1] ADD  DEFAULT (newid()) FOR [GUIDNumber]
GO
ALTER TABLE [dbo].[Repl1] ADD  DEFAULT (getdate()) FOR [CapturedDatetime]
GO
----Create second table

CREATE TABLE [dbo].[Repl2](
							[sr] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
							[GUIDNumber] [nvarchar](100) NULL,
							[CapturedDatetime] [datetime] NULL,
							PRIMARY KEY CLUSTERED (	[sr] ASC) ON [PRIMARY]
						) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Repl2] ADD  DEFAULT (newid()) FOR [GUIDNumber]
GO
ALTER TABLE [dbo].[Repl2] ADD  DEFAULT (getdate()) FOR [CapturedDatetime]
GO

---INSERT 5000 ROWS INTO THE TABLE [Repl1]
USE Forest
GO
INSERT INTO [Repl1] VALUES (newid(),getdate())
GO 5000
INSERT INTO [Repl2] VALUES (newid(),getdate())
GO 5000

Let’s get the created tables object_id. We can see from the below image that it is 565577053 and 1877581727.

----Run on the publisher Server
select * from sys.tables where name in ('Repl1', 'Repl2')

Create a Subscription Database on the Subscriber server.

----Run on the Subscriber Server
USE master
GO
CREATE DATABASE Forest
GO

Create a Publication “ForestPub”, add the snapshot agent for the publication, and add the article “Repl1” (table) in the publication on the Publisher Server.

----Run on the publisher Server
use [Forest]
exec sp_replicationdboption @dbname = N'Forest', @optname = N'publish', @value = N'true'
GO
-- Create the transactional publication (ForestPub)
use [Forest]
exec sp_addpublication @publication = N'ForestPub', 
@description = N'Transactional publication of database ''Forest'' from Publisher ''SGDDNODE1''.', 
@sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', 
@allow_anonymous = N'false', @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'false', 
@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
---Creates the Snapshot Agent for the publication ForestPub
exec sp_addpublication_snapshot @publication = N'ForestPub', 
@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

---Add the article to publication
use [Forest]
exec sp_addarticle @publication = N'ForestPub', @article = N'Repl1', @source_owner = N'dbo', 
@source_object = N'Repl1', @type = N'logbased', @description = null, @creation_script = null, 
@pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, 
@identityrangemanagementoption = N'manual', @destination_table = N'Repl1', 
@destination_owner = N'dbo', @vertical_partition = N'false', 
@ins_cmd = N'CALL sp_MSins_dboRepl1', @del_cmd = N'CALL sp_MSdel_dboRepl1', 
@upd_cmd = N'SCALL sp_MSupd_dboRepl1'
GO

Add the Subscriber Server in the Publication “ForestPub” on the Publisher Server.

----Run on the publisher Server
use [Forest]
exec sp_addsubscription @publication = N'ForestPub', @subscriber = N'SGDDNODE2', 
@destination_db = N'Forest', @subscription_type = N'Push', @sync_type = N'automatic', 
@article = N'all', @update_mode = N'read only',@subscriber_type = 0
exec sp_addpushsubscription_agent @publication = N'ForestPub', @subscriber = N'SGDDNODE2', 
@subscriber_db = N'Forest', @job_login = null, @job_password = null, @subscriber_security_mode = 1, 
@frequency_type = 64, @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 = 20180715, 
@active_end_date = 99991231, @enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor'
GO

Create and Start the below Extend Event to capture on the locks on the Publisher Server.

----Run on the publisher Server
CREATE EVENT SESSION [ReplLocks_FirstArticleinitialize] ON SERVER 
ADD EVENT sqlserver.lock_acquired(SET collect_database_name=(1),collect_resource_description=(1)
    ACTION(	sqlserver.client_app_name,sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text,
			sqlserver.username
			)
    WHERE ([sqlserver].[equal_i_sql_unicode_string]([database_name],N'Forest')))
ADD TARGET package0.event_file(SET filename=N'c:\temp\FirstArticleinitialize')
---change the location 'c:\temp\FirstArticleinitialize' as per your system
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=1 SECONDS,
MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
GO
ALTER EVENT SESSION ReplLocks_FirstArticleinitialize 
ON SERVER STATE = START

Now, let’s initialized the subscriber by running the snapshot agent for the article.

Let’s check the extended even to see schema modification lock (SCH-M) has been required on the table “Repl1” (object_id = 565577053 ). The below snapshot confirms the same.

let’s add the second article “Repl2” to the publication “ForestPub”.

use [Forest]
exec sp_addarticle @publication = N'ForestPub', @article = N'Repl2', @source_owner = N'dbo', 
@source_object = N'Repl2', @type = N'logbased', @description = N'', @creation_script = N'', 
@pre_creation_cmd = N'truncate', @schema_option = 0x000000000803509F, 
@identityrangemanagementoption = N'manual', @destination_table = N'Repl2', 
@destination_owner = N'dbo', @status = 24, @vertical_partition = N'false', 
@ins_cmd = N'CALL [sp_MSins_dboRepl2]', @del_cmd = N'CALL [sp_MSdel_dboRepl2]', 
@upd_cmd = N'SCALL [sp_MSupd_dboRepl2]'
GO

-- Adding the transactional subscriptions
use [Forest]
exec sp_addsubscription @publication = N'ForestPub', 
@subscriber = N'SGDDNODE2', @destination_db = N'Forest', 
@subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', 
@update_mode = N'read only', @subscriber_type = 0
exec sp_addpushsubscription_agent @publication = N'ForestPub', 
@subscriber = N'SGDDNODE2', @subscriber_db = N'Forest', @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

After adding the article “Repl2” the publication successfully, I initialized only second article of the publication by running the snapshot agent but when it acquired schema modification locks (Sch-M) on both the articles.

So, the above demonstration confirms that even we initialize only one article of the publication using the snapshot agent, SQL Server will still acquire schema modification (Sch-M) lock on all the articles of the publication.

If your company cannot afford any production impact, you can initialize when there is a non-peak business hour or initialize without a snapshot documented in books online.

Hope, you find this blog helpful!