March 3, 2009 at 6:35 am
Hi Guys
New to SQL coding so can someone please help? I monitoring our replication and would like to take a row count of a table ‘table1’ and match it to the replication table on our tier 2 and 3 environment .
SELECT COUNT(*)AS alias FROM [servername].DB.dbo.Table
SELECT COUNT(*)AS alias FROM [servername].DB.dbo.Table
SELECT COUNT(*)AS alias FROM [servername].DB.dbo.Table
SELECT COUNT(*)AS alias FROM [servername].DB.dbo.Table
SELECT COUNT(*)AS alias FROM [servername].DB.dbo.Table
But I don’t want to receive and email everytime indication everything is ok. Ideally I would like to receive and email\ alert when the row counts do not match.
The one table I am looking at sits on 5 different servers. Sorry bit stuck !!
Also If I wanted to do row counts for mutiple tables accross the servers i.e table1 on all five servers, table 2, table 3 etc.
Is this the right way to check data for 2005 & 2000 and can it be done?
Thanks in advance for your help!!
March 3, 2009 at 7:18 am
Hi MC
To count tables on multiple servers you can use linked servers, What are you exactly trying to do here ??:)
March 3, 2009 at 8:10 am
Basically we have had a few problems with replication and I think this is resloved now, but I want to monitor it for SQl 2000 & 2005. However I would like to take a row count of all replicated tables (link servers already set up) against each other to see if the row count is correct.
If the row count is out by say 100, then send an email to the DBA with the information to Investigate .
i.e
Table users replicates from tier1 to tier 2 & three, I want to know if the row counts for each table are the same and replication is functioning correctly.
Hope this helps, and thanks
March 3, 2009 at 10:58 am
Normally sp_publication_validation is what is used, any custom rule you will have to program it yourself.
* Noel
March 4, 2009 at 3:58 am
Hi Guys,
I am also using the following code to check transactions (replication) for delivered and undelivered transactions which output to a text file and then the second step picks up the text and sends it by email every hour.
How can I amend this so instead of receiving an email every hour, I will only receive an email if the undelivered transactions exceed say 100.
SELECT t.article_id,
a.publisher_db,
a.destination_object,
p.publication,
s.agent_id,
'UndelivCmdsInDistDB' =SUM(CASE WHEN xact_seqno > h.maxseq THEN 1 ELSE 0 END),
'DelivCmdsInDistDB' =SUM(CASE WHEN xact_seqno <= h.maxseq THEN 1 ELSE 0 END)
FROM (SELECT article_id,publisher_database_id, xact_seqno FROM MSrepl_commands (NOLOCK) ) AS t
JOIN (SELECT agent_id,article_id,publisher_database_id, publisher_db FROM MSsubscriptions (NOLOCK)
WHERE [subscriber_db] NOT LIKE 'virtual' ) AS s
ON (t.article_id = s.article_id AND t.publisher_database_id=s.publisher_database_id )
JOIN (SELECT agent_id,'maxseq'= isnull(max(xact_seqno),0x0) FROM MSdistribution_history (NOLOCK)
GROUP BY agent_id) AS h
ON (h.agent_id=s.agent_id)
JOIN (SELECT article_id, destination_object, publisher_db, publication_id FROM MSarticles (NOLOCK)) AS a
ON t.article_id = A.article_id AND a.publisher_db = s.publisher_db
JOIN (SELECT publication, publication_id FROM MSPublications (NOLOCK)) AS p
ON a.publication_id = p.publication_id
GROUP BY
t.article_id,
a.publisher_db,
a.destination_object,
s.agent_id,
p.publication
having SUM(CASE WHEN xact_seqno > h.maxseq THEN 1 ELSE 0 END) > 0
ORDER BY
6 DESC,
a.publisher_db,
a.destination_object,
p.publication,
s.agent_id,
t.article_id
GO
Thanks guys for your help, it very much appreciated.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply