Fixing Replication Monitor on upgraded SQL 2008 Server

By October 27, 2010Database

Recently I had to upgrade an instance of SQL Server 2000 Publisher/Distributor to SQL Server 2008. The in-place upgrade process from Microsoft went as expected and merge replication picked up where it stopped prior to the upgrade.

There was however an issue with the Replication Monitor. It failed to connect to the Distributor. What’s even worse it did not give any indication as to why not and what was happening behind the scenes.

After a little bit of digging in the logs I found this little tidbit:

Msg 515, Level 16, State 2, Procedure sp_replmonitorrefreshdata, Line 386
Cannot insert the value NULL into column 'isagentrunningnow', table 'distribution.dbo.MSreplication_monitordata'; column does not allow nulls. INSERT fails.
The statement has been terminated.

Which led me to this knowledge base article:

You could also run this:

and see what is causing the issue.

Unfortunately the proposed workaround is not 100% correct. After applying the changes to the ‘distrubtion’ database I got similar errors, just on different columns in that table. It turns out that most of the columns in that table do not allow nulls. To make the long story short here is the alter script that should “fix” the issue:

and if you run the replication monitor stored proc again you should see that it succeeds this time around.

Leave a Reply