on August 11, 2009
I needed to quickly report on what articles are being replicated from a database yesterday. 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.
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