May 14, 2012

sp_MSget_last_transaction (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_MSget_last_transaction(int @publisher_id
, nvarchar @publisher_db
, nvarchar @publisher
, bit @for_truncate)

MetaData:

 CREATE PROCEDURE sys.sp_MSget_last_transaction  
(
@publisher_id int = NULL,
@publisher_db sysname,
@publisher sysname = NULL,
@max_xact_seqno varbinary(16) = NULL output
,@for_truncate bit = 0
)
AS
begin
declare @publisher_database_id int
declare @max_xact_id varbinary(16)
declare @sync_bit int
declare @sync_with_backup bit

set nocount on

--
-- security check
-- only db_owner can execute this
--
if (is_member ('db_owner') != 1)
begin
raiserror(14260, 16, -1)
return (1)
end

SELECT @sync_bit = 32

if @publisher_id is NULL
select @publisher_id = srvid from master.dbo.sysservers where
UPPER(srvname) = UPPER(@publisher)

-- Get publisher database id.
SELECT @publisher_database_id = id from MSpublisher_databases where publisher_id = @publisher_id and
publisher_db = @publisher_db

if exists ( select * from master.dbo.sysdatabases where
name = db_name() and
category & @sync_bit = 0)
select @sync_with_backup = 0
else
select @sync_with_backup = 1



if @for_truncate = 0
begin
select top 1 @max_xact_id = rt.xact_id, @max_xact_seqno = rt.xact_seqno
from
MSrepl_transactions rt
where
rt.publisher_database_id = @publisher_database_id and
not xact_id = 0x0
order by xact_seqno desc
end
-- If (1) requesting truncate lsn (distbackuplsn), (2) sync with backup is set
-- query the values from MSrepl_backup_lsn
else if @sync_with_backup = 1
begin
-- Get the last backed up lsn if available.
select top 1 @max_xact_id = valid_xact_id, @max_xact_seqno = valid_xact_seqno
from
MSrepl_backup_lsns
where
publisher_database_id = @publisher_database_id
end

-- If @publisher is not null, we are calling this sp from sp_replrestart
-- Don't return result set.
if @publisher is null
select @max_xact_id, @max_xact_seqno, @publisher_database_id
-- Don't return any result when requsting a truncate lsn and
-- the database is not in 'sync with backup' mode, which signal the
-- distribution agent to use last dist lsn to call sp_repldone.
where not (@sync_with_backup = 0 and @for_truncate = 1)
end

No comments:

Post a Comment

Total Pageviews