Tuesday, February 15, 2011

The subscription(s) have been marked inactive and must be reinitialized

Yesterday we've found out that our SQL Server replication has been down for 2 days. After reinitializing the subscriptions I wanted to find out what caused the problem. After some searching on the internet I've found a very usefull Stored Procedure which can help you what the query is when you see replication errors.
use distribution
exec sp_browsereplcmds
 @xact_seqno_start = '0x<first 20 bytes of Transaction sequence number>',
 @xact_seqno_end = '0x<first 20 bytes of Transaction sequence number>'
But when the replication has been down for quite a while it's impossible to see what caused the problem in SQL. Allthough it wasn't the first thing that came to my mind, it can be found in the event log:


Hopefully this helps.