Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSadd_logreader_history

  No additional text.


Syntax
CREATE PROCEDURE sys.sp_MSadd_logreader_history
(
    @agent_id int,
    @runstatus int,
    @comments nvarchar(4000),
    @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,
	@updateable_row bit = 1		-- used to override history verbose level to decide
									-- whether the row being added can be updated by another.	
)
AS
BEGIN
	set nocount on
	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 float
			,@last_delivery_rate float             -- was declare as int for perfmon,but never used in perfmon, change back to float to eb consistant with other rates
			,@last_delivery_latency int
			,@last_delivery_time int
			,@avg_delivery_rate float
			,@avg_delivery_latency int
			,@total_delivery_time int
			,@agentclassname sysname
			,@last_xact_seqno varbinary(16)
			,@prev_runstatus int
			,@prev_start_time datetime
			,@existing_row_updateble bit
    
    -- security check
    -- only db_owner can execute this
    
    if (is_member ('db_owner') != 1)
    begin
        raiserror(14260, 16, -1)
        return (1)
    end

    
    -- security check
    -- Has to be executed from distribution database
    
    if (sys.fn_MSrepl_isdistdb (db_name()) != 1)
    begin
        raiserror(21482, 16, -1, 'sp_MSadd_logreader_history', 'distribution')
        return (1)
    end

	/*
	** 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

    -- The initial values provided below are needed for the first entry for the agent that
    -- appears in MSlogreader_history.  Normally, this occurs when the initialization message
    -- is logged (@runstatus = 1).  It can also occur when an error message is logged before
    -- the startup message is written. For message subsequent to the first message, these
    -- values are overwritten.

    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

    /* Get start_time for latest agent run */
    select @agent_name = N'DEFAULTAGENT'
    select @agent_name = isnull(name, N'DEFAULTAGENT') from MSlogreader_agents where id = @agent_id
    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),
            @last_xact_seqno = xact_seqno
            ,@existing_row_updateble = updateable_row
        FROM MSlogreader_history with (rowlock)
            WHERE agent_id = @agent_id
                ORDER BY timestamp DESC
    END
    ELSE
    BEGIN
    	SELECT TOP 1
    		@prev_runstatus = runstatus,
			@prev_start_time = start_time,
    		@last_xact_seqno = xact_seqno,
			@last_delivery_latency = isnull(delivery_latency, 0)
           ,@existing_row_updateble = updateable_row
        FROM MSlogreader_history with (rowlock)
            WHERE agent_id = @agent_id
            ORDER BY timestamp  DESC

		IF @prev_runstatus IN (@startup, @retry)
		BEGIN
			SELECT @start_time = @prev_start_time
		END
		
		-- Set Startup Perfmon counters
        dbcc addinstance ("SQL Replication Logreader", @agent_name)
        dbcc setinstance ("SQL Replication Logreader", "Logreader:Delivery Latency", @agent_name, @last_delivery_latency )
    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 xact_seqno to last value if not already set
	if (@xact_seqno = 0x or @xact_seqno is NULL) and @last_xact_seqno is not null
	begin
		select @xact_seqno = @last_xact_seqno
	end
	
    -- Set Perfmon counters
    -- Note that Startup perfmon counters are set above
    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

    /*
    ** 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 sys.sp_MSget_new_errorid @error_id OUTPUT

    -- 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 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
			,updateable_row = @updateable_row
            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
			,updateable_row = @updateable_row
			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, @total_delivery_time, @delivered_transactions, @delivered_commands,
                @average_commands, @avg_delivery_rate, @avg_delivery_latency, @error_id, NULL, @updateable_row)
        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, @updateable_row)
    end

    -- Get named information
    select @publisher = srvname, @publisher_db = publisher_db, @publication = publication,
        @agent_name = isnull(name, N'DEFAULTAGENT') from master.dbo.sysservers, MSlogreader_agents where
        id = @agent_id and
        publisher_id = srvid

 	-- Raise the appropriate error
	if @do_raiserror = 1
	begin
		select @agentclassname = formatmessage(14552)

		-- Only use the first 255 characters of the comment
		select @comments = left(@comments, 255)

		exec sys.sp_MSrepl_raiserror @agentclassname, @agent_name, @runstatus, @comments
	end

    IF @@ERROR <> 0
    BEGIN
       RETURN (1)
    END
END

 
Last revision SQL2008SP1
See also

  sp_instdist (Procedure)
sp_MSadd_repl_job_unsafe (Procedure)
sp_MSagent_retry_stethoscope (Procedure)
sp_MSagent_stethoscope (Procedure)
sp_MSdetect_nonlogged_shutdown (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash