Description: A common question in relation to SQL Replication is how can I check the status or see when a problem is happening that's preventing data from reaching the rollup. Below are screen shots and information on the best location to watch SQL Replication at all stages of the process
Publisher: The client core SQL Server.
Subscriber: The rollup core SQL Server.
Distributor: An intermediary database located on each publisher
1- Launch Replication Monitor. This is done by right-clicking a publication (source/client server) and selecting Replication Monitor. You should see a screen like below:
2- Expand the publishers and click on the desired publisher to monitor.
3- Right click the row on the right and select "View Details". See the two screen shots below:
Notes on the individual tabs: Under the Action menu you can turn of "Auto Refresh" which helps. This overall process will take some time. The SQL Servers are moving a lot of data so patience is important. Also, the "Agents" tab in the initial screenshot above can also help with checking status of initial tasks. See screen shot below for the Agents tab. This tab records information as it is copied from the source core SQL Server to the configured repl (replication) file share. This is the first step of the process. Typical errors include permission problems and issues with running out of space on the drive/share.
Publisher To Distributor History: This tab records details of the data as it goes from the source database file share (publisher and client core) to the distribution database. The distribution database will also be on the client/publisher SQL Server. This will be more utilized after the initial snapshot of the database finishes. After the initial snapshot the log reader takes over and just send up changes.
Distributor To Subscriber History: This tab records information as it is copied from the distribution database to the subscriber (rollup core). This tab is probably the most useful. See screen shot below. The messages "copying" and "copied" indicate if data from the specific table is either processing or has finished processing for the initial snapshot.
Undistributed Commands: If a problem with replication occurs then commands will still be sent from the publisher to the distributor and backup in the distributor database. This may cause space problems on the client SQL Server so this should be watched. If problems happen then either the problem needs to be addressed or the replication process stopped.