May 11, 2012

sp_MSenum_qreader_s (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_MSenum_qreader_s(int @publication_id
, int @hours
, int @session_type)

MetaData:

 create procedure sys.sp_MSenum_qreader_s   
(
@publication_id int = 0,
@hours int = 0, -- @hours < 0 will return TOP 100 --
@session_type int = 1 -- Return all sessions --
)
as
begin
set nocount on

declare @succeed int
,@retry int
,@failure int
,@min_time datetime
,@agent_id int
,@maxtimestamp timestamp
,@session_start timestamp
,@next_session_start timestamp
,@session_end timestamp
,@final_ts timestamp

,@status int
,@session_status int
,@error_id int
,@session_error_id int,
@start_time nvarchar(24),
@time nvarchar(24),
@comments nvarchar(255),
@duration int,
@delivery_rate int,
@delivery_latency int,
@transactions_processed int,
@commands_processed int,
@average_commands int,
@action_count int,
@start_datetime datetime,
@end_datetime datetime

--
-- security check
-- only replmonitor can execute this
--
if not (is_member(N'db_owner') = 1 or isnull(is_member(N'replmonitor'),0) = 1)
begin
raiserror(14260, 16, -1)
return (1)
end

if @session_type not in (1,2)
begin
return(1)
end

--
-- Status const defined in sqlrepl.h
--
select @succeed = 2
select @retry = 5
select @failure = 6

-- create the table to store sessions
create table #qreader_session (status int NOT NULL,
start_time nvarchar(24) NOT NULL, time nvarchar(24) NOT NULL, comments nvarchar(255) NULL,
duration int NULL, delivery_rate int NULL, delivery_latency int NULL,
publication_id int NULL, transactions_processed int NULL, commands_processed int NULL,
average_commands int NULL, action_count int NULL, error_id INT NULL, local_timestamp binary(8) NOT NULL)

-- get the agent id
select TOP 1 @agent_id = agent_id from MSqreader_history with (READPAST)

--
-- If Min time specified, initilialize it
--
IF (@hours < 1)
select @min_time = NULL
ELSE
select @min_time = dateadd(hour, -@hours, getdate())

-- initialize
select @delivery_latency = 0
,@next_session_start = NULL
,@session_end = NULL
,@final_ts = max(timestamp)
from MSqreader_history

-- Get the session details
while (@session_end != @final_ts)
begin
--
-- Mark the beginning of a new session
--
if (@next_session_start is NULL)
begin
select @session_start = min(timestamp) from MSqreader_history with (READPAST)
where agent_id = @agent_id and runstatus = 1
end
else
begin
select @session_start = @next_session_start
end

--
-- update @next_session_start
--
select @next_session_start = ISNULL(min(timestamp), @final_ts)
from MSqreader_history
where agent_id = @agent_id and timestamp > @session_start and runstatus = 1

--
-- find session end based on @next_session_start
--
if (@next_session_start = @final_ts)
select @session_end = @final_ts
else
begin
select @session_end = isnull(max(timestamp), @next_session_start)
from MSqreader_history with (READPAST)
where agent_id = @agent_id and
timestamp > @session_start and
timestamp < @next_session_start
end

--
-- get start time for this session
--
select @start_datetime = start_time,
@start_time = sys.fn_replformatdatetime(start_time)
from MSqreader_history with (READPAST)
where agent_id = @agent_id and timestamp = @session_start

--
-- do we need to process this session
--
if (@start_time IS NULL)
begin
--
-- Check if we are done with all sessions
--
if (@session_end = @final_ts)
break
else
continue
end

--
-- get status, end time, comments, error id for this session
--
select @status = runstatus,
@end_datetime = time,
@time = sys.fn_replformatdatetime(time),
@comments = comments,
@error_id = ISNULL(error_id, 0)
from MSqreader_history with (READPAST)
where agent_id = @agent_id and timestamp = @session_end

--
-- do we need to continue this session - min time check
--
if ((@min_time is NOT NULL) and (@min_time > @end_datetime))
begin
--
-- Check if we are done with all sessions
--
if (@session_end = @final_ts)
break
else
continue
end

--
-- get duration, action count, tran processed, cmds processed, avg cmds, etc
-- for this session
--
select @duration = DATEDIFF(second, @start_datetime, @end_datetime)
select @action_count = ISNULL(count(*), 0)
,@session_status = max(runstatus)
,@session_error_id = isnull(max(error_id),0)
from MSqreader_history with (READPAST)
where agent_id = @agent_id
and timestamp >= @session_start and timestamp <= @session_end

select @transactions_processed = sum(ISNULL(transactions_processed, 0))
,@commands_processed = sum(ISNULL(commands_processed, 0))
,@average_commands = avg(ISNULL(commands_processed, 0))
,@delivery_rate = cast(avg(ISNULL(delivery_rate, 0.0)) as int)
from MSqreader_history with (READPAST)
where agent_id = @agent_id
and timestamp >= @session_start and timestamp <= @session_end
and transactions_processed > 0
--
-- if we have errors in the session - choose the highest status
--
if (@session_status > 4)
begin
select @status = @session_status
,@error_id = @session_error_id
end
--
-- insert into #qreader_session
--
insert into #qreader_session values(@status, @start_time, @time, @comments,
@duration, @delivery_rate, @delivery_latency, NULL, @transactions_processed,
@commands_processed, @average_commands, @action_count, @error_id, @session_end)

--
-- Check if we are done with all sessions
--
if (@session_end = @final_ts)
break
end

-- return results

if (@hours < 0)
set rowcount 100
select
status,
start_time,
time,
comments,
duration,
delivery_rate,
delivery_latency,
publication_id,
transactions_processed,
commands_processed,
average_commands,
action_count,
error_id
from #qreader_session
where (@session_type = 2 and (status = @failure)) or
(@session_type = 1)
order by local_timestamp desc
--
-- all done
--
return 0
end

No comments:

Post a Comment

Total Pageviews