SQL Server Performance

Auditing Replication

Discussion in 'SQL Server 2005 Replication' started by viksar, Jun 13, 2007.

  1. viksar New Member

    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
  2. MohammedU New Member

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

    Hi mohammedU

    This is not working, Its coming up with error messages like

    Invalid column name 'freq_type'.

    Invalid column name 'active_start_time'.

  4. MohammedU New Member

    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.
  5. viksar New Member

    Its coming up with error message as

    "Invalid column name 'job_id'."
  6. MohammedU New Member

    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.
  7. viksar New Member

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

Share This Page