Notes on SQL

In the previous article I covered how to use the Replication Monitor to see what is happening with a Publication and to find out what the errors are.
However, in some circumstances Replication Monitor may not show the entire picture with regard to the health of a Publication.
Information can also be gleaned from the jobs that actually perform the tasks that make the Publication work.

In this example I have removed the login of the Subscriber from the Distributor with a simple DROP LOGIN command.

USE [master]
GO

DROP LOGIN [NT SERVICE\SQLAgent$SUBSCRIBER-C]

GO

Now, on the Publisher I’ll add one row to the table that is part of the Publication:

USE [PublisherDB]
GO

INSERT INTO [ReplTest].[PublicationTable]
           ([EmailAddress]
           ,[DOB]
)
     VALUES
           ('test2@test.com'
           ,'23 Jul 1970'
)
GO

With this Publication the data should be sent at about 30 seconds past each minute. Open the replication Monitor and look at the history of the Distribution:

However, this has been sitting here for several minutes now and the history has not moved – stuck at the last transaction that completed at 13:12:29. There is also no error message displayed, so this screen indicates that there must be a problem (because I know it should run every minute) but gives no details.

The tab for ‘Undistributed Commands’ shows that there is one transaction waiting to get to the Subscriber:

And firing a tracer token never gets to the Subscriber:

From the Tracer Token we can see that the Publisher to Distributor connection must be working and there is no response between the Distributor and Subscriber.

This Publication is a Pull Subscription, so on the Subscriber there is a job that pulls the data from the Distributor. So, look at the job history on the Subscriber. In this case there is only one Job so it is easy to select, but the name should have something within it that shows which Publication it is part of. Within the history for that job there is a long list of failures – one every minute oddly enough:

Expand the ‘tree’ from the red cross and the latest step just tells you there’s a problem and to look either in the previous job step or replication Monitor. We know that Replication Monitor has told us all it is going to, so look at the previous step of this job:

So now we know that the problem is that the job cannot connect to the Distributor. In this case recreating the LOGIN on the Distributor for this job will correct the issue:

USE [master]
GO

CREATE LOGIN [NT SERVICE\SQLAgent$SUBSCRIBER-C] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO

ALTER SERVER ROLE [sysadmin] ADD MEMBER [NT SERVICE\SQLAgent$SUBSCRIBER-C]
GO

And we can see that the Replication monitor shows two transactions successfully processed (because the Tracer Token is a transaction too):

The Tracer token finally completed its journey:

And the Job History looks fine too: