Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_replrestart

 

Used by transactional replication during backup and restore so that the replicated data at the Distributor is synchronized with data at the Publisher. This stored procedure is executed at the Publisher on the publication database. Run the sp_replrestart system stored procedure in the publication database without any parameters. This procedure forces replication to continue even if the distributor and some subscribers have data that the publisher no longer has.
Note Make sure that there is no incoming traffic to the publisher before you perform this step.
When you run the sp_replrestart system stored procedure, SQL Server 2000 fills the transaction log of the publication database with "No Operation" commands until the log sequence number (LSN) in the transaction log is greater than the value of the transaction sequence number (max xact_seqno) that was sent to the distribution database before the publisher failed. This is a procedure in the mssqlsystemresource database.




Syntax
create procedure sys.sp_replrestart
AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */
    declare @retcode int
        ,@lsn binary(10)
        ,@dist_lsn binary(10)
        ,@distributor sysname
        ,@distribdb sysname
        ,@distproc nvarchar(4000)
        ,@dbname sysname
        ,@publisher sysname
    /*
    ** Initializations
    */
    select @retcode = 0
    select @dbname = db_name()

    /*
    ** Security
    */
    exec @retcode = sys.sp_MSreplcheck_publish
    if @@ERROR <> 0 or @retcode <> 0
        return(1)

    -- Make sure the database is published.
    IF (SELECT category & 1
          FROM master.dbo.sysdatabases
         WHERE name = @dbname collate database_default) = 0
    BEGIN
        RAISERROR (14013, 16, -1)
        RETURN (1)
    END

    -- Make sure that the log reader is not running
    -- Use 0 so that it will not hold the repl proc structure (the lock).
    exec @retcode = sys.sp_replcmds 0
    if @@ERROR <> 0 or @retcode <> 0
    begin
        RAISERROR (20610, 16, -1, 'sp_replrestart')
        return(1)
    end

    /*
    ** Get distribution server information for remote RPC call.
    */

    EXEC @retcode = sys.sp_MSrepl_getdistributorinfo @rpcsrvname = @distributor OUTPUT,
                                       @distribdb = @distribdb OUTPUT

    IF @@ERROR <> 0 OR  @retcode <> 0
    BEGIN
        RAISERROR (14071, 16, -1)
        RETURN (1)
    END

    -- Get max dist lsn
    SELECT @distproc = QUOTENAME(RTRIM(@distributor)) + '.' + QUOTENAME(@distribdb) + '.dbo.sp_MSget_last_transaction'
    			,@publisher = publishingservername()
    EXECUTE @retcode = @distproc
        @publisher = @publisher,
        @publisher_db = @dbname,
        @max_xact_seqno = @dist_lsn output
    IF @@ERROR <> 0 or @retcode <> 0
        return 1

    if @dist_lsn is null
        set @dist_lsn = 0x0

    begin tran
	save tran sp_replrestart

    -- To safeguard the case when the logreader is started after the check later
    -- use a tran to prevent the logreader from picking up the new lsns
    while 1 = 1
    begin
        -- Get publisher's lsn
        EXEC @retcode = sys.sp_replincrementlsn_internal @lsn OUTPUT
        IF @@ERROR <> 0 or @retcode <> 0
            goto UNDO

        if @lsn >= @dist_lsn
            break
    end

	--on the other hand, after publisher db is restored, if users retrieves what's been delivered to subscriber already
	--and use it to bring publisher up to more current state, after such *compensation* publisher log may be ahead of @dist_lsn
	--in which case we should manully update MSrepl_transactions table so that it does not scan the compensating portion of log
	if(@lsn > @dist_lsn)
	begin
	    SELECT @distproc = QUOTENAME(RTRIM(@distributor)) + '.' + QUOTENAME(@distribdb) + '.sys.sp_MSreset_transaction'
	    EXECUTE @retcode = @distproc
	        @publisher = @publisher,
	        @publisher_db = @dbname,
	        @xact_seqno = @lsn
	    IF @@ERROR <> 0 or @retcode <> 0
	        GOTO UNDO

	end

    /* Mark the new starting point of the replication.*/
    exec @retcode = sys.sp_repldone NULL, NULL, 0, 0, 1
    IF @@ERROR <> 0 or @retcode <> 0
        GOTO UNDO

    /* release our hold on the db as logreader */
    EXEC @retcode = sys.sp_replflush
    IF @@ERROR <> 0 or @retcode <> 0
        GOTO UNDO

    commit tran
    return 0

UNDO:
    if @@trancount <> 0
    begin
        rollback tran sp_replrestart
        commit tran
    end
    return 1

 
Last revision 2008RTM
See also

  sp_MSdrop_repltran (Procedure)
sp_MSget_last_transaction (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash