Blocking process alert filters not working

• 
  Can you show me what you're trying to filter out (ie a picture or copy of the sql query) and the exact regex you're using?

  Russell D May 09, 2019 07:33

  - 0 +
• 
  Here is what is captured as the blocking query
  
  

  Database Name:	distribution
  Blocking Session ID:	72
  Number of blocked descendants:	1
  Total blocking duration for all descendants:	00:02:03
  Blocking SQL fragment:	CREATE PROCEDURE sp_MSadd_replcmds
  @publisher_database_id int,
  @publisher_id smallint,
  @publisher_db sysname,
  @data varbinary(1595),
  @1data varbinary(1595) = NULL,
  @2data varbinary(1595) = NULL,
  @3data varbinary(1595) = NULL,
  @4data varbinary(1595) = NULL,
  @5data varbinary(1595) = NULL,
  @6data varbinary(1595) = NULL,
  @7data varbinary(1595) = NULL,
  @8data varbinary(1595) = NULL,
  @9data varbinary(1595) = NULL,
  @10data varbinary(1595) = NULL,
  @11data varbinary(1595) = NULL,
  @12data varbinary(1595) = NULL,
  @13data varbinary(1595) = NULL,
  @14data varbinary(1595) = NULL,
  @15data varbinary(1595) = NULL,
  @16data varbinary(1595) = NULL,
  @17data varbinary(1595) = NULL,
  @18data varbinary(1595) = NULL,
  @19data varbinary(1595) = NULL,
  @20data varbinary(1595) = NULL,
  @21data varbinary(1595) = NULL,
  @22data varbinary(1595) = NULL,
  @23data varbinary(1595) = NULL,
  @24data varbinary(1595) = NULL,
  @25data varbinary(1595) = NULL,
  @26data varbinary(1595) = NULL
  AS
  BEGIN
  SET NOCOUNT ON
  DECLARE @date datetime
  ,@x int
  ,@tempdata varbinary(1595)
  
  DECLARE @xactId	varbinary(10),
  @xactSeqNo	varbinary(10),
  @artId	int,
  @cmdId	int,
  @cmdType	int,
  @fIncomplete	bit,
  @cmdLen	int,
  @originator_id	int,
  @origSrvLen	int,
  @origDbLen	int,
  @origPublId	int,
  @origDbVersion	int,
  @origLSN	varbinary(10),
  @hashKey	int,
  @cmdText	varbinary(1595),
  @originator	sysname,
  @originatorDb	sysname
  
  -- @publisher_id is always from master..sysservers, so we need to fetch from MSreplservers
  select @publisher_id = sys.fn_MSrepl_getsrvidfromdistdb(@publisher_id)
  
  SELECT @date = GETDATE()
  select @x = 0
  select @tempdata = null
  while @x <= 26
  begin
  select @tempdata = CASE @x
  when 0 then @data
  when 1 then @1data
  when 2 then @2data
  when 3 then @3data
  when 4 then @4data
  when 5 then @5data
  when 6 then @6data
  when 7 then @7data
  when 8 then @8data
  when 9 then @9data
  when 10 then @10data
  when 11 then @11data
  when 12 then @12data
  when 13 then @13data
  when 14 then @14data
  when 15 then @15data
  when 16 then @16data
  when 17 then @17data
  when 18 then @18data
  when 19 then @19data
  when 20 then @20data
  when 21 then @21data
  when 22 then @22data
  when 23 then @23data
  when 24 then @24data
  when 25 then @25data
  when 26 then @26data
  end
  
  if @tempdata is NULL
  goto END_CMDS
  
  -- We will now breakup the binary data. Check HP_FIXED_DATA 
  -- in publish.cpp for all of the offsets listed below...
  select @xactId = substring( @tempdata, 1, 10),
  @xactSeqNo	= substring( @tempdata, 11, 10),
  @artId	= substring( @tempdata, 21, 4),
  @cmdId	= substring( @tempdata, 25, 4),
  @cmdType	= substring( @tempdata, 29, 4),
  @fIncomplete	= convert(bit, substring( @tempdata, 33, 1)),
  @cmdLen	= substring( @tempdata, 34, 2),
  @origSrvLen	= substring( @tempdata, 36, 2),
  @origDbLen	= substring( @tempdata, 38, 2),
  @hashKey	= substring( @tempdata, 40, 2),
  -- @origPublId = only done below if an originator len is detected : usually = substring( @tempdata, 42, 4)
  -- @origDbVersion=only done below if an originator len is detected : usually = substring( @tempdata, 46, 4)
  @origLSN	= substring( @tempdata, 50, 10),
  @cmdText	= substring( @tempdata, 60, @cmdLen)
  -- @originator = only done below if an originator len is detected : usually = substring( @tempdata, 60 + @cmdLen, @origSrvLen)
  -- @originatorDb= only done below if an originator len is detected : usually = substring( @tempdata, 60 + @cmdLen + @origSrvLen, @origDbLen)
  
  IF @cmdId = 1
  begin
  INSERT INTO MSrepl_transactions 
  VALUES (@publisher_database_id, @xactId, @xactSeqNo, @date)
  end
  
  -- do special processing for the different command typs if needed
  if( @cmdType in( 37,38 ) )
  begin
  select @cmdType = 38 - @cmdType
  exec sp_MSset_syncstate @publisher_id, @publisher_db, @artId, @cmdType, @xactSeqNo
  select @cmdType = (38 - @cmdType) | 0x80000000
  end
  -- Check all posted cmds of SQLCMD type to see if they are tracer records
  -- sql cmd type is (47 | 0x40000000) or 1073741871
  else if @cmdType = 1073741871
  begin
  declare @tracer_id int,
  @retcode	int
  
  select @tracer_id = cast(cast(@cmdText as nvarchar) as int)
  
  exec @retcode = sys.sp_MSupdate_tracer_history @tracer_id = @tracer_id
  if @retcode <> 0 or @@error <> 0
  return 1
  end
  
  -- only add it if the command is not empty
  if @cmdLen > 0
  begin	
  -- handle article metadata cleanup after a table drop operation
  -- REPL_DROPOBSOLETEARTICLE_LOG_CMD (48)
  if ((@cmdType & 0xFFFFFFF) = 48)
  begin
  select @retcode = 0
  DECLARE @pub sysname, @pub_db sysname, @sub sysname, @sub_db sysname, @p sysname, @art sysname
  DECLARE #hCsubart CURSOR LOCAL SCROLL_LOCKS FOR
  select ps.srvname, p.publisher_db, ss.srvname, subscriber_db, publication, article
  from distribution..MSpublications p
  join MSreplservers as ps on p.publisher_id = ps.srvid
  join distribution..MSarticles a on p.publisher_id = a.publisher_id and p.publisher_db = a.publisher_db and p.publication_id = a.publication_id
  join distribution..MSpublisher_databases d on d.publisher_id = p.publisher_id
  left outer join distribution..MSsubscriptions s on p.publisher_id = s.publisher_id and p.publisher_db = s.publisher_db and p.publication_id = s.publication_id and s.article_id = a.article_id
  left outer join MSreplservers as ss on s.subscriber_id = ss.srvid
  where d.id = @publisher_database_id and p.publisher_id = @publisher_id and a.article_id = @artId
  OPEN #hCsubart
  FETCH #hCsubart INTO @pub, @pub_db, @sub, @sub_db, @p, @art
  WHILE (@@fetch_status != -1 and @retcode = 0) BEGIN
  exec @retcode = dbo.sp_MSdrop_subscription @publisher = @pub, @publisher_db = @pub_db, @subscriber = @sub, @article_id = @artId, @subscriber_db = @sub_db, @publication = @p
  FETCH #hCsubart INTO @pub, @pub_db, @sub, @sub_db, @p, @art
  END
  CLOSE #hCsubart
  DEALLOCATE #hCsubart
  if @retcode = 0 and @pub is not null and @pub_db is not null and @p is not null and @art is not null begin
  exec @retcode = dbo.sp_MSdrop_article @publisher = @pub, @publisher_db = @pub_db, @publication = @p, @article = @art
  end
  if @retcode <> 0 or @@error <> 0 goto Failure else goto Continue_next_command
  end
  else
  --handle nonsync subscription setup when command type is 
  -- REPL_NOSYNC_SUBSCRIPTION_SETUP_LOG_CMD (54)
  if ((@cmdType & 0xFFFFFFF) = 54)
  begin
  -- When logreader gets a log record with this type,
  -- the MSnosyncsubsetup table should already exist,
  -- report the failure if it does not exist.
  if (object_id(N'dbo.MSnosyncsubsetup', 'U')) is NULL 
  begin
  goto Failure
  end
  else
  begin
  declare @nosyncCommandStr	nvarchar(max),
  @publisher sysname,
  @publication sysname,
  @article	sysname,
  @subscriber	sysname,
  @destination_db	sysname,
  @subscriptionlsn	binary(10),
  @lsnsource	tinyint,
  @originator_publication_id	int,
  @originator_db_version	int,
  @originator_meta_data	nvarchar(max),
  @nosync_setup_script	nvarchar(max),
  @next_valid_lsn	binary(10),
  @next_valid_lsn_from_log	binary(10)
  
  -- if most high bit is set to 1 then interprete buffer as a new format 99 <= 0xFFFFFF9D (supports 2^32 ids), otherwise it's a backward compatibility for 99 <= 0x39003900 (this is max id)
  select @originator_publication_id = CASE WHEN CAST(substring(@cmdText, 1, 4) AS int) <= 0 THEN -CAST(substring(@cmdText, 1, 4) AS int) ELSE CAST(CAST(substring(@cmdText, 1, 4) AS nvarchar) AS int) END,
  @next_valid_lsn_from_log = CAST(substring(@cmdText, 5, 10) AS binary(10))
  if @@error <> 0 goto Failure
  
  -- Verify that the number of parameters is correct before using 
  -- these parameters in sp_MSsetupnosyncsubwithlsnatdist
  if ((select count(*) from dbo.MSnosyncsubsetup
  where publisher_database_id = @publisher_database_id
  and publication_id = @originator_publication_id
  and artid = @artId
  and next_valid_lsn = @next_valid_lsn_from_log) <> 13) goto Failure
  if @@error <> 0 goto Failure
  
  select @publisher = cast((select parameterValue 
  from dbo.MSnosyncsubsetup
  where publisher_database_id = @publisher_database_id
  and publication_id = @originator_publication_id
  and artid = @artId
  and next_valid_lsn = @next_valid_lsn_from_log
  and parameterName = N'publisher') as sysname),
  @publisher_db = cast((select parameterValue 
  from dbo.MSnosyncsubsetup
  where publisher_database_id = @publisher_database_id
  and publication_id = @originator_publication_id
  and artid = @artId
  and next_valid_lsn = @next_valid_lsn_from_log
  and parameterName = N'publisher_db') as sysname),
  @publication = cast((select parameterValue 
  from dbo.MSnosyncsubsetup
  where publisher_database_id = @publisher_database_id
  and publication_id = @originator_publication_id
  and artid = @artId
  and next_valid_lsn = @next_valid_lsn_from_log
  and parameterName = N'publication') as sysname),
  @article = cast((select parameterValue 
  from dbo.MSnosyncsubsetup
  where publisher_database_id = @publisher_database_id
  and publication_id = @originator_publication_id
  and artid = @artId
  and next_valid_lsn = @next_valid_lsn_from_log
  and parameterName = N'article') as sysname),
  @subscriber = cast((select parameterValue 
  from dbo.MSnosyncsubsetup
  where publisher_database_id = @publisher_database_id
  and publication_id = @originator_publication_id
  and artid = @artId
  and next_valid_lsn = @next_valid_lsn_from_log
  and parameterName = N'subscriber') as sysname),
  @destination_db = cast((select parameterValue 
  from dbo.MSnosyncsubsetup
  where publisher_database_id = @publisher_database_id
  and publication_id = @originator_publication_id
  and artid = @artId
  and next_valid_lsn = @next_valid_lsn_from_log
  and parameterName = N'destination_db') as sysname),
  @subscriptionlsn = cast((select parameterValue 
  from dbo.MSnosyncsubsetup
  where publisher_database_id = @publisher_database_id
  and publication_id = @originator_publication_id
  and artid = @artId
  and next_valid_lsn = @next_valid_lsn_from_log
  and parameterName = N'subscriptionlsn') as binary(10)),
  @lsnsource = cast((select parameterValue 
  from dbo.MSnosyncsubsetup
  where publisher_database_id = @publisher_database_id
  and publication_id = @originator_publication_id
  and artid = @artId
  and next_valid_lsn = @next_valid_lsn_from_log
  and parameterName = N'lsnsource') as tinyint),
  @originator_publication_id = cast((select parameterValue 
  from dbo.MSnosyncsubsetup
  where publisher_database_id = @publisher_database_id
  and publication_id = @originator_publication_id
  and artid = @artId
  and next_valid_lsn = @next_valid_lsn_from_log
  and parameterName = N'originator_publication_id') as int),
  @originator_db_version = cast((select parameterValue 
  from dbo.MSnosyncsubsetup
  where publisher_database_id = @publisher_database_id
  and publication_id = @originator_publication_id
  and artid = @artId
  and next_valid_lsn = @next_valid_lsn_from_log
  and parameterName = N'originator_db_version') as int),
  @originator_meta_data = (select parameterValue 
  from dbo.MSnosyncsubsetup
  where publisher_database_id = @publisher_database_id
  and publication_id = @originator_publication_id
  and artid = @artId
  and next_valid_lsn = @next_valid_lsn_from_log
  and parameterName = N'originator_meta_data'),
  @nosync_setup_script = (select parameterValue 
  from dbo.MSnosyncsubsetup
  where publisher_database_id = @publisher_database_id
  and publication_id = @originator_publication_id
  and artid = @artId
  and next_valid_lsn = @next_valid_lsn_from_log
  and parameterName = N'nosync_setup_script'),
  @next_valid_lsn = cast((select parameterValue 
  from dbo.MSnosyncsubsetup
  where publisher_database_id = @publisher_database_id
  and publication_id = @originator_publication_id
  and artid = @artId
  and next_valid_lsn = @next_valid_lsn_from_log
  and parameterName = N'next_valid_lsn') as binary(10))
  if @@error <> 0 goto Failure
  
  if @cmdId = 1
  begin
  -- Type 54 transactions should be provided with empty command which will be later used in sp_MSsetupnosyncsubwithlsnatdist
  insert dbo.MSrepl_commands
  (publisher_database_id, xact_seqno, type, article_id, originator_id, command_id, partial_command, command)
  values
  (@publisher_database_id, @xactSeqNo, @cmdType, @artId, 0, @cmdId, 0, convert(varbinary(1024), N''))
  if @@error <> 0 goto Failure
  end
  
  select @nosyncCommandStr = N'exec sp_MSsetupnosyncsubwithlsnatdist 
  @publisher = @publisher,
  @publisher_db = @publisher_db,
  @publication = @publication,
  @article = @article,
  @subscriber = @subscriber,
  @destination_db = @destination_db,
  @subscriptionlsn = @subscriptionlsn,
  @lsnsource = @lsnsource,
  @originator_publication_id = @originator_publication_id,
  @originator_db_version = @originator_db_version,
  @originator_meta_data = @originator_meta_data,
  @nosync_setup_script = @nosync_setup_script,
  @next_valid_lsn = @next_valid_lsn'
  
  exec sp_executesql
  @stmt = @nosyncCommandStr,
  @params = N'@publisher sysname,
  @publisher_db sysname,
  @publication sysname,
  @article	sysname,
  @subscriber	sysname,
  @destination_db	sysname,
  @subscriptionlsn	binary(10),
  @lsnsource	tinyint,
  @originator_publication_id	int,
  @originator_db_version	int,
  @originator_meta_data	nvarchar(max),
  @nosync_setup_script	nvarchar(max),
  @next_valid_lsn	binary(10)',
  @publisher = @publisher,
  @publisher_db = @publisher_db,
  @publication = @publication,
  @article = @article,
  @subscriber = @subscriber,
  @destination_db = @destination_db,
  @subscriptionlsn = @subscriptionlsn,
  @lsnsource = @lsnsource,
  @originator_publication_id = @originator_publication_id,
  @originator_db_version = @originator_db_version,
  @originator_meta_data = @originator_meta_data,
  @nosync_setup_script = @nosync_setup_script,
  @next_valid_lsn = @next_valid_lsn
  
  if @@error <> 0 goto Failure
  
  end --end of if (object_id(N'dbo.MSnosyncsubsetup, 'U')) is NOT NULL
  
  -- Upon success of the execution of sp_MSsetupnosyncsubwithlsnatdist,
  -- clean up the MSnosyncsubsetup table by deleting the parameters
  -- regarding this specified nonsync subscription
  delete dbo.MSnosyncsubsetup 
  where publisher_database_id = @publisher_database_id
  and publication_id = @originator_publication_id
  and artid = @artId
  and next_valid_lsn = @next_valid_lsn_from_log
  
  if @@error <> 0 goto Failure
  
  goto Continue_next_command
  Failure:
  return 1
  Continue_next_command:
  end
  else --i.e., when (@cmdType & 0xFFFFFFF) IS NOT 54
  begin
  -- Get the originator_id for the first command
  if @origSrvLen <> 0 and @origDbLen <> 0 
  begin 
  select @originator_id = null,
  @originator	= substring( @tempdata, 60 + @cmdLen, @origSrvLen),
  @originatorDb	= substring( @tempdata, 60 + @cmdLen + @origSrvLen, @origDbLen),
  @origPublId = substring( @tempdata, 42, 4),
  @origDbVersion	= substring( @tempdata, 46, 4)
  
  -- if @origPublId and @origDbVersion is 0 or NULL
  -- then we are not in Peer-To-Peer so we do not need
  -- to set the dbversion and publication id values...
  if isnull(@origPublId, 0) != 0
  and isnull(@origDbVersion, 0) != 0
  begin
  select @originator_id = id 
  from MSrepl_originators with (readpast)
  where publisher_database_id = @publisher_database_id 
  and UPPER(srvname) = UPPER(@originator)
  and dbname = @originatorDb
  and publication_id = @origPublId
  and dbversion = @origDbVersion
  end
  else
  begin
  select @origPublId = NULL,
  @origDbVersion = NULL
  
  select @originator_id = id 
  from MSrepl_originators 
  where publisher_database_id = @publisher_database_id 
  and UPPER(srvname) = UPPER(@originator)
  and dbname = @originatorDb
  and publication_id is NULL
  and dbversion is NULL
  end
  
  if @originator_id is null
  begin
  insert into MSrepl_originators (publisher_database_id, srvname, dbname, publication_id, dbversion) 
  values (@publisher_database_id, @originator, @originatorDb, @origPublId, @origDbVersion)
  
  select @originator_id = @@identity
  end
  end
  else
  select @originator_id = 0
  
  INSERT INTO MSrepl_commands 
  (
  publisher_database_id, 
  xact_seqno, 
  type, 
  article_id, 
  originator_id, 
  command_id, 
  partial_command, 
  hashkey,
  originator_lsn,
  command
  )
  VALUES 
  (
  @publisher_database_id,
  @xactSeqNo,
  @cmdType,
  @artId, 
  @originator_id,
  @cmdId,
  @fIncomplete,
  @hashKey,
  @origLSN,
  @cmdText
  )
  
  end --end of i.e., when (@cmdType & 0xFFFFFFF) in (54, 55, 56)
  end --end of if @cmdLen > 0
  
  select @x = @x + 1
  end
  
  END_CMDS:
  IF @@ERROR <> 0
  return (1)
  END
  

  
  

  I just added the string MSrepl_commands  as the filter.

  SQLDBA May 10, 2019 19:45

  - 0 +
• 
  I wonder if its because your're inserting into a table called that though in theory it should work. Try to filter out sp_MSrepl_commands?
  
  Can I see it exactly as you've added it?

  Russell D May 12, 2019 05:47

  - 0 +
• 
  
  The blocking alert continues to be raised.

  SQLDBA May 16, 2019 14:04

  - 0 +
• 
  Oops thats my bad - sp_MSrepl_commands doesn't appear anywhere in your script. What I'd meant was filter out the sproc MSrepl_commands exactly as that, but you said that you'd added this to the filter but its not in the screenshot, I take it its beneath on that list but scrolled down?

  Trying to reproduce this anyway using a slightly modified version of the above but since i'm missing so much of the schema its proving difficult. If you want to take this offline and can send in a reproduction to Support that would be helpful.
  

  Russell D May 20, 2019 07:20

  - 0 +

Add comment

Please sign in to leave a comment.