mmediate_sync = 1 and @independent_agent != 1 begin raiserror(21022, 16, -1) return (1) end -- Parameter Check: @allow_anonymous -- The publication must support immediate_sync to support anonymous. if @allow_anonymous = 1 and @immediate_sync != 1 begin raiserror(20011, 16, -1) return (1) end -- Make sure publication does not already exist if exists (select * from MSpublications where publication = @publication and publisher_id = @publisher_id and publisher_db = @publisher_db) begin if @thirdparty_flag = 1 begin raiserror(14016, 16, -1, @publication) return (1) end else begin exec @retcode = dbo.sp_MSdrop_publication @publisher = @publisher, @publisher_db = @publisher_db, @publication = @publication if @@error <> 0 or @retcode <> 0 return (1) end end -- -- For MSMQ queue type - Distributor needs to support MSMQ 2.0 -- if (@queue_type = 1 and @allow_queued_tran = 1) begin -- -- Now we use xp_MSver to detect NT OS version -- MSMQ subscription only allowed for platforms that support MSMQ 2.0 -- version 5.0.2195 or higher -- create table #tosversion ( propid int, propname sysname collate database_default, value int, charvalue nvarchar(255) collate database_default) insert into #tosversion (propid, propname, value, charvalue) exec master.dbo.xp_msver N'WindowsVersion' declare @vervalue int ,@lobyte tinyint ,@hibyte tinyint ,@loword smallint ,@hiword smallint -- -- low order byte of low order word = OSmajor, high order byte of low order word = OSminor -- high order word = OSbuild -- select @vervalue = value from #tosversion where propname = N'WindowsVersion' select @loword = (@vervalue & 0xffff) ,@hiword = (@vervalue / 0x10000) & 0xffff select @lobyte = @loword & 0xff ,@hibyte = (@loword / 100) & 0xff drop table #tosversion -- -- check for OS major version -- if (@lobyte < 5) begin raiserror(21334, 16, 4, '2.0') return (1) end -- -- check for OS build version -- if (@lobyte = 5 and @hiword < 2195) begin raiserror(21334, 16, 5, '2.0') return (1) end end begin tran save tran MSadd_publication insert into MSpublications values (@publisher_id, @publisher_db, @publication, @publication_type, @thirdparty_flag, @independent_agent, @immediate_sync, @allow_push, @allow_pull, @allow_anonymous, @description, @vendor_name, @retention, @sync_method, @allow_subscription_copy, @thirdparty_options, @allow_queued_tran) if @@error <> 0 goto UNDO -- Enable the distribution cleanup agent if transactional or snapshot publicational if @publication_type = 0 or @publication_type = 1 begin select @agentname = name from msdb..sysjobs j, msdb..sysjobsteps s where j.job_id = s.job_id and j.category_id = 11 and s.database_name = db_name() exec @retcode = msdb.dbo.sp_update_job @job_name=@agentname, @enabled=1 if @@error <> 0 or @retcode <> 0 goto UNDO end -- Add snapshot and logreader agent -- Always add a non local snapshot agent. This is to cover the case -- when there's no SQLServerAgent job for the snapshot agent (For example, in Access). -- The agent entry is needed for initance check. -- sp_addpublication_snapshot will drop the entry and recreat it. exec @retcode = dbo.sp_MSadd_snapshot_agent @name = @snapshot_agent, @publisher = @publisher, @publisher_db = @publisher_db, @publication = @publication, @publication_type = @publication_type, @local_job = 0 if @@error <> 0 or @retcode <> 0 goto UNDO -- If null is passed in, we know that the agent is created already. (For SQL server). -- If not null is passed in, add the agents without creating local jobs (For third party). if @logreader_agent is not null begin exec @retcode = dbo.sp_MSadd_logreader_agent @name = @logreader_agent, @publisher = @publisher, @publisher_db = @publisher_db, @publication = @publication, @local_job = 0 if @@error <> 0 or @retcode <> 0 goto UNDO end -- If publisher_id, publisher_db pair is not in MSpublisher_databases then add it. This will be used -- to store a publisher_database_id in the MSrepl_transactions and MSrepl_commands table. if not exists (select * from MSpublisher_databases where publisher_id = @publisher_id and publisher_db = @publisher_db) begin insert into MSpublisher_databases (publisher_id, publisher_db) values (@publisher_id, @publisher_db) if @@error <> 0 goto UNDO insert into MSrepl_backup_lsns (publisher_database_id) values (@@identity) if @@error <> 0 goto UNDO end commit tran return(0) UNDO: if @@TRANCOUNT > 0 begin ROLLBACK TRAN MSadd_publication COMMIT TRAN end return(1) END GO /* ** History runstatus values defined in sqlrepl.h ** ** Start 1 ** Succeed 2 ** Inprogress 3 ** Idle 4 ** Retry 5 ** Failure 6 */ GO raiserror(15339,-1,-1,'sp_MSrepl_raiserror') go create proc sp_MSrepl_raiserror @agent sysname, @agent_name nvarchar(100), @status int, @message nvarchar(255), @subscriber sysname = NULL, @publication sysname = NULL, @article sysname = NULL as if @status = 2 --Succeeded raiserror (14150, 10, -1, @agent, @agent_name, @message) else if @status = 5 --Retry Failure raiserror (14152, 10, -1, @agent, @agent_name, @message) else if @status = 6 --Failure begin raiserror (14151, 18, -1, @agent, @agent_name, @message) end else if @status = 7 begin raiserror (20574, 10, -1, @subscriber, @article, @publication) end else if @status = 8 begin raiserror (20575, 10, -1, @subscriber, @article, @publication) end else if @status = 9 begin raiserror (14158, 10, -1, @agent, @agent_name, @message) end go raiserror(15339,-1,-1,'sp_MSget_new_errorid') GO CREATE PROCEDURE sp_MSget_new_errorid @errorid int OUTPUT, @xact_seqno varbinary(16) = NULL, @command_id int = NULL AS set nocount on SELECT @errorid = NULL BEGIN TRAN sp_MSget_new_errorid SET ROWCOUNT 1 SELECT @errorid = id FROM MSrepl_errors (UPDLOCK PAGLOCK) ORDER BY id DESC SET ROWCOUNT 0 IF @errorid IS NULL SELECT @errorid = 1 ELSE SELECT @errorid = @errorid + 1 INSERT INTO MSrepl_errors VALUES (@errorid, GETDATE(), NULL, /* Error with type NULL is placeholder, refer to sp_MSget_repl_error */ NULL, NULL, NULL, NULL, @xact_seqno, @command_id) /* return an 0 error_id if failed to insert the row */ IF @@ERROR <> 0 SELECT @errorid = 0 SELECT @errorid COMMIT TRAN GO raiserror(15339,-1,-1,'sp_MSadd_qreader_history') go CREATE PROCEDURE sp_MSadd_qreader_history ( @agent_id int, @pubid int = NULL, @runstatus int, @comments nvarchar(255) = NULL, @transaction_id nvarchar(40) = NULL, @transaction_status int = 0, @transactions_processed int =0, @commands_processed int = 0, @seconds_elapsed int = 0, @subscriber sysname = NULL, @subscriberdb sysname = NULL, @perfmon_increment bit = 1, @log_error bit = 0, @update_existing_row bit = 0, @do_raiserror bit = 1) AS BEGIN DECLARE @current_time datetime ,@start_time datetime ,@duration int ,@agent_name nvarchar(100) ,@publisher sysname ,@publisher_db sysname ,@publication sysname ,@lastrow_timestamp timestamp ,@retcode int ,@cmdprocessed_rate float ,@transaction_rate float ,@error_id int ,@idle int ,@succeed int ,@startup int ,@retry int ,@failure int ,@inprogress int ,@database sysname ,@statobjid int ,@agentclassname sysname -- -- Status const defined in sqlrepl.h -- select @startup = 1 ,@succeed = 2 ,@inprogress = 3 ,@idle = 4 ,@retry = 5 ,@failure = 6 -- intializations if (@pubid = 0) select @pubid = NULL if (@comments = '') select @comments = NULL if (@transaction_id = '') select @transaction_id = NULL if (@commands_processed is NULL) select @commands_processed = 0 if (@subscriber = '') select @subscriber = NULL if (@subscriberdb = '') select @subscriberdb = NULL SELECT @database = db_name() ,@current_time = GETDATE() -- Get named information select @agent_name = name from MSqreader_agents where id = @agent_id if (@agent_name IS NULL) begin -- -- When Queue reader is shutting down due to the last queued subscription -- being dropped it may happen that before the Queue reader logs the shutdown -- message, the subscription drop process deletes the agent entry from MSqreader_agents -- select @agent_name = quotename(@@servername) + '.' + cast(db_id() as nvarchar) end if (@pubid is NULL) begin select @publisher = NULL ,@publisher_db = NULL ,@publication = NULL end else begin select @publisher = a.srvname, @publisher_db = b.publisher_db, @publication = b.publication from master..sysservers a, MSpublications b where b.publisher_id= @pubid and b.publisher_id = a.srvid end -- Update Perfmon counter if @perfmon_increment = 1 begin if @runstatus = @startup dbcc incrementinstance ("SQL Replication Agents", "Running", "QueueReader", 1) else if (@runstatus = @succeed or @runstatus = @retry or @runstatus = @failure) dbcc incrementinstance ("SQL Replication Agents", "Running", "QueueReader", -1) end -- Get start_time for latest agent run IF (@runstatus = @startup) SELECT @start_time = @current_time ELSE BEGIN SELECT TOP 1 @start_time = start_time, @lastrow_timestamp = timestamp FROM MSqreader_history (rowlock) WHERE agent_id = @agent_id ORDER BY timestamp DESC if (@start_time is NULL) begin select @runstatus = @startup, @start_time = @current_time end END -- Calculate agent run duration SELECT @duration = DATEDIFF(second, @start_time, @current_time) -- Calculate rate of processing IF (@seconds_elapsed IS NOT NULL and @seconds_elapsed > 0) BEGIN SELECT @cmdprocessed_rate = (@commands_processed * 1.0)/@seconds_elapsed ,@transaction_rate = (@transactions_processed * 1.0)/@seconds_elapsed END ELSE BEGIN SELECT @cmdprocessed_rate = 0.0 ,@transaction_rate = 0.0 END -- Set Perfmon counters if @runstatus = @idle or @runstatus = @inprogress begin dbcc addinstance ("SQL Replication QueueReader", @agent_name) -- dbcc incrementinstance ("SQL Replication QueueReader", "QueueReader:Delivered Cmds/sec", @agent_name, @cmdprocessed_rate) -- dbcc incrementinstance ("SQL Replication QueueReader", "QueueReader:Delivered Trans/sec", @agent_name, @transaction_rate) end -- -- Set error id to 0 unless the user want to log errors associate with this -- history message. -- IF (@log_error = 1) begin select @runstatus = @failure EXEC dbo.sp_MSget_new_errorid @error_id OUTPUT end ELSE SELECT @error_id = 0 -- -- @comments should contain message at all times -- if (@comments is null) select @comments = N'no comment specified' -- Insert idle record or update if history record is already 'idle' IF (@runstatus = @idle or @update_existing_row = 1) begin -- Attempt to update the last row if it is IDLE UPDATE MSqreader_history SET publication_id = @pubid, runstatus = @runstatus, time = @current_time, duration = @duration, comments = @comments, transaction_id = @transaction_id, transaction_status = @transaction_status, transactions_processed = @transactions_processed, commands_processed = @commands_processed, delivery_rate = @cmdprocessed_rate, transaction_rate = @transaction_rate, subscriber = @subscriber, subscriberdb = @subscriberdb, error_id = case @error_id when 0 then error_id else @error_id end WHERE agent_id = @agent_id and timestamp = @lastrow_timestamp and runstatus = @runstatus -- Insert idle record if there is not one if (@@ROWCOUNT = 0) begin INSERT INTO MSqreader_history(agent_id, publication_id, runstatus, start_time, time, duration, comments, transaction_id, transaction_status, transactions_processed, commands_processed, delivery_rate, transaction_rate, subscriber, subscriberdb, error_id) VALUES(@agent_id, @pubid, @runstatus, @start_time, @current_time, @duration, @comments, @transaction_id, @transaction_status, @transactions_processed, @commands_processed, @cmdprocessed_rate, @transaction_rate, @subscriber, @subscriberdb, @error_id) end end else begin INSERT INTO MSqreader_history(agent_id, publication_id, runstatus, start_time, time, duration, comments, transaction_id, transaction_status, transactions_processed, commands_processed, delivery_rate, transaction_rate, subscriber, subscriberdb, error_id) VALUES(@agent_id, @pubid, @runstatus, @start_time, @current_time, @duration, @comments, @transaction_id, @transaction_status, @transactions_processed, @commands_processed, @cmdprocessed_rate, @transaction_rate, @subscriber, @subscriberdb, @error_id) end -- -- Update global replication agent status table -- exec @retcode = dbo.sp_MSupdate_replication_status @@servername, @database, @publication = 'ALL', @agent_type = 9, @agent_name = @agent_name, @status = @runstatus if (@retcode != 0 and @@error != 0) return 1 -- Raise the appropriate error if (@do_raiserror = 1) begin select @agentclassname = formatmessage(14581) exec dbo.sp_MSrepl_raiserror @agentclassname, @agent_name, @runstatus, @comments end IF (@@ERROR != 0) RETURN (1) END GO raiserror(15339,-1,-1,'sp_MSadd_snapshot_history') go CREATE PROCEDURE sp_MSadd_snapshot_history ( @agent_id int, @runstatus int, @comments nvarchar(255), @delivered_transactions int = 0, @delivered_commands int = 0, @log_error bit = 0, @perfmon_increment bit = 1, @update_existing_row bit = 0, @do_raiserror bit = 1, @start_time_string nvarchar(25) = null ) AS BEGIN DECLARE @current_time datetime ,@start_time datetime ,@duration int ,@delivery_rate float ,@error_id int ,@retcode int ,@idle int ,@succeed int ,@startup int ,@retry int ,@failure int ,@inprogress int ,@lastrow_timestamp timestamp ,@publisher sysname ,@publisher_db sysname ,@publication sysname ,@agent_name nvarchar(100) ,@perfmon_delivery_rate int ,@agentclassname sysname /* ** Status const defined in sqlrepl.h */ select @startup = 1 ,@succeed = 2 ,@inprogress = 3 ,@idle = 4 ,@retry = 5 ,@failure = 6 ,@current_time = GETDATE() -- Get named information select @publisher = srvname, @publisher_db = publisher_db, @publication = publication, @agent_name = name from master..sysservers, MSsnapshot_agents where id = @agent_id and publisher_id = srvid -- Update Perfmon counter if @perfmon_increment = 1 begin if @runstatus = @startup dbcc incrementinstance ("SQL Replication Agents", "Running", "Snapshot", 1) else if (@runstatus = @succeed or @runstatus = @retry or @runstatus = @failure) dbcc incrementinstance ("SQL Replication Agents", "Running", "Snapshot", -1) end /* Get start_time for latest agent run */ IF @runstatus <> 1 -- Start status BEGIN IF @start_time_string IS NULL OR @start_time_string = N'' BEGIN SELECT TOP 1 @start_time = start_time, @lastrow_timestamp = timestamp FROM MSsnapshot_history (rowlock) WHERE agent_id = @agent_id ORDER BY timestamp DESC END ELSE BEGIN SELECT @start_time = @start_time_string END END ELSE BEGIN WAITFOR DELAY '000:00:01' SELECT @current_time = DATEADD(ms, CONVERT(INT, 1000.0 * (RAND(@@spid) + RAND() + RAND())/3.0), @current_time) SELECT @start_time = @current_time END /* Calculate agent run duration */ SELECT @duration = DATEDIFF(second, @start_time, @current_time) /* Calculate delivery_rate */ IF @duration <> 0 SELECT @delivery_rate = (@delivered_commands * 1.0)/@duration ELSE SELECT @delivery_rate = 0 -- Set Perfmon counters if @runstatus = @idle or @runstatus = @inprogress begin dbcc addinstance ("SQL Replication Snapshot", @agent_name) dbcc incrementinstance ("SQL Replication Snapshot", "Snapshot:Delivered Cmds/sec", @agent_name, @delivered_commands) dbcc incrementinstance ("SQL Replication Snapshot", "Snapshot:Delivered Trans/sec", @agent_name, @delivered_transactions) end /* ** Set error id to 0 unless the user want to log errors associate with this ** history message. */ SELECT @error_id = 0 IF @log_error = 1 -- Ignore errors here. @error_id will be set to 0 in case of errors EXEC dbo.sp_MSget_new_errorid @error_id OUTPUT -- Insert idle record or update if history record is already 'idle' IF @runstatus = @idle or @update_existing_row = 1 begin -- Attempt to update the last row if it is IDLE UPDATE MSsnapshot_history SET runstatus = @runstatus, time = @current_time, duration = @duration, comments = @comments, delivered_transactions = @delivered_transactions, delivered_commands = @delivered_commands, delivery_rate = @delivery_rate, error_id = case @error_id when 0 then error_id else @error_id end WHERE agent_id = @agent_id and timestamp = @lastrow_timestamp and runstatus = @runstatus -- Insert idle record if there is not one if @@ROWCOUNT = 0 begin INSERT INTO MSsnapshot_history VALUES (@agent_id, @runstatus, @start_time, @current_time, @duration, @comments, @delivered_transactions, @delivered_commands, @delivery_rate, @error_id, NULL) end end else begin INSERT INTO MSsnapshot_history VALUES (@agent_id, @runstatus, @start_time, @current_time, @duration, @comments, @delivered_transactions, @delivered_commands, @delivery_rate, @error_id, NULL) end -- Update global replication agent status table exec dbo.sp_MSupdate_replication_status @publisher, @publisher_db, @publication, @agent_type = 1, @agent_name = @agent_name, @status = @runstatus -- Raise the appropriate error if @do_raiserror = 1 begin select @agentclassname = formatmessage(14551) exec dbo.sp_MSrepl_raiserror @agentclassname, @agent_name, @runstatus, @comments end IF @@ERROR <> 0 RETURN (1) if @runstatus = 1 begin select 'start_time' = convert(nvarchar(12), @start_time, 112) + substring(convert(nvarchar(24), @start_time, 121), 11, 13) end END GO raiserror(15339,-1,-1,'sp_MSadd_logreader_history') go CREATE PROCEDURE sp_MSadd_logreader_history ( @agent_id int, @runstatus int, @comments nvarchar(255), @xact_seqno varbinary(16) = NULL, @delivery_time int = 0, -- Current delivery time (milliseconds) @delivered_transactions int = 0, -- Running total of session @delivered_commands int = 0, -- Running total of session @delivery_latency int = 0, -- Current latency @log_error bit = 0, @perfmon_increment bit = 1, @update_existing_row bit = 0, @do_raiserror bit = 1 ) AS BEGIN DECLARE @current_time datetime ,@start_time datetime ,@duration int ,@average_commands int ,@delivery_rate float ,@error_id int ,@retcode int ,@idle int ,@succeed int ,@startup int ,@retry int ,@inprogress int ,@failure int ,@lastrow_timestamp timestamp ,@publisher sysname ,@publisher_db sysname ,@publication sysname ,@agent_name nvarchar(100) ,@last_delivered_commands int ,@last_delivered_transactions int ,@latest_delivered_commands int ,@latest_delivered_transactions int ,@latest_delivery_rate int ,@last_delivery_rate int -- int for perfmon ,@last_delivery_latency int ,@last_delivery_time int ,@avg_delivery_rate float ,@avg_delivery_latency int ,@total_delivery_time int ,@agentclassname sysname /* ** Status const defined in sqlrepl.h */ select @startup = 1 ,@succeed = 2 ,@inprogress = 3 ,@idle = 4 ,@retry = 5 ,@failure = 6 ,@current_time = GETDATE() -- Update Perfmon counter if @perfmon_increment = 1 begin if @runstatus = @startup dbcc incrementinstance ("SQL Replication Agents", "Running", "Logreader", 1) else if (@runstatus = @succeed or @runstatus = @retry or @runstatus = @failure) dbcc incrementinstance ("SQL Replication Agents", "Running", "Logreader", -1) end /* Get start_time for latest agent run */ IF @runstatus <> 1 -- Startup status BEGIN SELECT TOP 1 @start_time = start_time, @lastrow_timestamp = timestamp, @last_delivered_commands = isnull(delivered_commands, 0), @last_delivered_transactions = isnull(delivered_transactions, 0), @last_delivery_latency = isnull(delivery_latency, 0), @last_delivery_time = isnull(delivery_time, 0), @last_delivery_rate = isnull(delivery_rate, 0) FROM MSlogreader_history (rowlock) WHERE agent_id = @agent_id ORDER BY timestamp DESC END ELSE BEGIN SELECT @start_time = @current_time SET @last_delivered_commands = 0 SET @last_delivered_transactions = 0 SET @last_delivery_latency = 0 SET @last_delivery_time = 0 SET @last_delivery_rate = 0 SET @last_delivery_latency = 0 END /* Use the current time if no corresponding start_up message logged */ IF @start_time is NULL SELECT @start_time = @current_time -- Calculate number of transactions in this history set @latest_delivered_commands = @delivered_commands - @last_delivered_commands -- Calculate number of commands in this history set @latest_delivered_transactions = @delivered_transactions - @last_delivered_transactions /* Calculate agent run duration */ SELECT @duration = DATEDIFF(second, @start_time, @current_time) -- Calculate total delivery_time if @latest_delivered_commands <> 0 -- Work around for Logreader passing in @delivery_time on shutdown. SELECT @total_delivery_time = @delivery_time + @last_delivery_time else SELECT @total_delivery_time = @last_delivery_time -- Calculate average delivery_rate of the session IF @latest_delivered_commands <> 0 and @total_delivery_time <> 0 BEGIN SELECT @avg_delivery_rate = (@delivered_commands * 1.0)/(@total_delivery_time/1000.0) -- Current history delivery rate if @delivery_time <> 0 SELECT @latest_delivery_rate = (@latest_delivered_commands * 1.0)/(@delivery_time/1000.0) else SELECT @latest_delivery_rate = 0 END ELSE BEGIN SELECT @avg_delivery_rate = @last_delivery_rate SELECT @latest_delivery_rate = 0 END -- Calculate the average delivery_latency of the session if @latest_delivered_commands <> 0 -- Work around for Logreader passing in @delivery_latency on shutdown. BEGIN IF @delivery_latency <> 0 IF @last_delivery_latency <> 0 SELECT @avg_delivery_latency = (@delivery_latency + @last_delivery_latency)/2 ElSE SELECT @avg_delivery_latency = @delivery_latency ELSE SELECT @avg_delivery_latency = 0 END ELSE BEGIN SELECT @avg_delivery_latency = @last_delivery_latency -- Ignore latency value if no commands SELECT @delivery_latency = 0 END /* ** Calculate average number of commands per transaction */ IF @delivered_commands <> 0 SELECT @average_commands = @delivered_commands/@delivered_transactions ELSE SELECT @average_commands = 0 -- Set Perfmon counters select @agent_name = name from MSlogreader_agents where id = @agent_id if @runstatus = @idle or @runstatus = @inprogress begin dbcc addinstance ("SQL Replication Logreader", @agent_name) dbcc incrementinstance ("SQL Replication Logreader", "Logreader:Delivered Trans/sec", @agent_name, @latest_delivered_transactions) dbcc incrementinstance ("SQL Replication Logreader", "Logreader:Delivered Cmds/sec", @agent_name, @latest_delivered_commands) dbcc setinstance ("SQL Replication Logreader", "Logreader:Delivery Latency", @agent_name, @delivery_latency) end else begin dbcc addinstance ("SQL Replication Logreader", @agent_name) dbcc setinstance ("SQL Replication Logreader", "Logreader:Delivery Latency", @agent_name, 0) end /* ** Set error id to 0 unless the user want to log errors associate with this ** history message. */ SELECT @error_id = 0 IF @log_error = 1 -- Ignore errors here. @error_id will be set to 0 in case of errors EXEC dbo.sp_MSget_new_errorid @error_id OUTPUT -- Insert idle record or update if history record is already 'idle' IF @runstatus = @idle or @update_existing_row = 1 begin -- Attempt to update the last row if it is IDLE if (@runstatus = @idle) begin UPDATE MSlogreader_history SET runstatus = @runstatus, time = @current_time, duration = @duration,comments = @comments, error_id = case @error_id when 0 then error_id else @error_id end WHERE agent_id = @agent_id and timestamp = @lastrow_timestamp and ( runstatus = @runstatus or (@update_existing_row = 1 and runstatus in (@idle, @inprogress) and @runstatus in (@idle, @inprogress)) ) end else begin UPDATE MSlogreader_history SET runstatus = @runstatus, start_time = @start_time, time = @current_time, duration = @duration, comments = @comments, xact_seqno = @xact_seqno, delivery_time = @total_delivery_time, delivered_transactions = @delivered_transactions, delivered_commands = @delivered_commands, average_commands = @average_commands, delivery_rate = @avg_delivery_rate, delivery_latency = @avg_delivery_latency, error_id = case @error_id when 0 then error_id else @error_id end WHERE agent_id = @agent_id and timestamp = @lastrow_timestamp and ( runstatus = @runstatus or (@update_existing_row = 1 and runstatus in (@idle, @inprogress) and @runstatus in (@idle, @inprogress)) ) end -- Insert idle record if there is not one if @@ROWCOUNT = 0 begin -- Use last values because nothing was done INSERT INTO MSlogreader_history VALUES (@agent_id, @runstatus, @start_time, @current_time, @duration, @comments, @xact_seqno, @last_delivery_time, @delivered_transactions, @delivered_commands, @average_commands, @avg_delivery_rate, @last_delivery_latency, @error_id, NULL) end end else begin INSERT INTO MSlogreader_history VALUES (@agent_id, @runstatus, @start_time, @current_time, @duration, @comments, @xact_seqno, @total_delivery_time, @delivered_transactions, @delivered_commands, @average_commands, @avg_delivery_rate, @avg_delivery_latency, @error_id, NULL) end -- Get named information select @publisher = srvname, @publisher_db = publisher_db, @publication = publication, @agent_name = name from master..sysservers, MSlogreader_agents where id = @agent_id and publisher_id = srvid -- Update global replication agent status table exec dbo.sp_MSupdate_replication_status @publisher, @publisher_db, @publication = 'ALL', @agent_type = 2, @agent_name = @agent_name, @status = @runstatus -- Raise the appropriate error if @do_raiserror = 1 begin select @agentclassname = formatmessage(14552) exec dbo.sp_MSrepl_raiserror @agentclassname, @agent_name, @runstatus, @comments end IF @@ERROR <> 0 BEGIN RETURN (1) END END GO raiserror(15339,-1,-1,'sp_MSadd_distribution_history') go CREATE PROCEDURE sp_MSadd_distribution_history ( @agent_id int, @runstatus int, @comments nvarchar(255), @xact_seqno binary(16) = 0x00, -- We use binary(16)to pad it out for the below compare @delivered_transactions int = 0, -- Running total for the session @delivered_commands int = 0, -- Running total for the session @delivery_rate float = 0, -- Last rate (cmds/sec) @log_error bit = 0, @perfmon_increment bit = 1, @xactseq varbinary(16) = NULL, @command_id int = NULL, @update_existing_row bit = 0, @updateable_row bit = 1, -- used to override history verbose level to decide -- whether the row being added can be updated by another. @do_raiserror bit = 1 ) AS BEGIN set nocount on DECLARE @current_time datetime ,@start_time datetime ,@entry_time datetime ,@duration int -- milliseconds ,@delivery_latency int ,@average_commands int ,@total_cmds int ,@publisher_id smallint ,@publisher_db sysname ,@publication sysname ,@publisher sysname ,@subscriber_id smallint ,@subscriber sysname ,@subscriber_db sysname ,@article sysname ,@article_id int ,@publication_id int ,@publisher_database_id int ,@agent_name nvarchar(100) ,@error_id int ,@startup int ,@succeed int ,@inprogress int ,@retry int ,@failure int ,@validation_failure int ,@validation_success int, @error_skipped int ,@requested_shutdown int ,@raiserror_status int ,@idle int ,@lastrow_timestamp timestamp ,@lastrow_xact_seqno binary(16) ,@new_delivered_commands int ,@new_delivered_transactions int ,@retcode int ,@last_delivery_rate float ,@last_delivery_latency int ,@avg_delivery_rate float ,@avg_delivery_latency int ,@perfmon_delivery_rate int ,@existing_row_updateble bit ,@this_row_updateable bit ,@agentclassname sysname ,@MAXINT int /* ** Status const defined in sqlrepl.h */ select @startup = 1 ,@succeed = 2 ,@inprogress = 3 ,@idle = 4 ,@retry = 5 ,@failure = 6 ,@validation_failure = 7 ,@validation_success = 8 ,@requested_shutdown = 9 ,@error_skipped = 10 ,@MAXINT = 2147483647 -- To prevent cleanup up being messed up by invalid history message, only log -- valid history message. if @runstatus > 10 or @runstatus < 1 begin --Invalid history message logged RAISERROR (21079, 16, -1, @runstatus) return (1) end select @existing_row_updateble = 1 select @this_row_updateable = 1 select @raiserror_status = @runstatus if (@runstatus = @validation_failure or @runstatus = @validation_success or @runstatus = @requested_shutdown or @runstatus = @error_skipped) begin select @runstatus = @inprogress select @this_row_updateable = 0 end if (@updateable_row = 0) begin select @this_row_updateable = 0 end -- Security Check exec @retcode = dbo.sp_MScheck_pull_access @agent_id = @agent_id, @agent_type = 0 -- distribution agent if @@error <> 0 or @retcode <> 0 return (1) SELECT @current_time = GETDATE() -- Update Perfmon counter if @perfmon_increment = 1 begin if @runstatus = @startup dbcc incrementinstance ("SQL Replication Agents", "Running", "Distribution", 1) else if (@runstatus = @succeed or @runstatus = @retry or @runstatus = @failure) dbcc incrementinstance ("SQL Replication Agents", "Running", "Distribution", -1) end -- Get agent name, publisher id and publisher_db select @agent_name = name, @publisher_database_id = publisher_database_id, @publisher_id = publisher_id, @publisher_db = publisher_db, @publication = publication, @subscriber_id = subscriber_id, @subscriber_db = subscriber_db from MSdistribution_agents where id = @agent_id select @publisher = srvname from master..sysservers where srvid = @publisher_id select @subscriber = srvname from master..sysservers where srvid = @subscriber_id /* Get start_time and xact_seqno for latest agent run */ IF @runstatus <> 1 BEGIN SELECT TOP 1 @lastrow_xact_seqno = xact_seqno, @start_time = start_time, @total_cmds = total_delivered_commands, @lastrow_timestamp = timestamp, @new_delivered_transactions = @delivered_transactions - delivered_transactions, @new_delivered_commands = @delivered_commands - delivered_commands, @last_delivery_rate = delivery_rate, @last_delivery_latency = delivery_latency, @existing_row_updateble = updateable_row FROM MSdistribution_history (rowlock) WHERE agent_id = @agent_id ORDER BY timestamp DESC /* ** Check the case where the user did not pass in the proper values ** for delivered commands and transactions (this leads to negative ** new command/tran counts). */ if ( @new_delivered_commands < 0) SELECT @new_delivered_commands = 0 if ( @new_delivered_transactions < 0 ) SELECT @new_delivered_transactions = 0 END ELSE BEGIN -- At least get running total of commands over all sessions. SELECT TOP 1 @lastrow_xact_seqno = xact_seqno, @total_cmds = total_delivered_commands FROM MSdistribution_history (rowlock) WHERE agent_id = @agent_id ORDER BY timestamp DESC SELECT @start_time = @current_time SELECT @new_delivered_commands = @delivered_commands SELECT @new_delivered_transactions = @delivered_transactions SELECT @last_delivery_rate = 0 SELECT @last_delivery_latency = 0 END IF @total_cmds IS NULL SELECT @total_cmds = 0 /* Use the current time if no corresponding start_up message logged */ IF @start_time is NULL SELECT @start_time = @current_time /* Calculate agent run duration */ SELECT @duration = DATEDIFF(second, @start_time, @current_time) IF @delivered_commands <> 0 SELECT @average_commands = @delivered_commands/@delivered_transactions ELSE SELECT @average_commands = 0 -- Get the entry time of the last distributed transaction if @xact_seqno <> 0x00 and @new_delivered_commands <> 0 -- SELECT @entry_time = entry_time FROM MSrepl_transactions (READPAST) SELECT @entry_time = entry_time FROM MSrepl_transactions WHERE xact_seqno = @xact_seqno and publisher_database_id = @publisher_database_id -- Calculate the latency of the last distributed transaction IF @entry_time IS NOT NULL begin -- Calculte diff in minutes. declare @diff_min int select @diff_min = DATEDIFF(minute, @entry_time, @current_time) if @diff_min > 16666 select @delivery_latency = 999999999 else select @delivery_latency = DATEDIFF(millisecond, @entry_time, @current_time) end ELSE SELECT @delivery_latency = 0 -- Calculate the average delivery latency of the session IF @last_delivery_latency = 0 SET @avg_delivery_latency = @delivery_latency ELSE IF @delivery_latency = 0 SET @avg_delivery_latency = @last_delivery_latency ELSE SET @avg_delivery_latency = (@delivery_latency + @last_delivery_latency)/2 -- Calculate average delivery rate of the session IF @last_delivery_rate = 0 SET @avg_delivery_rate = @delivery_rate ELSE IF @delivery_rate = 0 or @new_delivered_commands = 0 SET @avg_delivery_rate = @last_delivery_rate ELSE SET @avg_delivery_rate = (@delivery_rate + @last_delivery_rate)/2.0 /* Calculate grand total of delivered trans across sessions, check ** to make sure the result does not overflow integer column */ if (@total_cmds > @MAXINT - @new_delivered_commands) SET @total_cmds = @MAXINT else SET @total_cmds = @total_cmds + @new_delivered_commands -- Set Perfmon counters if @runstatus = @idle or @runstatus = @inprogress begin dbcc addinstance ("SQL Replication Distribution", @agent_name) dbcc incrementinstance ("SQL Replication Distribution", "Dist:Delivered Trans/sec", @agent_name, @new_delivered_transactions) dbcc incrementinstance ("SQL Replication Distribution", "Dist:Delivered Cmds/sec", @agent_name, @new_delivered_commands) dbcc setinstance ("SQL Replication Distribution", "Dist:Delivery Latency", @agent_name, @delivery_latency) end else begin /* Reset latency counter */ dbcc addinstance ("SQL Replication Distribution", @agent_name) dbcc setinstance ("SQL Replication Distribution", "Dist:Delivery Latency", @agent_name, 0) end /* ** Set error id to 0 unless the user want to log errors associate with this ** history message. */ SELECT @error_id = 0 IF @log_error = 1 -- Ignore errors here. @error_id will be set to 0 in case of errors EXEC dbo.sp_MSget_new_errorid @error_id OUTPUT, @xactseq, @command_id -- @xact_seqno may be uninitialized for the first several messages after -- the start-up of the distribtion agent. Get the correct value in that case. -- We must do this because distribution cleanup will use the lastest xact_seqno -- as cleanup boundary. -- Note: @last_xact_seqno might be NULL -- Only do this if @xact_seqno is 0, since a smaller xact_seqno might be logged due -- to reinited sub for immediate_sync pub. -- This will prevent history being messed up by one gabage history entry. if @xact_seqno = 0x00 and @lastrow_xact_seqno is not null select @xact_seqno = @lastrow_xact_seqno -- Insert idle record or update if history record is already 'idle' IF (@existing_row_updateble = 1) and (@runstatus = @idle or @update_existing_row = 1) begin -- Attempt to update the last row if it is IDLE if (@runstatus = @idle) begin UPDATE MSdistribution_history SET runstatus = @runstatus, time = @current_time, duration = @duration, comments = @comments, xact_seqno = @xact_seqno, updateable_row = @this_row_updateable, error_id = case @error_id when 0 then error_id else @error_id end WHERE agent_id = @agent_id and timestamp = @lastrow_timestamp and ( runstatus = @runstatus or (@update_existing_row = 1 and runstatus in (@idle, @inprogress) and @runstatus in (@idle, @inprogress)) ) end else begin -- Attempt to update the last row if it is IDLE UPDATE MSdistribution_history SET runstatus = @runstatus, start_time = @start_time, time = @current_time, duration = @duration, xact_seqno = @xact_seqno, comments = @comments, delivered_transactions = @delivered_transactions, delivered_commands = @delivered_commands, average_commands = @average_commands, delivery_rate = @avg_delivery_rate, delivery_latency = @avg_delivery_latency, total_delivered_commands = @total_cmds, current_delivery_rate = @delivery_rate, current_delivery_latency = @delivery_latency, updateable_row = @this_row_updateable, error_id = case @error_id when 0 then error_id else @error_id end WHERE agent_id = @agent_id and timestamp = @lastrow_timestamp and ( runstatus = @runstatus or (@update_existing_row = 1 and runstatus in (@idle, @inprogress) and @runstatus in (@idle, @inprogress)) ) end -- Insert idle record if there is not one if @@ROWCOUNT = 0 begin INSERT INTO MSdistribution_history (agent_id, runstatus, start_time, time, duration, comments, xact_seqno, delivered_transactions, delivered_commands, average_commands, delivery_rate, delivery_latency, total_delivered_commands, error_id, timestamp, current_delivery_rate, current_delivery_latency, updateable_row) VALUES (@agent_id, @runstatus, @start_time, @current_time, @duration, @comments, @xact_seqno, @delivered_transactions, @delivered_commands, @average_commands, @avg_delivery_rate, @avg_delivery_latency, @total_cmds, @error_id, NULL, @delivery_rate, @delivery_latency, @this_row_updateable) end end else begin INSERT INTO MSdistribution_history (agent_id, runstatus, start_time, time, duration, comments, xact_seqno, delivered_transactions, delivered_commands, average_commands, delivery_rate, delivery_latency, total_delivered_commands, error_id, timestamp, current_delivery_rate, current_delivery_latency, updateable_row) VALUES (@agent_id, @runstatus, @start_time, @current_time, @duration, @comments, @xact_seqno, @delivered_transactions, @delivered_commands, @average_commands, @avg_delivery_rate, @avg_delivery_latency, @total_cmds, @error_id, NULL, @delivery_rate, @delivery_latency, @this_row_updateable) end -- Refresh repl monitor declare @agent_type int set @agent_type = dbo.fn_MSmask_agent_type (@agent_id, 3) -- Update global replication agent status table exec dbo.sp_MSupdate_replication_status @publisher, @publisher_db, @publication, @agent_type = @agent_type, @agent_name = @agent_name, @status = @runstatus if (@raiserror_status = @validation_failure or @raiserror_status = @validation_success or @raiserror_status = @error_skipped) begin -- Get the "real" publication name (as opposed to 'ALL') and article name select @article_id = article_id from MSrepl_commands where publisher_database_id = @publisher_database_id and xact_seqno = @xactseq and command_id = @command_id select @publication = mp.publication, @publication_id = mp.publication_id from MSpublications as mp, MSsubscriptions as ms where mp.publisher_id = ms.publisher_id and mp.publisher_db = ms.publisher_db and mp.publication_id = ms.publication_id and ms.publisher_id = @publisher_id and ms.publisher_db = @publisher_db and ms.subscriber_id = @subscriber_id and ms.subscriber_db = @subscriber_db and ms.article_id = @article_id select @article = article from MSarticles where article_id = @article_id and publisher_id = @publisher_id and publisher_db = @publisher_db and publication_id = @publication_id end -- Raise the appropriate error if @do_raiserror = 1 begin select @agentclassname = formatmessage(14553) exec dbo.sp_MSrepl_raiserror @agentclassname, @agent_name, @raiserror_status, @comments, @subscriber=@subscriber, @publication=@publication, @article=@article end IF @@ERROR <> 0 RETURN (1) END GO raiserror(15339,-1,-1,'sp_MSsubscription_cleanup') GO CREATE PROCEDURE sp_MSsubscription_cleanup @cutoff_time datetime as begin set nocount on declare @ACTIVE tinyint, @INACTIVE tinyint, @SUBSCRIBED tinyint, @VIRTUAL smallint declare @retcode int, @max_time datetime, @agent_id int, @num_dropped int select @ACTIVE = 2 select @INACTIVE = 0 select @SUBSCRIBED = 1 select @VIRTUAL = -1 select @max_time = dateadd(hour, 1, getdate()) -- Refer to sp_MSmaximun_cleanup_xact_seqno to understand the logic -- in this sp. If you change the logic here, you may need to change -- that sp as well. -- Deactivate real subscriptions for agents that are working on -- transactions that are older than @retention -- update all the subscriptions for those agents, including -- subscriptions that are in subscribed state! update MSsubscriptions set status = @INACTIVE where agent_id in ( select derivedInfo.agent_id from ( -- Here we are retrieving the agent id, publisher database id, -- min subscription sequence number, and the transaction seqno -- related to the max timestamp row in the history table. this is -- important since the tran seqno can go back to lower values in -- the case of reinit with immediate sync. select s.agent_id as agent_id, s.publisher_database_id as publisher_database_id, min(s.subscription_seqno) as subscription_seqno, isnull(h.xact_seqno, 0x0) as xact_seqno from MSsubscriptions s left join (MSdistribution_history h (REPEATABLEREAD) join (select agent_id, max(timestamp) as timestamp from MSdistribution_history (REPEATABLEREAD) group by agent_id) as h2 on h.agent_id = h2.agent_id and h.timestamp = h2.timestamp) on s.agent_id = h.agent_id where s.status = @ACTIVE and s.subscriber_id >= 0 -- Only well-known agent group by s.agent_id, -- agent and pubdbid as a pair can never be differnt s.publisher_database_id, isnull(h.xact_seqno, 0x0) -- because of join above we can include this ) derivedInfo where @cutoff_time >= ( -- Get the entry_time of the first tran that cannot be -- cleaned up normally because of this agent. -- Use history if it's larger and it is not null (exists) case when derivedInfo.xact_seqno >= derivedInfo.subscription_seqno then -- Have to join with commands table because transactions that -- does not have commands will not be picked up by -- sp_MSget_repl_commands isnull((select top 1 entry_time from MSrepl_transactions t, MSrepl_commands c where t.publisher_database_id = derivedInfo.publisher_database_id and c.publisher_database_id = derivedInfo.publisher_database_id and c.xact_seqno = t.xact_seqno -- history xact_seqno can be cleaned up and t.xact_seqno > isnull(derivedInfo.xact_seqno,0x0) and c.xact_seqno > isnull(derivedInfo.xact_seqno,0x0) order by t.xact_seqno asc), @max_time) else isnull((select top 1 entry_time from MSrepl_transactions t, MSrepl_commands c where t.publisher_database_id = derivedInfo.publisher_database_id and c.publisher_database_id = derivedInfo.publisher_database_id and c.xact_seqno = t.xact_seqno -- sub xact_seqno cannot be cleaned up and t.xact_seqno >= derivedInfo.subscription_seqno and c.xact_seqno >= derivedInfo.subscription_seqno order by t.xact_seqno asc), @max_time) end)) if @@rowcount <> 0 RAISERROR(21011, 10, -1) -- Dropping all the aonymous agents that are working on -- transactions that are older than @retention -- No message raised. -- Don't drop agents that do not have history (true for new agents). -- For each publisher/publisherdb pair do cleanup declare hC CURSOR LOCAL FAST_FORWARD FOR select distinct derivedInfo.agent_id from ( -- Here we are retrieving the agent id, publisher database id, -- min subscription sequence number, and the transaction seqno -- related to the max timestamp row in the history table. this is -- important since the tran seqno can go back to lower values in -- the case of reinit with immediate sync. select msda.id as agent_id, msda.publisher_database_id as publisher_database_id, min(s.subscription_seqno) as subscription_seqno, h.xact_seqno as xact_seqno from MSsubscriptions s join MSdistribution_agents msda on s.agent_id = msda.virtual_agent_id join (MSdistribution_history h (REPEATABLEREAD) join (select agent_id, max(timestamp) as timestamp from MSdistribution_history (REPEATABLEREAD) group by agent_id) as h2 on h.agent_id = h2.agent_id and h.timestamp = h2.timestamp) on msda.id = h.agent_id where s.status = @ACTIVE group by msda.id, -- agent and pubdbid as a pair can never be differnt msda.publisher_database_id, h.xact_seqno ) derivedInfo where @cutoff_time >= ( -- Get the entry_time of the first tran that cannot be -- cleaned up normally because of this agent. -- Use history if it's larger and it is not null (exists) case when derivedInfo.xact_seqno > 0x00 then -- does not have commands will not be picked up by -- sp_MSget_repl_commands isnull((select top 1 entry_time from MSrepl_transactions t, MSrepl_commands c where t.publisher_database_id = derivedInfo.publisher_database_id and c.publisher_database_id = derivedInfo.publisher_database_id and c.xact_seqno = t.xact_seqno -- history xact_seqno can be cleaned up and t.xact_seqno > derivedInfo.xact_seqno and c.xact_seqno > derivedInfo.xact_seqno order by t.xact_seqno asc), @max_time) else isnull((select top 1 entry_time from MSrepl_transactions t, MSrepl_commands c where t.publisher_database_id = derivedInfo.publisher_database_id and c.publisher_database_id = derivedInfo.publisher_database_id and c.xact_seqno = t.xact_seqno -- sub xact_seqno cannot be cleaned up and t.xact_seqno >= derivedInfo.subscription_seqno and c.xact_seqno >= derivedInfo.subscription_seqno order by t.xact_seqno asc), @max_time) end) for read only select @num_dropped = 0 open hC fetch hC into @agent_id while (@@fetch_status <> -1) begin exec @retcode = dbo.sp_MSdrop_distribution_agentid @agent_id if @retcode <> 0 or @@error <> 0 return (1) select @num_dropped = @num_dropped + 1 fetch hC into @agent_id end if @num_dropped > 0 RAISERROR(20597, 10, -1, @num_dropped) -- Deactivating subscriptions virtual subscriptions that are older then @retention. update MSsubscriptions set status = @SUBSCRIBED -- Only change active subscriptions! where status = @ACTIVE and subscriber_id = @VIRTUAL -- Get the entry_time of the first tran that cannot be -- cleaned up normally because of this subscription. and @cutoff_time >= isnull((select top 1 entry_time