How do I determine if replication is being done within a SQL 2005 instance?

• Sodtke

  SSC Eights!

  Points: 982

  More actions

  October 3, 2007 at 12:37 pm

  #175627

  I would like to ask the members if there is a method via transact SQL or via system stored procedures within a MS SQL 2005 instance to determine if replication is being done on the system.

  I could easily pull up SSMS, but am automating a script to email me some vital stats and would like to know if this is possible?

  Is the presence of the 'distribution' db on the server a good enough indication that replication is being done?

  Thanks in advance.

• MarkusB

  SSC-Dedicated

  Points: 37370

  More actions

  October 3, 2007 at 12:54 pm

  #738756

  No, the presence of a database called distribution is not enough. Three reason for this are:

  1. The distributor could be on another server

  2. Replication might no longer be used

  3. You can name the distrbution database different.

  This should give you a better indication

  SELECT COUNT(*) FROM sys.databases

  WHERE is_published = 1

  OR is_subscribed = 1

  Markus

  [font="Verdana"]Markus Bohse[/font]

• Sodtke

  SSC Eights!

  Points: 982

  More actions

  October 3, 2007 at 1:01 pm

  #738761

  Thanks MarkusB for the quick response. That would do it.

  Greatly appreciated.

• noeld

  SSC Guru

  Points: 96590

  More actions

  October 3, 2007 at 3:46 pm

  #738848

  NOTE that is_subscribed does not work in all cases

  
  * Noel