-- Name: sp_MSincrementpublicationminautonosynclsn
-- Description: This is a helper procedure for updating a publication's
-- min. autonosync lsn to the "current" (from sp_replincrementlsn)
-- lsn of the publisher database.
-- Parameter: @publication sysname (mandatory)
-- Note: @publication is assumed to be validated and no error will be raised
-- if syspublications does not exist.
-- Security: This is an internal system procedure.
create procedure sys.sp_MSincrementpublicationminautonosynclsn (
@publication sysname
)
as
begin
set nocount on
declare @retcode int,
@pubid int,
@min_artid int,
@min_autonosync_lsn binary(10),
@distributor sysname,
@distribdb sysname,
@publisher sysname,
@dbname sysname,
@distproc nvarchar(768),
@min_autonosync_lsn_str nvarchar(30),
@distver int
select @retcode = 0
if object_id(N'dbo.syspublications') is not null
and object_id(N'dbo.sysextendedarticlesview') is not null
begin
select top 1 @pubid = sysp.pubid,
@min_autonosync_lsn = sysp.min_autonosync_lsn,
@min_artid = sysea.artid
from dbo.syspublications sysp
join dbo.sysextendedarticlesview sysea
on sysp.pubid = sysea.pubid
where sysp.name = @publication
order by sysea.artid asc
-- if we have an article
-- and (this is the first time we are setting min_autonosync_lsn
-- or this is our first article)
-- then
-- we need to post a no-op cmd so that we
-- ensure we will always know if that backup is
-- valid and that cleanup has not removed cmds
if @min_artid is not NULL
and (@min_autonosync_lsn is NULL
or not exists(select *
from dbo.sysextendedarticlesview
where pubid = @pubid
and artid > @min_artid))
begin
exec @retcode = sys.sp_replpostcmd 0, @pubid, @min_artid, 1, N'-- INITIALIZE WITH BACKUP'
if @@error <> 0 or @retcode <> 0 begin select @retcode = 1 goto Failure end
end
end
exec @retcode = sys.sp_replincrementlsn_internal @min_autonosync_lsn output
if @@error <> 0 or @retcode <> 0 begin select @retcode = 1 goto Failure end
if object_id('dbo.syspublications') is not null
begin
update dbo.syspublications
set min_autonosync_lsn = @min_autonosync_lsn
where name = @publication
if @@error <> 0 or @retcode <> 0 begin select @retcode = 1 goto Failure end
end
-- Update distributor size autonosync lsn value
-- this is done to make sure that we're not cleaning up commands when there are no subscriptions
-- for init from backup publications
SELECT @publisher = publishingservername()
EXEC @retcode = sys.sp_MSrepl_getdistributorinfo @publisher = @publisher,
@rpcsrvname = @distributor OUTPUT,
@distribdb = @distribdb OUTPUT,
@version = @distver OUTPUT
if @@error <> 0 or @retcode <> 0 begin select @retcode = 1 goto Failure end
if (@distver > 0x090007FF)
BEGIN
SELECT @dbname = DB_NAME()
SELECT @distproc = QUOTENAME(RTRIM(@distributor)) + '.'
+ QUOTENAME(@distribdb) + '.dbo.sp_MSchange_publication'
SELECT @min_autonosync_lsn_str = CONVERT(nvarchar(30), @min_autonosync_lsn)
EXECUTE @retcode = @distproc
@publisher = @publisher,
@publisher_db = @dbname,
@publication = @publication,
@property = N'min_autonosync_lsn',
@value = @min_autonosync_lsn_str
END
Failure:
return @retcode
end