0

We have set up a merge replication in SQL Server. One of our biggest deadlocks is this stored procedure up above that is run by subscribers.

CREATE PROCEDURE sys.sp_MSmakegeneration
    @gencheck int = 0,
    @commongen bigint = NULL, 
    @commongenguid uniqueidentifier = NULL,
    @commongenvalid int = NULL OUTPUT,
    @compatlevel int = 90
AS
    SET NOCOUNT ON
    DECLARE @gen bigint
, @replnick binary(6)
, @dt datetime
, @art_nick int
, @first_ts int
, @makenewrow bit
, @retcode smallint
, @nickbin varbinary(255)
, @maxgendiff_fornewrow bigint
, @count_of_articles int
, @lock_acquired bit
, @lock_resource nvarchar(255)
, @procfailed bit
, @delete_old_genhistory bit
, @close_old_genhistory bit
, @changecount int
, @dbname nvarchar(258)
, @processing_order int
, @prev_processing_order int
, @prev_art_nick int
, @force_leveling bit
, @gen_change_threshold int

declare @cmd nvarchar(4000)
declare @old_bi_gen bigint
declare @bi_view_objid int
--declare @GENSTATUS_OPEN tinyint
--declare @GENSTATUS_MERGE_INSERTED_OPEN tinyint
--declare @GENSTATUS_LOCAL_CLOSED tinyint
--declare @GENSTATUS_TEMPORARILY_CLOSED tinyint

What does this stored procedure exactly do here?

And why should it be repeatedly called while it exists there?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
porya ras
  • 458
  • 5
  • 15
  • @Charlieface: voted for the migration :-) – marc_s Aug 12 '22 at 09:16
  • It's just a procedure used in Merge Replication. It has specific handling for deadlocks, so if you are not seeing an actual issue with the results I would just ignore it – Charlieface Aug 12 '22 at 09:59
  • @Charlieface It's always showing in performance dashboard in deadlock list. There are 2 to 3 sps inside it that i can't find in system. can it be a problem that causes it ? , because they're inside sys schema and I don't have privillages to add them – porya ras Aug 13 '22 at 06:36
  • Like I said, it's an internal system procedure that does stuff for replication. If it's not causing an actual issue (such as replication failing etc) then don't worry about it. You can see the full procedure here https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=3a163352962d55346f694b83605ef8db and you can see it has handling for deadlock errors. – Charlieface Aug 13 '22 at 22:08

0 Answers0