LittleKendra.com

I needed to quickly report on what articles are being replicated from a database yesterday, and it was helpful to include the size of the articles, number of rows, and the subscriber information.

The system tables make this pretty quick work– just run the following against your publisher and you’ll be able to document this easily.

select
	publicationName= sp.name
	, tableType=case p.index_id when 1 then 'Clustered Index' else 'Heap' end
	, p.index_id
	, articleName=ar.name
	, sub.srvname
	, ar.dest_table
	, totalSizeGB = cast(sum(au.total_pages)*8./1024./1024. as numeric(10,1))
	, numRows = (select top 1 [rows] from sys.partitions p2 where p2.object_id=o.object_id and p2.index_id in (0,1))
	, createDate= o.create_date
	, modifyDate= o.modify_date
	, droppedGB = cast(sum(case au.[type] when 0 then au.total_pages else 0 end)*8./1024./1024. as numeric(10,1))
	, inRowGB = cast(sum(case au.[type] when 1 then au.total_pages else 0 end)*8./1024./1024. as numeric(10,1))
	, LOBGB = cast(sum(case au.[type] when 2 then au.total_pages else 0 end)*8./1024./1024. as numeric (10,1))
	, rowOverflowGB = cast(sum(case au.[type] when 3 then au.total_pages else 0 end)*8./1024./1024. as numeric(10,1))
from sysarticles ar
left join syspublications sp on sp.pubid=ar.pubid
left join syssubscriptions sub on
	ar.artid=sub.artid
	and dest_db !='virtual'
left join sys.objects o on ar.objid= o.object_id
left join sys.partitions AS p (NOLOCK) on
	p.object_id = o.object_id
	and p.index_id in (1,0)
left join sys.allocation_units AS au (NOLOCK) on
		au.container_id = p.partition_id
		and p.index_id in (1,0)
group by
	sp.name
	, ar.name
	, sub.srvname
	, ar.dest_table
	, o.name
	, o.object_id
	, o.create_date
	, o.modify_date
	, p.index_id
order by
	1

This post is to share a script I’ve been working on periodically over the last couple of months to monitor and report on replication latency at the distributor. I use this in monitoring transactional replication with a stand-alone distributor.

The basic purpose for the script is:

• Check the number of undelivered commands for all active publications
• Log the data found to a table (which is collected on a centralized server so the history can be displayed graphically in Reporting Services 2008– that code not in this post)
• When the number of undelivered commands is  higher than allowed, fail the job and send an email with a table highlighting the publications that are behind
• Accept a parameter of dynamic length to allow specifying publications that should not be monitored as “production” (ie, do not alert in monitoring). Our repl servers are not SQL 2008 yet, so I use an XML wad rather than a TVP. I do love TVP’s for this type of thing though.
• And be able to accomplish all these tasks from a non-system database.  (No custom monitoring code in system or application dbs!)

So this script shows not only how to check for undelivered commands, but also how to build a quick and easy HTML table to summarize data in emails. I always really like having as much data as possible about the state of the system in an email which is requesting investigation. (For monitoring tie in/SQL job failures are written to the event log which are then picked up.)

Note: there are some email addresses at the bottom of the sproc which I should have parameterized.  I am leaving this way because everyone should read the sproc thoroughly before installing it anywhere

The table for Logging

I pull all results to a centralized server, so this table stays small

Continue Reading →