Sample Header Ad - 728x90

MS SQL Server Replication - stored procedures sp_refreshsubscriptions and sp_addsubscription blocks all tables of a publication with SCH-M lock

0 votes
1 answer
228 views
The setup: A push transaction replication is configured for about 20 tables/articles at SQL2019 CU31 Standard. Some of the tables are permanently under a load of thousends of inserts per second. In general replication works fine except the moment when a new table is added to the replication. Initially we execute sp_addarticle to add a new article at the publisher. Then, we have to call either sp_refreshsubscriptions or sp_addsubscription, to add a subscription to this article at the subscriber. The issue: The stored procedure sp_refreshsubscriptions puts an SCH-M lock not only on the table that is added. It also put the lock on other tables belonged to the same publication, including the ones highly loaded with inserts. As SCH-M is not compatible with any other locks inserts start to wait for the SCH-M session while the SCH-M session itself waits for other concurrent inserts resulting in deadlocks and locks for 15..20 seconds. This is a downtime for business and unacceptable. An alternative approach with sp_addsubscription brings no releif. Even though the new article is explicitly specified in parameters still the procedure puts SCH-M locks on all tables in the same publication. In contrast when inserts are paused (at a Stage environment) the same operation executes instantly. The question: Why the locks of unrelated tables and is it possible to workaround them besides pausing inserts?
Asked by Evgeny Veselov (1 rep)
Sep 22, 2023, 10:30 AM
Last activity: Jun 7, 2025, 02:03 AM