Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MStrypurgingoldsnapshotdeliveryprogress

  No additional text.


Syntax

-- Name: sp_MStrypurgingoldsnapshotdeliveryprogress

-- Description: This function removes snapshot sessions in
--              MSsnapshotdeliveryprogress containing progress tokens that
--              are too old (3 days or older) provided that it can acquire the
--              'snapshot_delivery_in_progress_' + db_name() application lock
--              in exclusive mode immediately. This is to ensure that the
--              the removal of progress tokens will not disrupt any on-going
--              snapshot delivery processes.

-- Parameter: none

-- Notes: This procedure is normally invoked by the distribution/merge agent
--        at the begining of a snapshot delivery session although the database
--        adminstrator can invoke this procedure to actively purge old entries
--        in sp_MSsnapshotdeliveryprogress. Since this procedure
--        is meant to provide a best-effort mechanism for cleaning up old
--        sessions in MSsnapshotdeliveryprogress, it will not raise an error
--        if it fails to acquire the 'snapshot_in_progress_' + db_name()
--        application lock.

-- Returns: 0 - succeeded
--          1 - failed

-- Security: Execute permission of this procedure is granted to public;
--           procedural security check will be performed to make sure
--           that the caller is either a db_owner of the current database
--           or a sysadmin.

create procedure sys.sp_MStrypurgingoldsnapshotdeliveryprogress
as
begin
    set nocount on
    declare @retcode int
    declare @resource nvarchar(255)
    declare @lock_acquired int
    declare @cursor_allocated bit
    declare @cursor_opened bit
    declare @pubidprefix nvarchar(100)
    declare @pubid uniqueidentifier
    declare @cutofftime datetime
    select @retcode = 0

    select @cutofftime = datediff(day, 3, getdate())
    if @@error<>0 or @retcode<>0 begin select @retcode=1 goto Failure end

    select @lock_acquired = 0
    exec @retcode = sys.sp_MSreplcheck_subscribe
    if @retcode <> 0 or @@error <> 0
    begin
        select @retcode = 1
        goto Failure
    end

    select @resource = 'snapshot_delivery_in_progress_' +
        db_name() collate database_default
    select @cursor_allocated = 0
    select @cursor_opened = 0
    select @pubidprefix = N':'

    exec @retcode = sys.sp_getapplock @Resource = @resource,
                                      @LockMode = 'Exclusive',
                                      @LockOwner = 'Session',
                                      @LockTimeout = 0,
                                      @DbPrincipal = N'db_owner'
    -- Bail on critical errors from sp_getapplock
    if @retcode < -1 or @@error <> 0
    begin
        select @retcode = 1
        goto Failure
    end

    -- Lock request timeout because a snapshot is being delivered; bail
    -- without raising an error
    if @retcode = -1
    begin
        select @retcode = 0
        goto Failure
    end
    select @retcode = 0
    select @lock_acquired = 1

    -- Delete all sessions containing tokens that are more than 3 days old
    if object_id('dbo.MSsnapshotdeliveryprogress') is not null
    begin
        -- Eliminating merge snapshot progress requires additional cleanup in
        -- the merge meta-data tables so do it separately
        declare hpubid cursor local fast_forward for
            select convert(uniqueidentifier, right(progress_token, len(progress_token) - len(@pubidprefix)))
              from dbo.MSsnapshotdeliveryprogress
             where left(progress_token, len(@pubidprefix)) = @pubidprefix
               and session_token in
                    (select session_token
                       from dbo.MSsnapshotdeliveryprogress sdp_inner
                      where progress_timestamp < @cutofftime)
        if @@error<>0 or @retcode<>0 begin select @retcode=1 goto Failure end
        select @cursor_allocated = 1

        open hpubid
        if @@error<>0 or @retcode<>0 begin select @retcode=1 goto Failure end
        select @cursor_opened = 1

        fetch hpubid into @pubid
        while (@@fetch_status <> -1)
        begin

            exec @retcode = sys.sp_MSpurgepartialmergesnapshot
                    @pubid = @pubid
            if @@error<>0 or @retcode<>0 begin select @retcode=1 goto Failure end
            fetch hpubid into @pubid
        end

        close hpubid
        if @@error<>0 or @retcode<>0 begin select @retcode=1 goto Failure end
        select @cursor_opened = 0

        deallocate hpubid
        if @@error<>0 or @retcode<>0 begin select @retcode=1 goto Failure end
        select @cursor_allocated = 0

        delete dbo.MSsnapshotdeliveryprogress
        where session_token in
            (select session_token
               from dbo.MSsnapshotdeliveryprogress sdp_inner
              where progress_timestamp < @cutofftime)
        if @@error <> 0
        begin
            select @retcode = 1
            goto Failure
        end
    end

Failure:
    if @cursor_opened = 1
    begin
        close hpubid
    end

    if @cursor_allocated = 1
    begin
        deallocate hpubid
    end

    if @lock_acquired = 1
    begin
        exec @retcode = sys.sp_releaseapplock @Resource = @resource,
                                              @LockOwner = 'Session',
                                              @DbPrincipal = N'db_owner'

        if @retcode < 0 or @@error <> 0
        begin
            select @retcode = 1
        end
    end
    return @retcode
end

 
Last revision 2008RTM
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