SQL Server Performance Forum – Threads Archive

Auditing Replication

Hi GROUP, Our system is quiet exhausted by replication. We are now looking forward for removing unnecessary replication from our system. We are planning to live without replication for those publication where Number of transactions or commands in a day is less than 100 or 1000. Also we want to trace down duplicate articles within different publication And, we want to check for number of transactions for each publication. We have SQL Server 2005 with SP1 (Enterprise edition) with almost 150 publication publishing around 400 articles.
Does anyone have any script or procedure to extract this information? We need to extract all the above information so if there are separate procedures or script available for this that would be great.
Thanks

Try the following script…I used in sql 2000 but not tested in 2005… SELECT ‘description’ = substring(pub.[description],1, 25), ’subscriber’ = ss.srvname, ‘publication’ = pub.name, ‘article’ = art.name, ‘destination database’ = sub.dest_db, ‘destination Tables’ = art.dest_table, ’Subscription Type’ = case sub.subscription_type when 0 then ‘Push' when 1 then ‘Pull’ end, ‘subscription status’ = case sub.status when 0 then ‘InActive' when 1 then ‘Subscribed' when 2 then ‘Active’ end, ‘synchronization type’ = case sub.sync_type when 1 then ‘Automatic' when 2 then ‘None’ end, ’Frequency’ = CASE sjs.freq_type WHEN 1 THEN ‘Once' WHEN 4 THEN ‘Daily' WHEN 8 THEN ‘Weekly' WHEN 16 THEN ‘Monthly' WHEN 32 THEN ‘Monthly relative' WHEN 64 THEN ‘When SQLServer Agent starts' END, ’Start Time’ = CASE len(active_start_time) WHEN 3 THEN cast(’00:0′ + Left(right(active_start_time,3),1) +’:’ + right(active_start_time,2) as char (<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />) WHEN 4 THEN cast(’00:’ + Left(right(active_start_time,4),2) +’:’ + right(active_start_time,2) as char (<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />) WHEN 5 THEN cast(‘0′ + Left(right(active_start_time,5),1) +’:’ + Left(right(active_start_time,4),2) +’:’ + right(active_start_time,2) as char (<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />) WHEN 6 THEN cast(Left(right(active_start_time,6),2) +’:’ + Left(right(active_start_time,4),2) +’:’ + right(active_start_time,2) as char (<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />) END FROM syssubscriptions sub, master..sysservers ss, syspublications pub, sysextendedarticlesview art, msdb..sysjobschedules sjs WHERE UPPER(ss.srvname) LIKE UPPER(‘%’) collate database_default AND sub.srvid = ss.srvid AND pub.name LIKE ‘%’ collate database_default AND art.name LIKE ‘%’ collate database_default AND art.pubid = pub.pubid AND sub.artid = art.artid AND (sub.login_name = suser_sname(suser_sid()) collate database_default OR is_srvrolemember(‘sysadmin’) = 1 OR is_member (‘db_owner’) = 1) AND sjs.job_id = pub.snapshot_jobid ORDER BY article, publication, subscriber MohammedU. Moderator SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Hi mohammedU This is not working, Its coming up with error messages like Invalid column name ‘freq_type’. Invalid column name ‘active_start_time’.

Try after changing the table name sysjobschedules to sysschedules…. MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Its coming up with error message as "Invalid column name ‘job_id’."

Join sysjobschedule table with sysschedules on scheduleid and see…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

I have made modification to this script however No rows are generated by execution of this script. SELECT ‘description’ = substring(pub.[description],1, 25), ’subscriber’ = ss.srvname, ’publication’ = pub.name, ’article’ = art.name, ’destination database’ = sub.dest_db, ’destination Tables’ = art.dest_table, ’Subscription Type’ = case sub.subscription_type when 0 then ‘Push' when 1 then ‘Pull’ end, ’subscription status’ = case sub.status when 0 then ‘InActive' when 1 then ‘Subscribed' when 2 then ‘Active’ end, ’synchronization type’ = case sub.sync_type when 1 then ‘Automatic' when 2 then ‘None’ end, ’Frequency’ = CASE sjs.freq_type WHEN 1 THEN ‘Once' WHEN 4 THEN ‘Daily' WHEN 8 THEN ‘Weekly' WHEN 16 THEN ‘Monthly' WHEN 32 THEN ‘Monthly relative' WHEN 64 THEN ‘When SQLServer Agent starts' END, ’Start Time’ = CASE len(active_start_time) WHEN 3 THEN cast(’00:0′ + Left(right(active_start_time,3),1) +’:’ + right(active_start_time,2) as char (<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />) WHEN 4 THEN cast(’00:’ + Left(right(active_start_time,4),2) +’:’ + right(active_start_time,2) as char (<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />) WHEN 5 THEN cast(‘0′ + Left(right(active_start_time,5),1) +’:’ + Left(right(active_start_time,4),2) +’:’ + right(active_start_time,2) as char (<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />) WHEN 6 THEN cast(Left(right(active_start_time,6),2) +’:’ + Left(right(active_start_time,4),2) +’:’ + right(active_start_time,2) as char (<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />) END FROM syssubscriptions sub, master..sysservers ss, syspublications pub, sysextendedarticlesview art, msdb..sysschedules sjs WHERE UPPER(ss.srvname) LIKE UPPER(‘%’) collate database_default AND sub.srvid = ss.srvid AND pub.name LIKE ‘%’ collate database_default AND art.name LIKE ‘%’ collate database_default AND art.pubid = pub.pubid AND sub.artid = art.artid AND (sub.login_name = suser_sname(suser_sid()) collate database_default OR is_srvrolemember(‘sysadmin’) = 1 OR is_member (‘db_owner’) = 1) AND sjs.schedule_id = pub.snapshot_jobid ORDER BY article, publication, subscriber

]]>