Sql-server – How to check if subscriber replication initialization has timed out

replicationsql server

I've added merge replication to a SQL Server 2005 database and have added a SQL Server 2000 subscriber. Creation of the publisher, subscriber and snapshot creation went fine, but there seems to be a problem with the initialization.

After about 1 hour and 40 minutes the following error appeared in replication monitor during the initialization stage

The replication agent has not logged a progress message in 10 minutes. This might indicate an unresponsive agent or high system activity. Verify that records are being replicated to the destination and that connections to the Subscriber, Publisher, and Distributor are still active.

As far as I can tell this process is still running as sp_who2 gives the following:

SPID  Status                         Login HostName BlkBy DBName   Command     CPUTime DiskIO LastBatch      ProgramName                                  SPID
----- ------------------------------ ----- -------- ----- -------- ----------- ------- ------ -------------- -------------------------------------------- -----
56    RUNNABLE                       sa    HOST   .   database BULK INSERT 27126   287567 09/26 10:03:16 HOST-database-database_pub-REMOTEHOST-6 56   

and select * from sysprocesses gives

spid   kpid   blocked waittype waittime    lastwaittype                     waitresource dbid   uid    cpu         physical_io          memusage    login_time              last_batch              ecid   open_tran status                                                                                   nt_username                                                                                                                      net_address  net_library  loginame
------ ------ ------- -------- ----------- -------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------ ------ ----------- -------------------- ----------- ----------------------- ----------------------- ------ --------- --------
56     5392   0       0x0042   31          OLEDB                                          7      0      27266       288945               0           2011-09-26 10:02:29.717 2011-09-26 10:03:16.780 0      2         runnable                                                                                                                                                                                                                  000C29092F59 TCP/IP       sa 

Is the process still running or has it got stuck and are there any tools I can use to see what's going on here?

Thanks,

Tom

Update

The process described above was no longer visible from sp_who2 or sysprocesses around 6 hours later. There don't seem to have been any errors in the logs, and it doesn't look like the subscriber was correctly initialised as the agent attempts to initialise it again on synchronization. Does anyone have any further ideas?

Kind regards,

Tom

Best Answer

Use SQL Server Replication Monitor in order to review the current state of your Replication topology.

See Monitoring Replication with Replication Monitor