Interesting thing happened today, but never mind that. We had a server go down recently in our staging environment, wasn’t any big deal, we had to have vendor support come out and replace the motherboard. It was stage, not a big deal. The thing was, the server was down for a couple of days. Actually it was down for a total of 4 days.

The server in question is a SQL 2008 subscriber to several published tables on the staging OLTP SQL 2008 Instance (using push subscriptions). Sadly, not thinking of this ahead of time, we neglected to adjust the maximum distribution retention period for transactional replication (which sits at 72 hours). Suffice to say, the server came back online, we restarted the distribution agents we got the dreaded “subscription marked as inactive” error requiring us to have to reinitialize all of the subscriptions to that server.

“There goes my afternoon” thought I. Not being a fan of the replication monitor in 2008 I dreaded having to work with it to go through and reinitialize each and every publication for that subscriber, especially given that there are around 80 different articles. There must be a better way I thought…and lo, there is!

Books online talks about sp_reinitsubscription and passing parameters for the publication, article, subscriber, destination database, for schema change, publisher, ignore distributor failover and invalidate snapshot.

What BOL is not clear about it that you can pass in just a subscriber, that’s it, nothing else.

exec sp_reinitsubscription @subscriber = ‘<sql instance>’;

Running this reinitialized all of the publications within the current database for that one subscriber. Very quick, very easy. From there I just had to kickstart the snapshot agents and we were back in business (sp_start_job in this case ftw).