April 2, 2008 at 7:24 am
Hi,
I'm looking for a way to see replication state without using replication monitor but with some kind of select statement.
Is there any chance to get these information on SQL Server 2005?
Best Regards,
Jan
April 2, 2008 at 5:03 pm
I have a SQL script I use because I think the GUI is inaccurate. I have a report I scheduled through ReportingServices and run it hourly. It gives me a very good idea what is going on. I have attached the script I use. I only look for items that are greater than five minutes. This is running on SQL 2005 transactional replication.
use distribution
select count(*) as msrepl_commands_rowcount from msrepl_Commands with (nolock)
select count(*) as msrep_transactions_rowcount from msrepl_Transactions with (nolock)
create table #latencytmp
( agent_id int,
publisher_database_id int,
article_id int,
xact_seqno varbinary (16))
insert into #latencytmp
(agent_id,
publisher_database_id,
article_id,
xact_seqno)
(select
msagh.[agent_id],
msd.id as publisher_database_id,
msa.[article_id],
max(msagh.[xact_seqno])
from dbo.msdistribution_History msagh (nolock)
inner join dbo.msdistribution_agents msag (nolock)
on msag.id = msagh.agent_id
inner join [dbo].[MSpublisher_databases] msd(nolock)
on msd.publisher_db = msag.publisher_db
inner join dbo.mspublications msp (nolock)
on msp.publisher_id = msag.publisher_id
and msp.publisher_db = msag.publisher_db
and msp.publication = msag.publication
inner join dbo.MSarticles msa (nolock)
on msa.publisher_id = msp.publisher_id
and msa.publisher_db = msp.publisher_db
and msa.publication_id = msp.publication_id
where msagh.xact_seqno <> ''
and msag.subscriber_id >= 0
group by msagh.agent_id, msd.id, msa.article_id
)
select msa.article
, msag.ID
-- , msag.name
, SUBSTRING(msag.name, 1, (CHARINDEX('-', msag.name))- 1) As PublisherServer
, msp.publisher_db
, msp.publication
, ss.srvname AS SubscriberServer
, count(msc.article_ID) as Undistributed_Data_Count
, Min(entry_time) As Oldest_Transaction
from #latencytmp sjmtmp (nolock)
inner join dbo.msdistribution_agents msag (nolock)
on msag.id = sjmtmp.agent_id
inner join dbo.mspublications msp (nolock)
on msp.publisher_id = msag.publisher_id
and msp.publisher_db = msag.publisher_db
and msp.publication = msag.publication
inner join dbo.MSarticles msa (nolock)
on msa.publisher_id = msp.publisher_id
and msa.publisher_db = msp.publisher_db
and msa.publication_id = msp.publication_id
INNER JOIN dbo.msrepl_commands msc (nolock)
ON msc.Publisher_database_id = sjmtmp.Publisher_database_id
and msc.xact_seqno > sjmtmp.xact_seqno
and msc.article_id = sjmtmp.article_id
INNER JOIN MSrepl_transactions mst (nolock)
ON mst.xact_seqno = msc.xact_seqno
LEFT JOIN sys.sysservers ss
on ss.srvid = msag.subscriber_id
where entry_time < dateadd(mi,-5, getdate())
group by msa.article, msag.ID, msag.name, msp.publisher_db, msp.publication, ss.srvname
order by count(msag.ID) desc
drop table #latencytmp
April 3, 2008 at 4:44 am
Hi Mikelyn,
it's not exactly what I'm looking for, but as I now know the associated tables and views I can build a check.
Thank you very much.
Regards,
Jan
April 5, 2008 at 10:28 pm
Run select on following objects in distribution database to find status.
select * from MSlogreader_history
select * from MSsnapshot_history
select * from MSdistribution_history
select * from MSmerge_history
select * from MSqreader_history
I am not sure what type replication you are using. Thats what I listed all.
[font="Verdana"]--www.sqlvillage.com[/size][/font]
April 8, 2008 at 8:59 am
Hi,
this is what I build to check replication state. 99% of our replication failures are caused by an missing initial snapshot, so this procedure just checks if there is a message which says "snapshot not available", and sends a E-Mail:
Create PROCEDURE [dbo].[proc_replication_monitor]
AS
BEGIN
SET NOCOUNT ON;
declare @id int
declare @comments nvarchar(4000)
declare @time datetime
declare @publisher_db nvarchar(128)
declare @publication nvarchar(128)
declare @subscriber_db nvarchar(128)
declare @sendmail bit
set @sendmail = 0
declare @servername nvarchar(25)
set @servername = @@servername
DECLARE @mailRecipientsnvarchar(150) --die Empfänger, getrennt mit ;
DECLARE @mailSubject nvarchar(50)
DECLARE @mailBody nvarchar(max)
DECLARE @mailType nvarchar(10) --Format (HTML oder Plain)
DECLARE @mailProfile nvarchar(15) --Name des zu benutzenden Profils
DECLARE @mailPriority nvarchar(10)
DECLARE @mailAttach int
SET @mailRecipients= N'E-Mail Adresses separated with ;'
SET @mailSubject = 'Replikationsmonitor auf ' + @servername
SET @mailType = 'HTML'
SET @mailAttach= 0
SET @mailProfile = --You need a E-Mail profile on your server for running this sp
SET @mailPriority = 'High'
SET @mailBody = '
Die folgende(n) Replikation(en) scheinen nicht mehr zu laufen: '
SET @mailBody = @mailBody + ' '
SET @mailBody = @mailBody + ' '
SET @mailBody = @mailBody + ' '
declare myCursor Cursor for
select
agents.id
, his.comments
, his.time
,agents.publisher_db
,agents.publication
--,subscriber.publisher_database_id
,agents.subscriber_db
--, agents.*
from
distribution.dbo.msdistribution_agents agents
left outer join distribution.dbo.msdistribution_History his
on agents.id = his.agent_id
inner join (
select max(his.time) as time
,agents.id
from
distribution.dbo.msdistribution_agents agents
left outer join distribution.dbo.msdistribution_History his
on agents.id = his.agent_id
group by agents.id
) maxtime
on agents.id = maxtime.id
and his.time = maxtime.time
where agents.subscriber_db <> 'virtual'
and his.comments like '%snapshot%available%'
order by
his.time desc,
agents.id asc
open myCursor
fetch next from myCursor
into @id
, @comments
, @time
, @publisher_db
, @publication
, @subscriber_db
while @@fetch_status = 0
begin
set @sendmail = 1
SET @mailBody = @mailBody + ' '
fetch next from myCursor
into @id
, @comments
, @time
, @publisher_db
, @publication
, @subscriber_db
end
if @sendmail = 1
begin
SET @mailBody = @mailBody + ' '
EXECUTE msdb.dbo.sp_send_dbmail @profile_name = @mailProfile
,@recipients = @mailRecipients
,@subject = @mailSubject
,@body = @mailBody
,@body_format = @mailType
,@attach_query_result_as_file = @mailAttach
,@importance = @mailPriority
--,@query = 'SELECT a.[TicketNr] ,b.comment as Dienst ,a.[insert_date] ,DATEDIFF(Hour , a.insert_date, getdate()) as [Offen seit (h)] FROM [Verwaltung].[dbo].[tbl_WMIWatcherTickets] a INNER JOIN [Verwaltung].dbo.tbl_WMIWatcher b ON a.wmiWatcherid = b.id where a.[open] = 1 and a.insert_date < DATEADD(hour,-1,getdate()) '
end
close myCursor
deallocate myCursor
END
This SP runs in a SQL Server Agent Job every 5 minutes on all servers which are publishing replications, and it seems to working as wanted.
Regards,
Jan
February 1, 2010 at 3:48 pm
Was wondering the same thing, so spent a couple hours digging and came up with this. Not valid for all prod environments, but for regular transactional it should work. I'll post here if I run into issues with it:
SELECT
errors.agent_id,
errors.last_time,
agentinfo.name,
agentinfo.publication,
agentinfo.subscriber_db,
error_messages.comments AS ERROR
FROM
--find errors; a runstatus 3 can be the last message, even if good
(SELECT agent_id,
MAX(TIME) AS last_time
FROM distribution.dbo.MSdistribution_history with (nolock)
WHERE runstatus IN (3,5,6)
AND comments NOT LIKE '%were delivered.'
GROUP BY agent_id
) errors
INNER JOIN
(SELECT agent_id,
MAX(TIME) AS last_time
FROM distribution.dbo.MSdistribution_history with (nolock)
WHERE runstatus IN (1,2,4)
OR comments LIKE '%were delivered.'
GROUP BY agent_id
) clean
ON errors.agent_id = clean.agent_id
AND errors.last_TIME > clean.last_time
--grab the agent information
INNER JOIN distribution.dbo.MSdistribution_agents agentinfo
ON agentinfo.id = errors.agent_id
--and the actual message we'd see in the monitor
LEFT OUTER JOIN distribution.dbo.MSdistribution_history error_messages
ON error_messages.agent_id = errors.agent_id AND error_messages.time = errors.last_time
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply