June 4, 2012

sp_MStrypurgingoldsnapshotdeliveryprogress (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_MStrypurgingoldsnapshotdeliveryprogress()

MetaData:

 --   
-- 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'<MergePubId>:'

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

No comments:

Post a Comment

Total Pageviews