info@techdevops.com
|
437-991-3573
|
Data Engineering Services
|
TechDevOps.com
|
| | | |
|
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance
|
| | | |
|
Microsoft SQL Server Replication - Distribution Agent - History, Status, Stats, Errors
by BF (Principal Consultant; Architecture; Engineering)
2016-03-18
Solution:
These reports assumes your Distribution DB is called "Distribution". If not - adjust the T-SQL accordingly.
1. Distribution - Transactions & Commands in Distribution:
--Distribution - Total Transactions:
Select p.id, p.publisher_db, count(*) as 'Total Transactions'
from distribution.dbo.MSpublisher_databases p
inner join distribution.dbo.MSrepl_transactions t
on p.id = t.publisher_database_id
group by p.id, p.publisher_db
--Distribution - Total Commands:
Select p.id, p.publisher_db, count(*) as 'Total Commands'
from distribution.dbo.MSpublisher_databases p
inner join distribution.dbo.MSrepl_commands c
on p.id = c.publisher_database_id
group by p.id, p.publisher_db
--Distribution - Total Commands - Per Publisher Per Publication Per Article:
Select p.publisher_db, p2.publication, c.article_id, a.article, count(*) as 'Total Commands in Distro - Per Pub & Article'
from distribution.dbo.MSpublisher_databases p with (nolock)
inner join distribution.dbo.MSpublications p2 with (nolock) on p.publisher_db = p2.publisher_db
inner join distribution.dbo.MSarticles a with (nolock) on p2.publication_id = a.publication_id and p2.publisher_db = a.publisher_db
inner join distribution.dbo.MSrepl_commands c with (nolock) on c.publisher_database_id = p.id and c.article_id = a.article_id
group by p.publisher_db, p2.publication, c.article_id, a.article
order by p.publisher_db, p2.publication, a.article asc
--NOTE:
--1. When the SQL Job [Distribution clean up: distribution] runs, MSrepl_commands table gets cleaned up. i.e Commands are removed that have already been sent to Subscribers.
--2. dbo.sysarticles - this table is not accurate. Use MSarticles instead.
2. Get MSdistribution_history cmdspersec rate:
Select
t1.time,
t1.agent_id as 'AgentID',
t2.name as 'Agent Name',
comments
from Distribution.dbo.MSdistribution_history t1 with (nolock)
inner join Distribution.dbo.MSdistribution_agents t2 with (nolock)
on t1.agent_id = t2.id
where [time] > getdate() - 1
and comments like '%cmdspersec%'
order by time desc
Get cmdspersecrate extracted:
Inputs: Get AgentID AND Stats State ID from above query (State Details)
Important: sincelaststats_cmdspersec
declare @agent_id int = 21;
with cte_stats as(
select h.agent_id, ag.name, h.time, cast(h.comments as xml) comments
from MSdistribution_history h with (nolock)
inner join Distribution.dbo.MSdistribution_agents ag with (nolock) on h.agent_id = ag.id
where
h.[comments] like '<stats state="2"%'
and h.agent_id = @agent_id
)
select
cte_stats.agent_id
,cte_stats.name
,cte_stats.time
,c.value('(/stats/@state)[1]', 'int') AS [state]
,c.value('(/stats/@work)[1]', 'int') AS [work]
,c.value('(/stats/@idle)[1]', 'int') AS [idle]
,c.value('(/stats/reader/@fetch)[1]', 'int') AS [reader_fetch]
,c.value('(/stats/reader/@wait)[1]', 'int') AS [reader_wait]
,c.value('(/stats/writer/@write)[1]', 'int') AS [writer_write]
,c.value('(/stats/writer/@wait)[1]', 'int') AS [writer_wait]
,c.value('(/stats/sincelaststats/@elapsedtime)[1]', 'int') AS [sincelaststats_elapsedtime]
,c.value('(/stats/sincelaststats/@work)[1]', 'int') AS [sincelaststats_work]
,c.value('(/stats/sincelaststats/@cmds)[1]', 'int') AS [sincelaststats_cmds]
,c.value('(/stats/sincelaststats/@cmdspersec)[1]', 'float') AS [sincelaststats_cmdspersec]
,c.value('(/stats/sincelaststats/reader/@fetch)[1]', 'int') AS [sincelaststats_reader_fetch]
,c.value('(/stats/sincelaststats/reader/@wait)[1]', 'int') AS [sincelaststats_reader_wait]
,c.value('(/stats/sincelaststats/writer/@write)[1]', 'int') AS [sincelaststats_writer_write]
,c.value('(/stats/sincelaststats/writer/@wait)[1]', 'int') AS [sincelaststats_writer_wait]
,cte_stats.[comments]
from cte_stats
cross apply cte_stats.comments.nodes('/stats[@state="2"]') b(c)
order by cte_stats.agent_id, cte_stats.name, cte_stats.time desc;
3. Get the Max Average delivered commands per second in last 24 hours:
select t1.agent_id, t2.name, max(t1.delivery_rate) as maxDeliveryrate, FORMAT(max(t1.delivery_rate),'#,0') maxDeliveryrate_formatted
from Distribution.dbo.MSdistribution_history t1
inner join Distribution.dbo.MSdistribution_agents t2 with (nolock)
on t1.agent_id = t2.id
where t1.time > getdate() -1
group by t1.agent_id, t2.name
4. Distribution History: All
Use Distribution
go
Select
t1.[Time], --The time the message is logged.
t1.runstatus as 'RunStatusID',
Case t1.runstatus
When 1 Then 'Start'
When 2 Then 'Succeed'
When 3 Then 'In progress'
When 4 Then 'Idle'
When 5 Then 'Retry'
When 6 Then 'Fail'
End as 'RunStatus',
t1.agent_id as 'AgentID',
t2.name as 'Agent Name',
t2.publisher_db,
t2.publication,
t2.subscriber_db,
Case t2.subscription_type
When 0 Then 'Push'
When 1 Then 'Pull'
When 2 Then 'Anonymous'
End as 'Subscription Type',
t2.subscriber_login,
t1.error_id,
t1.comments as 'Details'
from Distribution.dbo.MSdistribution_history t1 with (nolock)
inner join Distribution.dbo.MSdistribution_agents t2 with (nolock)
on t1.agent_id = t2.id
order by [Time] asc
5. Distribution History: Delivered
Use Distribution
go
SELECT
publisher.name as 'PublisherServer', t2.publication, publisher_db,
subscriber.name as 'SubscriberServer',
subscriber_db, t1.comments as 'Comments: Delivery Stats', t1.[time]
FROM [Distribution].dbo.MSdistribution_history t1
INNER JOIN [Distribution].dbo.MSdistribution_agents t2 ON t1.agent_id = t2.id
INNER JOIN [Master].sys.servers publisher ON t2.publisher_id = publisher.server_id
INNER JOIN [Master].sys.servers subscriber ON t2.subscriber_id = subscriber.server_id
WHERE
[Time] > dateadd(MI,-15,getdate())
AND Comments like '%delivered%'
--AND (comments not like '%Stats state%'
--AND comments <> 'No replicated transactions are available.'
--AND comments not like '%passed rowcount%'
--AND comments not like '%Initializing%'
--)
ORDER BY subscriber.name asc
6. Distribution: Key Tables Size
USE distribution
GO
SELECT
Object_name(t2.object_id) AS 'Table Name',
t1.row_count as 'Total Records',
Case Object_name(t2.object_id)
WHEN 'MSrepl_commands' THEN 'Rows of Replicated Commands'
WHEN 'MSrepl_transactions' THEN 'One row for each Replicated Transaction'
WHEN 'MSdistribution_history' THEN 'History rows for the Distribution Agents associated with the Local Distributor'
END as 'Description',
getdate() as 'Time'
FROM sys.dm_db_partition_stats t1 WITH (nolock)
INNER JOIN sys.tables t2 WITH (nolock) ON t1.object_id = t2.object_id
INNER JOIN sys.schemas t3 WITH (nolock) ON t2.schema_id = t3.schema_id
WHERE index_id < 2
AND Object_name(t2.object_id)
IN
('MSdistribution_history', 'MSrepl_commands', 'MSrepl_transactions'
)
ORDER BY 3 DESC
7. MSdistribution_status
select * from MSdistribution_status
8. Get Replication statistics re: latency, throughput and transaction count
sp_replcounters
9. Current Distribution Status:
EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 0, @max_distretention = 72
--Removes replicated transactions from the distribution database.
--Max is the maximum amount of time commands will be stored in the distribution database IF subscribers have not picked them up, or if you are using anonymous subscribers.
--Min, is the minimum amount of time commands will be stored in the distribution after they have been replicated to named subscribers.
--PUBLISHER DBs
Select * from MSpublisher_databases
--ROW COUNTS
Select count(*) as 'Total records - MSrepl_transactions' from distribution.dbo.MSrepl_transactions
Select count(*) as 'Total records - MSrepl_commands' from distribution.dbo.MSrepl_commands
SELECT Name, Rowcnt, STATS_DATE (id, indid) as 'Last Update Stats'
FROM distribution.dbo.sysindexes
WHERE name IN('ucMSrepl_transactions', 'ucMSrepl_commands')
ORDER BY Name Desc
select t.publisher_database_id, t.xact_seqno,
max(t.entry_time) as EntryTime, count(c.xact_seqno) as CommandCount
into #results
FROM MSrepl_commands c with (nolock)
LEFT JOIN msrepl_transactions t with (nolock) on t.publisher_database_id = c.publisher_database_id and t.xact_seqno = c.xact_seqno
GROUP BY t.publisher_database_id, t.xact_seqno
SELECT
Publisher_database_id,
datepart(year, EntryTime) as Year, datepart(month, EntryTime) as Month, datepart(day, EntryTime) as Day, sum(CommandCount) as DistributionActiveCommandCountByDay
FROM #results
GROUP BY publisher_database_id, datepart(year, EntryTime), datepart(month, EntryTime), datepart(day, EntryTime)
ORDER BY publisher_database_id, Month, Day
Drop Table #results
SELECT
convert(VARCHAR(25), mdh.[time]) [Log Time],
(CASE WHEN mdh.runstatus = '1'
THEN 'Start - ' + cast(mdh.runstatus AS VARCHAR)
WHEN mdh.runstatus = '2'
THEN 'Succeed - ' + cast(mdh.runstatus AS VARCHAR)
WHEN mdh.runstatus = '3'
THEN 'InProgress - ' + cast(mdh.runstatus AS VARCHAR)
WHEN mdh.runstatus = '4'
THEN 'Idle - ' + cast(mdh.runstatus AS VARCHAR)
WHEN mdh.runstatus = '5'
THEN 'Retry - ' + cast(mdh.runstatus AS VARCHAR)
WHEN mdh.runstatus = '6'
THEN 'Fail - ' + cast(mdh.runstatus AS VARCHAR)
ELSE cast(mdh.runstatus AS VARCHAR)
END ) [Distribution Agent Run Status],
und.UndelivCmdsInDistDB [UnDistributed CMDs],
publisher.name as 'PublisherServer',
mda.Publication,
mda.Publisher_DB,
subscriber.name as 'Subscriber',
mda.subscriber_db [Subscriber DB],
mdh.comments [Comments],
mda.name [Pub - DB - Publication - SUB - AgentID],
(CASE WHEN mda.subscription_type = '0' THEN 'Push'
WHEN mda.subscription_type = '1' THEN 'Pull'
WHEN mda.subscription_type = '2' THEN 'Anonymous'
ELSE cast(mda.subscription_type AS VARCHAR)
END) [SUB Type]
FROM Distribution.dbo.MSdistribution_agents mda
INNER JOIN [Master].sys.servers publisher ON mda.publisher_id = publisher.server_id
INNER JOIN [Master].sys.servers subscriber ON mda.subscriber_id = subscriber.server_id
LEFT JOIN distribution.dbo.MSdistribution_history mdh ON mdh.agent_id = mda.id
JOIN (SELECT s.agent_id,MaxAgentValue.[time],
SUM(CASE WHEN xact_seqno > MaxAgentValue.maxseq THEN 1 ELSE 0 END) AS UndelivCmdsInDistDB
FROM distribution.dbo.MSrepl_commands t (NOLOCK)
JOIN distribution.dbo.MSsubscriptions AS s (NOLOCK)
ON (t.article_id = s.article_id AND t.publisher_database_id = s.publisher_database_id)
JOIN (SELECT hist.agent_id, max(hist.[time]) AS [time], h.maxseq FROM distribution.dbo.MSdistribution_history hist ( NOLOCK )
JOIN (SELECT agent_id,isnull(max(xact_seqno),0x0) AS maxseq FROM distribution.dbo.MSdistribution_history (NOLOCK) GROUP BY agent_id) AS h
ON (hist.agent_id = h.agent_id AND h.maxseq = hist.xact_seqno)
GROUP BY hist.agent_id, h.maxseq) AS MaxAgentValue ON MaxAgentValue.agent_id = s.agent_id
GROUP BY s.agent_id, MaxAgentValue.[time]
) und
ON mda.id = und.agent_id AND und.[time] = mdh.[time]
ORDER BY
mda.Publication,
Subscriber
10. Distribution Errors:
select * from distribution.dbo.msrepl_errors (nolock) order by [time] desc
|
|
|
|
|
|
|
|
|
|