June 8, 2012

sp_resetsnapshotdeliveryprogress (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_resetsnapshotdeliveryprogress(int @verbose_level
, nvarchar @drop_table)

MetaData:

 --   
-- Name: sp_resetsnapshotdeliveryprogress
--
-- Description: This procedure removes all rows (if @drop_table = 'false')
-- in the MSsnapshotdeliveryprogress table (if it exists). This
-- will effectively wipes out all memory of any previous
-- progress that any snapshot delivery processes had made to
-- the subscriber database.
--
-- Notes: 1) This procedure should be called at the subscriber database.
-- 2) This procedure will try to acquire the
-- 'snapshot_delivery_in_progress_<dbname>' application lock in
-- exclusive mode prior to truncating (or drop)
-- the MSsnapshotdeliveryprogress table. It will raise an error if
-- it cannot acquire the lock in 5 seconds and the specified
-- @verbose_level >= 1.
--
-- Parameter: @verbose_level int (optional, default 1)
-- @drop_table nvarchar(5) (optional, default N'false')
-- - specifies whether to drop the progress table or just
-- truncate the table
--
-- 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_resetsnapshotdeliveryprogress
@verbose_level int = 1,
@drop_table nvarchar(5) = N'false'
as
begin
set nocount on
declare @retcode int
declare @lock_acquired int
declare @lock_resource nvarchar(255)
declare @cursor_allocated bit
declare @cursor_opened bit
declare @pubidprefix nvarchar(100)
declare @pubid uniqueidentifier

select @retcode = 0
select @lock_acquired = 0
select @lock_resource = N'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_MSreplcheck_subscribe
if @retcode <> 0 or @@error <> 0
begin
select @retcode = 1
goto Failure
end

-- Parameter validation
select @drop_table = lower(@drop_table collate SQL_Latin1_General_CP1_CS_AS)
if @drop_table not in (N'true', N'false')
begin
raiserror (14148, 16,-1, '@drop_table')
select @retcode = 1
goto Failure
end

-- Wiping out the content of the MSsnapshotdeliveryprogress table
-- can disrupt snapshots that are being applied to this subscription
-- database. Try to acquire the snapshot-delivery-in-progress application
-- lock in exclusive mode prior to wiping out the table. Note that the
-- the distribution/merge agent will acquire the same application lock in
-- shared mode while a snapshot is being applied. Don't try to wait
-- for the lock for too long as other snapshot delivery processes may be
-- blocked by our waiting. In effect, we are saying that it is OK to
-- starve the process that is trying to truncate the progress table.

exec @retcode = sys.sp_getapplock @Resource = @lock_resource,
@LockMode = N'Exclusive',
@LockOwner = N'Session',
@LockTimeout = 5000, -- 5 seconds
@DbPrincipal = N'db_owner'

if @@error <> 0
begin
select @retcode = 1
goto Failure
end

if @retcode < 0
begin
if @retcode = -1 and @verbose_level >= 1
begin
raiserror(21514,16,-1)
select @retcode = 1
end
else
begin
select @retcode = 0
end
goto Failure
end

select @retcode = 0, @lock_acquired = 1

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

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

if @drop_table = N'false'
begin
truncate table dbo.MSsnapshotdeliveryprogress
if @@error <> 0
begin
select @retcode = 1
goto Failure
end
end
else
begin
drop table dbo.MSsnapshotdeliveryprogress
if @@error <> 0
begin
select @retcode = 1
goto Failure
end
end
end
if @retcode <> 0 or @@error <> 0
begin
select @retcode = 1
goto Failure
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 = @lock_resource,
@LockOwner = N'Session',
@DbPrincipal = N'db_owner'
if @@error <> 0 or @retcode < 0
begin
select @retcode = 1
end
end
return @retcode
end

No comments:

Post a Comment

Total Pageviews