Specs: We're using SQL Server 2005 with transactional replication.

Our MSDB .LDF file has recently become corrupted, causing the MSDB db to be marked as 'suspect'. We read a bunch of stuff online and learned that to fix this you either have to restore from a backup, or you have to rebuild MSDB from the sql install script.

Since we don't have a backup, we tried running the install script, instmsdb.sql (after dropping the msdb db by running the sql server service using the -T3806 -m -c startup params and all that jazz).

This was successful, so I'd planned on rebuilding the distribution db and publishers, and then reinitizaling the subscribers. However, when I tried to access anything in the Sql Server Mgmt Studio menus regarding replication, I'd get an error about missing tables in msdb. One of the specific errors is "invalid object msdb.dbo.MSdistributordbs".

So I figured the system is in an in-between state - the distribution system table is created, but the required replication-specific tables and sps aren't present in the msdb db.

So, my question is one of:

  1. What scripts or stored procedures need to be run so that msdb is replication ready, and/or,
  2. How do I manually remove the publishers and other replication related objects so that I can re-create all the replication stuffs?
  3. Is it even possible to load the msdb without the ldf file somewhere that i've missed?

Edit: One more thing I tried - I enabled replication on the subscriber server, copied it's msdb mdf and log files to the 'bad' server, and tried to attach those. However, I forgot the databases would be incompatible since the publisher is sql server 2005 and the subsriber is sql server 2008. Sigh. This might have worked otherwiser (well at least gotten to the point where I could remove and recreate the replication).

(And just as a side note, our 'sysadmin' in charge of db backup scripts didnt backup any of the system dbs. This has been rectified. sigh).

  • "...Since we don't have a backup..." Uh oh. At the end of the trail, identifying the reason for the corruption would be prudent. – jl. Jul 19 '10 at 16:04
  • Reason for the corruption: Power went out and then our servers' battery backup fan failed, causing it to overheat and shutdown, followed by the poor servers. When they came back up, we noticed the issues with the sql server (rest of data is fine, just replication was foobar'd). The servers have shut down due to power outages a few times before we got the battery backup and nothing was corrupted, so not sure exactly what caused it here. – user48662 Jul 20 '10 at 2:37

It's been a long time since I've had to use replication (and perhaps you've tried this) but are you not able to use the wizard to disable the publisher and distribution db and then re-enable them?

  • Nope - when I try to click on any menu option related to replication I get an error - missing/invalid objects in the msdb database (like mentioned in the last comment). Thanks for posting anyways :) – user48662 Jul 20 '10 at 2:29

You can attach a database with only the mdf. this is a link to a system stored proc that will do it, it also contains a reference to the up-to-date CREATE DATABASE statement that has replaced this proc. I don't know how this will work a system database.

The other option is this document which explains how to remove replication using system stored proc calls. I had to use it once to fix a replicated database that had got its knickers in a twist. I can't remember the details but management studio wouldn't let you remove replication because it wasn't setup and you couldn't create a publication because it already existed.

UPDATE: Did you try using the sp_dropdistributor proc. I'd try that, then try re-creating/configuring the publisher and distributor.

  • Thanks for your response. I'd actually already tried #1 in various fashions before posting the question, but always got an error saying log file not found (I don't know why this happens, but it does). I can attach a normal database without a log file just fine, just not this system one. For option #2, I tried manually running the sp as well, but receive missing/invalid object errors each time (e.g. missing msdb.dbo.msdistributorsdb table). – user48662 Jul 20 '10 at 2:27
  • updated answer. – pipTheGeek Jul 20 '10 at 11:58
  • Yes, I tried using sp_dropdistributor, got same errors as other sp. – user48662 Jul 20 '10 at 13:02
  • I expect the stored procs to generate errors. When I had to use them they all generated errors but still removed what they could. At the end the server was is a consistant enough state for me to replication up again. If this isn't the case could you post what errors you are getting please? Although, I admit I am running out of ideas. Maybe someone at ask.sqlservercentral.com would be able to help. – pipTheGeek Jul 22 '10 at 11:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy