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 sys.sp_MStran_is_snapshot_required(
nvarchar @publisher,
nvarchar @publisher_db,
nvarchar @publication,
nvarchar @subscriber,
nvarchar @subscriber_db,
int @subscription_type,
bit @run_at_distributor,
varbinary @last_xact_seqno,
varbinary @subscription_guid,
varbinary @subid)
create procedure sys.sp_MStran_is_snapshot_required @publisher sysname, @publisher_db sysname, @publication sysname, @subscriber sysname, @subscriber_db sysname, @subscription_type int, @run_at_distributor bit = 0, -- 0 running at subscriber 1 running at distributor @last_xact_seqno varbinary(16) = null, -- must be set when @run_at_distributor = 1 @subscription_guid varbinary(16) = null, -- must be set when @run_at_distributor = 1 @subid varbinary(16) = null -- must be set when @run_at_distributor = 1 and @subscription_type = 2 (anonymous) as begin set nocount on -- constants declare @INACTIVE tinyint declare @SUBSCRIBED tinyint declare @ACTIVE tinyint declare @INITIATE tinyint declare @PUSH tinyint declare @PULL tinyint declare @ANONYMOUS tinyint declare @SYNC_TYPE_NONE tinyint declare @NOT_NEEDED tinyint declare @NEEDED tinyint declare @UNKNOWN tinyint declare @TRAN_PUB tinyint declare @SNAPSHOT_PUB tinyint declare @SNAPSHOT_BIT varbinary(16) declare @SYNCTRAN_TYPE int declare @READ_ONLY tinyint declare @CONCURRENT tinyint declare @CONCURRENT_C tinyint -- local vars declare @retcode int declare @publisher_name sysname declare @publisher_database_id int declare @pub_type int declare @sub_count int declare @pub_id int declare @agent_id int declare @publication_id int declare @sync_type int declare @allow_anon bit declare @immediate_sync bit declare @status int declare @transaction_timestamp varbinary(16) declare @dist_sub_guid varbinary(16) declare @max_xact_seqno varbinary(16) declare @num_non_active int declare @max_sub_seqno varbinary(16) declare @min_sub_seqno varbinary(16) declare @max_pub_seqno varbinary(16) declare @is_needed int -- flag to be selected prior to exit -- 0 not needed 1 needed 2 unknown select @INACTIVE = 0 select @SUBSCRIBED = 1 select @ACTIVE = 2 select @INITIATE = 3 select @PUSH = 0 select @PULL = 1 select @ANONYMOUS = 2 select @SYNC_TYPE_NONE = 2 select @NOT_NEEDED = 0 select @NEEDED = 1 select @UNKNOWN = 2 select @TRAN_PUB = 0 select @SNAPSHOT_PUB = 1 select @SNAPSHOT_BIT = 0x80000000 select @SYNCTRAN_TYPE = @SNAPSHOT_BIT | 9 select @READ_ONLY = 0 select @CONCURRENT = 3 select @CONCURRENT_C = 4 select @retcode = 0 select @publisher_name = null select @publisher_database_id = null select @pub_type = null select @sub_count = 0 select @pub_id = null select @agent_id = null select @publication_id = null select @sync_type = null select @allow_anon = 0 select @immediate_sync = null select @status = null select @transaction_timestamp = null select @dist_sub_guid = null select @max_xact_seqno = null select @num_non_active = 0 select @max_sub_seqno = 0x00 select @min_sub_seqno = 0x00 select @max_pub_seqno = 0x00 select @is_needed = @UNKNOWN -- Security check: db_owner, sysadmin if proc is called at the subscriber; -- PAL at the distributor if @run_at_distributor = 0 begin exec @retcode = sys.sp_MSreplcheck_subscribe if @@error<>0 or @retcode<>0 begin return 1 end end -- Common Parameter Validation if @publisher is null begin -- The parameter %s cannot be NULL. raiserror (14043, 16, -1, '@publisher', 'sp_MStran_is_snapshot_required') return (1) end if @publisher_db is null begin -- The parameter %s cannot be NULL. raiserror (14043, 16, -1, '@publisher_db', 'sp_MStran_is_snapshot_required') return (1) end if @publication is null begin -- The parameter %s cannot be NULL. raiserror (14043, 16, -1, '@publication', 'sp_MStran_is_snapshot_required') return (1) end if @subscription_type not in (@PUSH, @PULL, @ANONYMOUS) begin raiserror(20587, 16, -1, @subscription_type, 'sp_MStran_is_snapshot_required') return (1) end -- Perform subscriber side queries if @run_at_distributor = 0 begin -- push cases do not have this table. instead of checking for push we will just check if the table -- exists then retrieve the publication type. We can use this for one special case, SNAPSHOT PUBS. if object_id ('MSsubscription_properties') is not null begin select @pub_type = publication_type from dbo.MSsubscription_properties mssp where mssp.publisher = @publisher and mssp.publisher_db = @publisher_db and mssp.publication = @publication end -- check for table if object_id ('MSreplication_subscriptions') is not null begin -- Retrieve time stamp and publication information select @publisher_name = msrs.publisher, @transaction_timestamp = msrs.transaction_timestamp, @subscription_guid = msrs.subscription_guid, @subid = msrs.subid from dbo.MSreplication_subscriptions msrs where msrs.publisher = @publisher and msrs.publisher_db = @publisher_db and (msrs.publication = @publication or (msrs.publication = '' -- this 'or' case is here for subs on pubs with NON-independednt agents and @pub_type = NULL and msrs.independent_agent = 0)) and msrs.subscription_type = @subscription_type end else begin select @publisher_name = null end -- check for valid publisher name if @publisher_name is null begin -- if the subscription is push and the table does not exist it could mean 1 of two things. -- 1-Snapshot not applied yet or 2-Subscription not found. Either will mean unknown for push. -- We can not say sub not found because what if the distributor knows about the sub (case 1). -- or -- If it is push then it is also possible that the table exists but the subscriber -- has not sync'd or failed durring a sync and has the table but no entries. if @subscription_type = @PUSH begin -- set the @subscription_guid to 0x00 which indicate that the tables -- did not exist when the proc was called at the subscriber side select @transaction_timestamp = 0x00 select @subscription_guid = 0x00 select @subid = 0x00 -- set to unknown and then return select @is_needed = @UNKNOWN goto Results_Handler end -- for any other case (PULL + ANON) this means... -- The subscription on the Subscriber does not exist. raiserror (20017, 16, -1) return (1) end -- Take a look at the time stamp and subscription_guid to see if a snapshot is needed. On reinit -- or initial subscription creation the tran_timestamp is set to 0x00 and subscription_guid is set -- to null. When these cases are met we can assume this means that the snapshot must be applied. -- or -- For anon subscriber side reinit case only the timestamp is reset so we need to check for this case -- or -- check to see if this is a snapshot publication. if so utomatically return @NEEDED since snapshot -- pubs will always need to retrieve a new snapshot from the publisher side. if (@transaction_timestamp = 0x00 and @subscription_guid is NULL) or (@subscription_type = @ANONYMOUS and @transaction_timestamp = 0x00 and @subscription_guid is not NULL) or @pub_type = @SNAPSHOT_PUB begin select @is_needed = @NEEDED end -- Any push case should be checked at the distributor side as well for further info -- but according to the information at the subscriber no snapshot is currently needed -- and -- for pull and anon case where the time stamp is not 0x00 then we do not need a snapshot else if @transaction_timestamp <> 0x00 begin select @is_needed = @NOT_NEEDED end -- any other case will be set to unknown since they are not handled and will have to be checked at distributor else begin select @is_needed = @UNKNOWN end goto Results_Handler end -- Perform distributor side queries else begin if object_id('MSpublications') is null begin -- Must be run on distributor. raiserror(21454, 16, -1) return (1) end -- retrieve publication id etc. select @pub_id = msp.publisher_id, @pub_type = msp.publication_type, @publication_id = msp.publication_id, @allow_anon = msp.allow_anonymous, @immediate_sync = msp.immediate_sync from dbo.MSpublications msp, master.dbo.sysservers sv where sv.srvname = @publisher and msp.publisher_id = sv.srvid and (msp.publication = @publication or (@publication is null or @publication = N'' or upper(@publication) = N'ALL') and msp.independent_agent = 0) and msp.publisher_db = @publisher_db and msp.publication_type in (@TRAN_PUB, @SNAPSHOT_PUB) -- check for valid pubid if @pub_id is null begin -- The publication '%s' does not exist. raiserror(20026, 16, -1, @publication) return (1) end -- Distributor side specific parameter validation -- if the subscription is anon and we do not allow it then we know we can't find it if @subscription_type = @ANONYMOUS and @allow_anon = 0 begin -- The subscription could not be found. raiserror(20021, 16, -1) return (1) end -- if the subscription is anon and guids/ids are null then we are not sure -- because it could be the subscription was created but never initialized if @subscription_type = @ANONYMOUS and @subscription_guid is null and @subid is null begin select @is_needed = @UNKNOWN goto Results_Handler end -- if it's an immediate sync publication then we will require the subscription guid if @immediate_sync = 1 and @subscription_guid is null begin -- The parameter %s cannot be NULL. raiserror (14043, 16, -1, '@subscription_guid', 'sp_MStran_is_snapshot_required') return (1) end if @subscriber is null begin -- The parameter %s cannot be NULL. raiserror (14043, 16, -1, '@subscriber', 'sp_MStran_is_snapshot_required') return (1) end if @subscriber_db is null begin -- The parameter %s cannot be NULL. raiserror (14043, 16, -1, '@subscriber_db', 'sp_MStran_is_snapshot_required') return (1) end -- set last seqno if it was passed in as null if @last_xact_seqno is null begin select @last_xact_seqno = 0x00 end -- if it is a pull or push and publication is not immediate then attempt -- to retrieve the information from MSsubscriptions status column which is -- only a valid value for the pull/push non-immediate_sync cases -- -- we also skip over pub_type = SNAP_PUB because we have an optimization for -- it following this check. -- -- NOTE: -- If the result of this does not hit the snapshot is NEEDED section then -- we will follow this with more system table checks to ensure we have -- the correct value. This is just to avoid extra processing if possible if @subscription_type in (@PULL, @PUSH) and @immediate_sync = 0 and @pub_type <> @SNAPSHOT_PUB begin -- Attempt to retrieve information for well known subscriptions select @status = min(mss.status), @sub_count = count(mss.status), @sync_type = max(mss.sync_type) from dbo.MSsubscriptions mss, master.dbo.sysservers sv where sv.srvname = @subscriber and mss.subscriber_id = sv.srvid and mss.subscriber_db = @subscriber_db and mss.publisher_id = @pub_id and mss.publisher_db = @publisher_db and mss.publication_id = @publication_id and mss.subscription_type = @subscription_type -- subcount is > 0 and status is not null if @sub_count = 0 or @status is null begin -- The subscription could not be found. raiserror(20021, 16, -1) return (1) end -- nosync subscriptions will never be reinitialized and never need a snapshot if @sync_type = @SYNC_TYPE_NONE begin select @is_needed = @NOT_NEEDED goto Results_Handler end -- any status that is inactive or subscribed will need the snapshot -- also -- if this is a snapshot publication automatically return @NEEDED since -- snapshot pubs will always retrieve a snapshot from the publisher side if @status in (@INACTIVE, @SUBSCRIBED) or @pub_type = @SNAPSHOT_PUB begin select @is_needed = @NEEDED goto Results_Handler end end -- For the push pull case retrieve the value from dist agent if @subscription_type in (@PULL, @PUSH) begin select @publisher_database_id = msda.publisher_database_id, @dist_sub_guid = msda.subscription_guid, @agent_id = msda.id from dbo.MSdistribution_agents msda, master.dbo.sysservers sv where sv.srvname = @subscriber and msda.subscriber_id = sv.srvid and msda.subscriber_db = @subscriber_db and msda.publisher_id = @pub_id and msda.publisher_db = @publisher_db and msda.publication in (@publication, N'ALL') and msda.subscription_type = @subscription_type end -- for anon use the virtual_agent_id to retrieve the value else if @subscription_type = @ANONYMOUS begin -- if the last tran sequence number is 0x00 then use virtual id to grab the actual agent id -- if not then we should be using the anonymous_agent_id (see sp_MSget_repl_cmds_anonymous) if @last_xact_seqno = 0x00 begin select @publisher_database_id = msda2.publisher_database_id, @dist_sub_guid = msda2.subscription_guid, @agent_id = msda2.id from dbo.MSdistribution_agents msda1, dbo.MSdistribution_agents msda2 where msda1.subscription_guid = @subid and msda2.id = msda1.virtual_agent_id end else begin select @publisher_database_id = msda2.publisher_database_id, @dist_sub_guid = msda2.subscription_guid, @agent_id = msda2.id from dbo.MSdistribution_agents msda1, dbo.MSdistribution_agents msda2 where msda1.subscription_guid = @subid and msda2.id = msda1.anonymous_agent_id end end -- if for whatever reason we have another subscription type -- (this should never actually happen) let's set the vals to null else begin select @publisher_database_id = null select @dist_sub_guid = null select @agent_id = null end -- make sure values are valid if @publisher_database_id is null or @agent_id is null or @dist_sub_guid is null begin -- The subscription could not be found. raiserror(20021, 16, -1) return (1) end -- By now, we should be in "a" distribution database so regardless -- of whether the distribution database procedure sp_MScheck_pull_access -- has been moved into the resource database. exec @retcode = sys.sp_MScheck_pull_access @agent_id = @agent_id, @agent_type = 0 -- distribution agent if @@error <> 0 or @retcode <> 0 return (1) -- now that we know the subscription exists let's check to see if -- it is a snap pub. if so snap pubs always need a new snapshot. if @pub_type = @SNAPSHOT_PUB begin select @is_needed = @NEEDED goto Results_Handler end -- retrieve sync type select @sync_type = max(sync_type) from dbo.MSsubscriptions where agent_id = @agent_id -- nosync subscriptions will never be reinitialized and never need a snapshot if @sync_type = @SYNC_TYPE_NONE begin select @is_needed = @NOT_NEEDED goto Results_Handler end -- if this agent is servicing any inactive concurrent sync articles then say -- no snapshot needed. The concurrent sync article will be activated when the -- SYNCDONE token is written to the distribution db by the logreader at this -- point, all log records associated with the sync will be present in MSrepl_commands -- (see sp_MSget_repl_commands for more details) if exists(select * from MSsubscriptions s join MSpublications p on (s.publisher_id = p.publisher_id and s.publisher_db = p.publisher_db and s.publication_id = p.publication_id) where s.agent_id = @agent_id and s.status = @INITIATE and (p.sync_method = @CONCURRENT or p.sync_method = @CONCURRENT_C)) begin select @is_needed = @NOT_NEEDED goto Results_Handler end -- if it is anonymous and the @subid is 0x00 then that means that the subscription has -- just been created but has never been initialized. Unfortunately we have no way of -- knowing unless that snapshot is applied or we query the subscriber side again. if @subscription_type = @ANONYMOUS and @subid = 0x00 begin select @is_needed = @UNKNOWN goto Results_Handler end -- for push and pull if the guids are equal at the sub and dist then we will most -- likly have to check the replcommands table for the snapshot app needed info. We -- say most likely because there are some optimizations inside to avoid hitting -- replcommands if at all possible. -- -- in the anonymous case, if we are here we will always need to come in and -- perform extra checks because the subscription guid for anon is not reliable else if @dist_sub_guid = @subscription_guid or @subscription_type = @ANONYMOUS begin -- retrieve some info to check if we are done with snapshot transactions select @num_non_active = sum(case when status <> @ACTIVE then 1 else 0 end), @max_sub_seqno = max(subscription_seqno), @min_sub_seqno = min(subscription_seqno), @max_pub_seqno = max(publisher_seqno) from MSsubscriptions where agent_id = @agent_id -- if there are any non-active subscriptions then need a snapshot if @num_non_active > 0 begin select @is_needed = @NEEDED goto Results_Handler end -- if these conditions are met then we know we do not need a snapshot and can exit if @last_xact_seqno > @max_sub_seqno and @last_xact_seqno > @max_pub_seqno begin select @is_needed = @NOT_NEEDED goto Results_Handler end -- Get the last xact_seqno on the pub db FIRST. It will -- be used as the upper bound for differnt queries. We have to do -- this to prevent transactions on new or changed subscriptions or -- with new orignator_id being skipped eigher by preselected query or -- preselected originator_id. -- Have to have readpast here to prevent the query be blocked by logreader -- (even before the first row to the dist agent). -- -- Note: DO NOT consider distcmds inserted for a concurrent snapshot -- These represent 'forward in time' xacts. Skipping past these xacts -- can cause lost transactions! -- -- Note2: consider adding new MSrepl_commands idx with keys: -- publisher_database_id, command_id, type, xact_seqno -- -- Note3: might be able to use replpostcmd + a LSN mapping token to -- allow SS agent to post sync cmds to log instead of dist db. -- select @max_xact_seqno = max(xact_seqno) from MSrepl_commands with (READPAST) where publisher_database_id = @publisher_database_id and command_id = 1 and type <> -2147483611 -- If there's nothing to do set not needed and move on to results if @max_xact_seqno = @last_xact_seqno begin select @is_needed = @NOT_NEEDED goto Results_Handler end -- check MSrepl_commands to see if we have snapshot commands waiting for us -- Consider: -- We may want to use a use a dirty read since if the snapshot is generating/logreader is -- is picking up snap commands we can make our assumptions that the snapshot is needed. -- for this assumption to be true it doesn't matter if anything by the logreader is -- rolled back because of an error etc. We will still need a snapshot later. if exists (select msrc.* from MSrepl_commands msrc, MSsubscriptions mss where mss.agent_id = @agent_id and mss.status = @ACTIVE -- make sure only to scan the active subs and mss.publisher_database_id = @publisher_database_id and msrc.publisher_database_id = mss.publisher_database_id and msrc.article_id = mss.article_id and msrc.xact_seqno > @last_xact_seqno -- lower bound and msrc.xact_seqno >= @min_sub_seqno -- this is here in case @last_xact_seqno is 0x00 and msrc.xact_seqno <= @max_xact_seqno -- upper bound and (msrc.type & @SNAPSHOT_BIT) = @SNAPSHOT_BIT -- make sure they are snapshot commands -- 1. filter out the snapshot transactions that were inserted later that is not -- the subscription's snapshot transaction -- 2. filter out trigger generation command for non synctran subscription. and ( (msrc.type & @SNAPSHOT_BIT) = @SNAPSHOT_BIT and msrc.xact_seqno >= mss.subscription_seqno and msrc.xact_seqno <= mss.ss_cplt_seqno and ( msrc.type <> @SYNCTRAN_TYPE or mss.update_mode <> @READ_ONLY ) )) begin select @is_needed = @NEEDED end else begin select @is_needed = @NOT_NEEDED end goto Results_Handler end else begin -- all else needs a snapshot select @is_needed = @NEEDED goto Results_Handler end end Results_Handler: select "is_snapshot_required" = @is_needed, "last_xact_seqno" = @transaction_timestamp, "subscription_guid" = @subscription_guid, "subid" = @subid return 0 end
