Missing objects in MSDB database

Fri Jul 9, 2004 by Tara Kizer in sql-server-database-administration

In our test environment, we have a SQL Server that has four named instances on it. For one of the instances, I was configuring it to be the distributor for replication. Upon configuring it, I received an error that sp_add_jobstep_internal system stored procedure was missing from the msdb database. I thought to myself how could a system object be missing in a system database. It turns out a lot of objects were missing from msdb and it occurred on all four instances. Now I don't know why it happened as I didn't do the installations and don't know if errors were encountered or if a bad CD was used or something, but I found out how to fix it. I found a link that mentioned running three files when your msdb database is missing. Well ours wasn't missing and it can't be dropped, so I decided to run the three files anyway since it was in a test environment after all. And what do you know, it worked! Here's the three files:

<PathToSQLInstance>\Install\instmsdb.sql
<PathToSQLInstance>\Install\servrmsgs.sql
<PathToSQLInstance>\Install\web.sql

If anyone knows why system objects were missing in the msdb database, I'd like to hear from you. They weren't dropped, I am sure of that.

Legacy Comments


Derrick Leggett 2004-07-09	re: Missing objects in MSDB database My personal belief is that you were smoking weed. Your version got a little blury during the process. At the same time you were running those scripts though, you had a moment of clarity and everything became clear again. It's a vision you'll live with for the rest of your life. Are you happy?

Lee Dise 2004-07-16	re: Missing objects in MSDB database I wonder if there is actually some source code in the SQL 2000 install that indicates where in the process those scripts are executed...? Not that I'd know without checking. But I'm remembering back to a time when we were trying to upgrade our servers from SQL Server 7. As with most wizard-driven processes, 99% of the time, things work great and life is good. But we had one server that failed the upgrade on the first try and absolutely refused to budge an inch thereafter. That's when I discovered there was some sort of an installation "verbose" mode that showed you what was being executed as it was happening, and I actually remember seeing some of the code. I don't like to look at that stuff. (I wound up uninstalling SQL Server 7, installing SQL Server 2000 from scratch, as it were, and then restoring the databases.) I'm thinking that the DBMS may have been installed in two attempts. On the first attempt, once it got past the actual creation of 'msdb', the install itself encountered a bump; and then (on the second try), it fooled itself into thinking those SQL batches had been run, when in fact they had not. Once it saw that 'msdb' had been created, it thought, good, no need to go into that again.... I love to speculate.

Martin Babb 2004-08-03	re: Missing objects in MSDB database I had a similar problem with SQL 2000 on my notebook. After installing sp3a I noticed that enterprise manager was not behaving as expected. I reinstalled the client tools and Enterprise manager (foolishly) from the original CD. Afterwards most things worked, but every time I fired up query analyser or attemped to backup or reconnect a database i received errors. I reinstalled sp3a but to no avail, I eventually got tired of it, removed sql and reinstalled from scratch. Had I looked before no I could have tried your method.