Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Using T-SQL to Verify Tables Row Counts in Transactional Replication Expand / Collapse
Author
Message
Posted Thursday, August 5, 2010 2:38 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:44 PM
Points: 21,187, Visits: 14,879
Thanh Ngay Nguyen (8/5/2010)
Good point, Jason. Thanks!

Instead of using dbo.sysindexes, sys.partitions can be used to obtain the rowcount

SELECT  ( SCHEMA_NAME(o.schema_id) + '.' + OBJECT_NAME(o.object_id) ) AS TblName,
p.rows AS RowCnt
FROM sys.partitions AS p
INNER JOIN sys.objects AS o ON p.object_id = o.object_id
WHERE o.type_desc = 'USER_TABLE'
AND p.index_id IN ( 0, 1 )



You're welcome and thanks for updating your script.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #964710
Posted Thursday, August 5, 2010 8:59 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 7, 2014 12:02 PM
Points: 6, Visits: 159
Another benefit of using the built in sp_publication_validation or sp_article_validation stored procs is that if the validation fails during execution, it will raise an error to the application log. There's a built in alert for replication validation failures which can automatically reinitialize the subscriptions, or just send email notifications via Database Mail to all concerned parties, or do both.
Post #964821
Posted Friday, August 6, 2010 10:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 21, 2013 4:45 AM
Points: 11, Visits: 48
The built in validation procedures (article and publication) have existed since at least SQL Server 7.0. The parameters have not changed, so the same code that worked on SQL Server 7.0 would work on every version through SQL Server 2008 R2. (I can't remember if the validation procedures existed in SQL Server 6.5, but I do know they are there from 7.0 onwards.)

Michael Hotek
President - Champion Valley Software, Inc.
http://www.ChampionValleySoftware.com
Post #965329
Posted Friday, August 6, 2010 2:00 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 17, 2014 12:39 PM
Points: 33, Visits: 268
Given some of the limitations with Replication Monitoring, problems with stale subscriptions on large tables etc it's a solid approach.

Kudos Thanh -- nice article!





Post #965444
Posted Tuesday, May 22, 2012 7:26 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, July 14, 2014 12:25 AM
Points: 661, Visits: 460
Hi Guys,

Does anyone get a syntax issue on the below piece of code?

IF OBJECT_ID('tempdb..#tempSubscribedArticles') IS NOT NULL 
DROP TABLE #tempSubscribedArticles

CREATE TABLE #tempSubscribedArticles
(
subscriber_srv VARCHAR(255),
subscriber_db VARCHAR(255),
destination_owner VARCHAR(255),
destination_object VARCHAR(255),
object_type VARCHAR(255),
rowcount_subscriber INT
)

DECLARE @sub_srv VARCHAR(255),
@sub_db VARCHAR(255),
@strSQL_S VARCHAR(4000)

DECLARE db_cursor_s CURSOR
FOR SELECT DISTINCT
subscriber_srv,
subscriber_db
FROM #tempTransReplication

OPEN db_cursor_s
FETCH NEXT FROM db_cursor_s INTO @sub_srv, @sub_db

WHILE @@FETCH_STATUS = 0
BEGIN
SET @strSQL_S = 'SELECT ' + '''' + @sub_srv + ''''
+ ' AS subscriber_srv, ' + '''' + @sub_db + ''''
+ ' AS subscriber_db, '
+ 's.name AS destination_owner, o.name AS destination_object, o.Type_Desc AS object_type, i.rowcnt AS rowcount_subscriber
FROM ' + @sub_srv + '.' + @sub_db + '.sys.objects AS o
INNER JOIN ' + @sub_srv + '.' + @sub_db
+ '.sys.schemas AS s on o.schema_id = s.schema_id
LEFT OUTER JOIN ' + @sub_srv + '.' + @sub_db
+ '.dbo.sysindexes AS i on o.object_id = i.id
WHERE ' + '''' + @sub_srv + '.' + @sub_db + ''''
+ ' + ' + '''' + '.' + ''''
+ ' + s.name' + ' + ' + '''' + '.' + '''' + ' + o.name'
+ ' IN (SELECT subscriber_srv + ' + '''' + '.' + ''''
+ ' + subscriber_db + ' + '''' + '.' + ''''
+ ' + destination_owner + ' + '''' + '.' + ''''
+ ' + destination_object FROM #tempTransReplication)
AND ISNULL(i.indid, 0) IN (0, 1)
ORDER BY i.rowcnt DESC'
-- heap (indid=0); clustered index (indix=1)
INSERT INTO #tempSubscribedArticles
EXEC ( @strSQL_S
)

FETCH NEXT FROM db_cursor_s INTO @sub_srv, @sub_db
END
CLOSE db_cursor_s
DEALLOCATE db_cursor_s


I get this error when I am running it.


Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '-'.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'AND'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '-'.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'AND'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '-'.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'AND'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '-'.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'AND'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '-'.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'AND'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '-'.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'AND'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '-'.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'AND'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '-'.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'AND'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '-'.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'AND'.


Any advice will be very helpful
Post #1304144
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse