April 16, 2018 at 1:18 am
Problem Description:
Unusual High Waiting Tasks & Sudden dip in CPU usage with lots of locking while try to add an article Table(s) in an existing Transactional replication publication which was either initiated from snapshot or initiated from Backup in SQL server 2016 Enterprise edition, it was further observed that articles don’t get replicated after successful generation of snapshot if the distribution agent is in running condition during snapshot generation.
Environment details:
1. 24 cores of physical machines.
2. Issue experience on both version of SQL mentioned below
a. Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) Oct 28 2016 18:17:30 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600)
b. Microsoft SQL Server 2016 (SP1-CU7-GDR) (KB4057119) - 13.0.4466.4 (X64) Enterprise Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600)
3. SQL Server detected 4 sockets with 6 cores per socket and 6 logical processors per socket, 24 total logical processors; using 20 logical processors based on SQL Server licensing
Findings
exec sp_helppublication ‘Publication Name’
immediate_sync bit allow_anonymous bit
0 0
Database size:
512 GB
Replicated Articles size:
Problem arises irrespective of size of replicated article
------- SpWho2 'Active' Result during snapshot generation
Culprit ID : 6902
6902 SUSPENDED DBNAME (XXX) UNKNOWN TOKEN 10142 1455 10/20 10:05:02 Publication Name(XXXXXX\XXXXXX-XXXX-PUB__XXX__REPORTING__ALL__TAB-8)
------- DBCC INPUTBUFFER (6902)
sys.sp_MSactivate_auto_sub;1
-------- Individual Culprit Query
--EXEC %%Object(MultiName = @qualified_name).LockMatchID(ID = @object_id, Exclusive = 1, BindInternal = 0)
-------- Parent Procedure
--create procedure sys.sp_MSsetfilteredstatus @object_id int, @forceset bit = 0 as declare @qualified_name nvarchar(517) ,@retcode int ,@setFlag bit ,@pkkey sysname ,@source_table sysname ,@fcdc_tracked bit exec @retcode = sys.sp_MSreplcheck_publish if @@ERROR <> 0 or @retcode <> 0 return(1) exec sys.sp_MSget_qualified_name @object_id, @qualified_name output BEGIN TRANSACTION declare @cdc_tracked_tables table (object_id int) if object_id('cdc.change_tables') is not null begin insert @cdc_tracked_tables select distinct source_object_id from cdc.change_tables end if (@@rowcount = 0) set @fcdc_tracked = 0 else set @fcdc_tracked = 1 if not (@qualified_name is null) begin EXEC %%Object(MultiName = @qualified_name).LockMatchID(ID = @object_id, Exclusive = 1, BindInternal = 0) if @@error <> 0 select @qualified_name = null end if @qualified_name is null begin raiserror(18750, -1, -1, 'sp_MSsetfilteredstatus', 'object_id'); goto error_abort_exit end if exists( select * from sys.objects where type = 'RF' and parent_object_id = @object_id ) or exists( select * from sysarticles A join syspublications P on A.pubid = P.pubid where A.objid = @object_id and (P.allow_queued_tran = 1 or P.allow_sync_tran = 1)) or exists( select * from sysarticles where objid = @object_id and (upper(upd_cmd) like 'CALL%' OR upper(upd_cmd) like 'XCALL%' ) ) or exists( select * from sysarticles where objid = @object_id and (upper(del_cmd) like 'XCALL%' ) ) or exists( select * from sysarticles sa, syssubscriptions ss where sa.objid = @object_id and sa.artid = ss.artid and ss.status = 3) or @forceset = 1 begin EXEC %%Relation(ID = @object_id).SetHasFilterProc(Value = 1) set @setFlag = 1 end else begin if (@fcdc_tracked = 0) EXEC %%Relation(ID = @object_id).SetHasFilterProc(Value = 0) set @setFlag = 0 end --set/reset IsLoggedForRepl for all replicated column (except legacy blobs) depand on if has_replication_filter is set --this controls logging behavior of offrow columns in deletes --don't clear the bits if table is still being tracked by cdc if(@setFlag = 1 or @fcdc_tracked = 0) begin declare #hpk cursor local fast_forward for select name from sys.columns where object_id = @object_id and is_replicated = 1 and system_type_id not in (34, 35, 99) --image, text. ntext and user_type_id not in (34, 35, 99) --image, text. ntext open #hpk fetch #hpk into @pkkey while (@@fetch_status = 0) begin EXEC %%ColumnEx(ObjectID = @object_id, Name = @pkkey).SetLogForRepl(Value = @setFlag) IF @@ERROR <> 0 BEGIN select @source_table = object_name(@object_id) RAISERROR(15052, -1, -1, @source_table) return 1 END fetch #hpk into @pkkey end close #hpk deallocate #hpk --in case of turning of has_replicate_filter, make sure IsLoggedForRepl is still set for base of pk/computed pk columns if(@setFlag = 0) begin exec @retcode = sp_MSSetLogForRepl @object_id if @@ERROR <> 0 or @retcode <> 0 return(1) end end COMMIT TRANSACTION return (0) error_abort_exit: IF @@TRANCOUNT = 1 ROLLBACK TRAN ELSE COMMIT TRAN return (1)
Snaps during problem
April 16, 2018 at 9:09 am
You have locking issues on the publisher? I'm trying to understand because it's not clear what's going on. You mention initiated from backup or snapshot, but those are initiated on the subscriber.
If you are adding an article to a publication, what happens ? Is this where you have blocking? You should. a new snapshot is needed for all articles.
April 19, 2018 at 7:07 am
Steve Jones - SSC Editor - Monday, April 16, 2018 9:09 AMYou have locking issues on the publisher? I'm trying to understand because it's not clear what's going on. You mention initiated from backup or snapshot, but those are initiated on the subscriber.If you are adding an article to a publication, what happens ? Is this where you have blocking? You should. a new snapshot is needed for all articles.
Thanks Steve ,kindly find the responses below
1. You have locking issues on the publisher?
Yes we have locking issue on the publisher server.
2. not clear what's going on
we are facing Unusual High Waiting Tasks & Sudden dip in CPU usage with lots of locking on Publisher server (which is also production environment) while trying to generate snapshot for newly added Table(s) in an existing Transactional publication (which is already initialized and successfully sync with subscriber) in SQL server 2016 Enterprise edition , Following Session is found during snapshot generation
------- SpWho2 'Active' Result -- UNKNOWN TOKEN (what is it)
Culprit ID : 6902
6902 SUSPENDED DBNAME (XXX) UNKNOWN TOKEN 10142 1455 10/20 10:05:02 Publication Name(XXXXXX\XXXXXX-XXXX-PUB__XXX__REPORTING__ALL__TAB-8)
------- DBCC INPUTBUFFER (6902)
sys.sp_MSactivate_auto_sub;1
3. Initiated from backup or snapshot, but those are initiated on the subscriber.
Two different Publications were made for Table replication to different servers out of which one was initiated from backup and second was initiated from snapshot.
4. If you are adding an article to a publication, what happens ? Is this where you have blocking?
No issues occur while adding article (Table) to a publication , Blocking occurs during snapshot generation of that newly added article.
April 19, 2018 at 8:59 am
There is going to be some blocking. I think that if you change the sync_method parameter to concurrent (default native), this can help: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-changepublication-transact-sql?view=sql-server-2017
April 26, 2018 at 12:38 am
Steve Jones - SSC Editor - Thursday, April 19, 2018 8:59 AMThere is going to be some blocking. I think that if you change the sync_method parameter to concurrent (default native), this can help: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-changepublication-transact-sql?view=sql-server-2017
Dear Steve,
Sync_method of the publication(s) (under discussion) are already set to "Concurrent i.e. synchronization_method = 3.
restricted status task replication frequency synchronization method
0 1 1 0 3
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply