SQL Server Storage Engine

How to recreate the msdb database in SQL Server 2005?

I've just spent a bunch time researching an answer to this question on the new disaster recovery forum because I couldn't find any definitive info on how to do this in SQL Server 2005. I pieced together a method to do this on previous releases of SQL Server and tried it on one of the SQL Server 2005 instances on my laptop to make sure it works. I'd like to share it with everyone to save someone having to do the same.

If your msdb goes suspect then you have two choices, restore it from a backup or recreate it (and then recreate any scheduled jobs). Obviously everyone has a comprehensive and valid set of backups, right? If only...

Of course, the very first thing you do is work out why it went suspect in the first place and take any necessary steps to stop it happening again.

Now you'd hope that if you don't have a valid msdb backup then you can at least run repair on it and so you don't lose everything in there. Well, that works as long as the transaction log isn't damaged. Ok, but then surely we can stick the database into the now-documented emergency mode (alter database dbname set emergency) and run emergency mode repair? (dbcc checkdb (dbname, repair_allow_data_loss) in emergency mode). Nope, msdb can't be put into emergency mode.

So, you're out of options and you're going to have to recreate msdb. Here's what to do (change the directory paths to suit your installation):

  1. Detach the damaged msdb. You can't just detach msdb because you're not allowed to detach system databases. However, you can if you start the server with trace flag 3608. I did this by shutting down the server, navigating to the directory 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn' and doing the following: start sqlservr.exe -c -T3608
  2. Move or rename the damaged msdb files (msdbdata.mdf and msdblog.ldf in the 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data' directory)
  3. Run the instmsdb.sql script in the 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install' directory
  4. Shutdown and restart the server without the 3608 trace flag

This works on SQL Server 2000 as well.

Hopefully you'll never have to do this but if you ever do, let me know how it goes for you.

Published Tuesday, June 06, 2006 7:01 PM by Paul Randal - MSFT
Filed under:

Comments

 

jeremyts said:

Good info Paul. But I'm wondering if this works on SQL 2005 Express Edition?

When running step 3, I get the following error....

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install>sqlcmd -i instmsdb.sql
----------------------------------
Starting execution of INSTMSDB.SQL
----------------------------------
Configuration option 'allow updates' changed from 1 to 1. Run the RECONFIGURE statement to install.
Changed database context to 'master'.
Msg 5120, Level 16, State 101, Server DC1, Line 63
Unable to open the physical file "c:\Program Files\Microsoft SQL Server\MSSQL.1\ MSSQL\DATA\MSDBData.mdf". Operating system error 2: "2(error not found)".
File activation failure. The physical file name "c:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\MSDBLog.ldf" may be incorrect.
Msg 945, Level 14, State 2, Server DC1, Line 63
Database 'msdb' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.
Msg 3446, Level 16, State 2, Server DC1, Line 3
Primary log file is not available for database 'msdb'.  The log cannot be backed up.
Msg 5069, Level 16, State 1, Server DC1, Line 3
ALTER DATABASE statement failed.
Msg 945, Level 14, State 2, Server DC1, Line 2
Database 'msdb' cannot be opened due to inaccessible files or insufficient memor
y or disk space.  See the SQL Server errorlog for details.
Msg 50000, Level 20, State 127, Server DC1, Line 4
A problem was encountered accessing msdb. INSTMSDB.SQL terminating.

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install>

Are you able to provide advice?

Cheers,
Jeremy.
June 8, 2006 8:24 AM
 

Paul Randal - MSFT said:

Hmm - I just tried it on my Express instance on my laptop and it worked fine. Are you trying to do it on a RANU instance? I ran:

sqlcmd /E /S.\SQLEXPRESS /Iinstmsdb.sql
June 8, 2006 4:41 PM
 

wmrock said:

Great Information!

This is what I have been looking for for about 7 months, sql server on win 2000 has been stopped due to bad data base, can not run restore as backup requires sql to be running. catch 22.   As the bad data base is one this business has not used in years, your method is a great tool to use.
 I only have one problem.  I just because responsible to this server and have no documentation.
I run steps 1 and 2 , but the thirdstep of
"Run the instmsdb.sql script in the 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install' directory"  is the problem.  How or what do I run it with?   sqlcmd does not seem to be system.

thanks for any advice you might give.
regards
June 22, 2006 3:00 AM
 

Paul Randal - MSFT said:

Try using 'osql' instead of 'sqlcmd'
June 23, 2006 8:04 PM
 

Vinod Kumar's Blog said:

Good step by step approach by the Storage engine blog. Will help quite a lot of folks :) ...
How to...
June 28, 2006 1:58 AM
 

apms said:

Great information. Thanks! But...

Still have a problem. Installed MSDE2K as an instance and ran sp3 on that instance.

Sp setup breaked at running scripts, saying database msdb log file was not good, look at the SQL log file (where no info was...)

Than stumbled over this usefull piece of information. Tried this, but this is not ok. Could you prompt me what's the problem?

C:\Program Files\Microsoft SQL Server\MSSQL$DRUMIS\BinnMSSQL$DRUMIS\Binn>sqlservr.exe -c -T3608 -s DRUMIS

C:\Program Files\Microsoft SQL Server\80\Tools\Binn>OSQL.EXE -S"RATIOSERVER\DRUMIS" -d"Master" -U********** -P********** -i"C:\Program Files\Microsoft SQL Server\MSSQL$DRUMIS\DataMSSQL$DRUMIS\Install\instmsdb.sql"

***********SQL-Server log*****************

2006-11-14 17:10:34.07 server    Microsoft SQL Server  2000 - 8.00.760 (Intel X8

6)

       Dec 17 2002 14:22:05

       Copyright (c) 1988-2003 Microsoft Corporation

       Desktop Engine on Windows NT 5.2 (Build 3790: )

2006-11-14 17:10:34.09 server    Copyright (C) 1988-2002 Microsoft Corporation.

2006-11-14 17:10:34.09 server    All rights reserved.

2006-11-14 17:10:34.09 server    Server Process ID is 2100.

2006-11-14 17:10:34.09 server    Logging SQL Server messages in file 'C:\Program

Files\Microsoft SQL Server\MSSQL$DRUMIS\DataMSSQL$DRUMIS\LOG\ERRORLOG'.

2006-11-14 17:10:34.11 server    SQL Server is starting at priority class 'norma

l'(2 CPUs detected).

2006-11-14 17:10:34.29 server    SQL Server configured for thread mode processin

g.

2006-11-14 17:10:34.29 server    Using dynamic lock allocation. [500] Lock Block

s, [1000] Lock Owner Blocks.

2006-11-14 17:10:34.34 spid4     Recovering only master database

2006-11-14 17:10:34.34 spid4     Starting up database 'master'.

2006-11-14 17:10:34.53 server    Using 'SSNETLIB.DLL' version '8.0.766'.

2006-11-14 17:10:34.56 spid4     Server name is 'RATIOSERVER\DRUMIS'.

2006-11-14 17:10:34.57 server    SQL server listening on 192.168.1.100: 3676.

2006-11-14 17:10:34.57 server    SQL server listening on 127.0.0.1: 3676.

2006-11-14 17:10:34.59 spid4     Recovery complete.

2006-11-14 17:10:34.59 spid4     SQL global counter collection task is created.

2006-11-14 17:10:34.59 server    SQL server listening on TCP, Shared Memory, Nam

ed Pipes.

2006-11-14 17:10:34.59 server    SQL Server is ready for client connections

2006-11-14 17:10:34.61 spid4     Warning: override, autoexec procedures skipped.

2006-11-14 17:11:05.10 spid51    Starting up database 'D4WData'.

2006-11-14 17:23:41.42 spid51    DBCC TRACEON 1717, server process ID (SPID) 51.

2006-11-14 17:23:41.90 spid51    Clearing tempdb database.

2006-11-14 17:23:41.90 spid51    Starting up database 'model'.

2006-11-14 17:23:42.23 spid51    Starting up database 'tempdb'.

2006-11-14 17:23:42.31 spid51    Error: 15457, Severity: 0, State: 1

2006-11-14 17:23:42.31 spid51    Configuration option 'allow updates' changed fr

om 1 to 1. Run the RECONFIGURE statement to install..

2006-11-14 17:23:42.71 spid51    Starting up database 'msdb'.

2006-11-14 17:23:42.71 spid51    udopen: Operating system error 2(error not foun

d) during the creation/opening of physical device C:\Program Files\Microsoft SQL

Server\MSSQL$DRUMIS\DataMSSQL$DRUMIS\Data\msdbdata.mdf.

2006-11-14 17:23:42.73 spid51    FCB::Open failed: Could not open device C:\Prog

ram Files\Microsoft SQL Server\MSSQL$DRUMIS\DataMSSQL$DRUMIS\Data\msdbdata.mdf f

or virtual device number (VDN) 1.

2006-11-14 17:23:43.14 spid51    Error: 50000, Severity: 20, State: 127

2006-11-14 17:23:43.14 spid51    A problem was encountered accessing msdb. INSTM

SDB.SQL terminating..

***********OSQL output*****************

-----------------------------

Starting execution of INSTMSDB.SQL

----------------------------------

Configuration option 'allow updates' changed from 1 to 1. Run the RECONFIGURE statement to install.

Msg 5105, Level 16, State 4, Server RATIOSERVER\DRUMIS, Line 57

Device activation error. The physical file name 'C:\Program Files\Microsoft SQL Server\MSSQL$DRUMIS\DataMSSQL$DRUMIS\Data\msdbdata.mdf' may be incorrect.

Msg 945, Level 14, State 2, Server RATIOSERVER\DRUMIS, Line 57 Database 'msdb' cannot be opened due to inaccessible files or insufficient memory or disk space.

 See the SQL Server errorlog for details.

Device activation error. The physical file name 'C:\Program Files\Microsoft SQL Server\MSSQL$DRUMIS\DataMSSQL$DRUMIS\Data\msdblog.ldf' may be incorrect.

Msg 945, Level 14, State 2, Server RATIOSERVER\DRUMIS, Line 1

Database 'msdb' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.

Msg 5069, Level 16, State 1, Server RATIOSERVER\DRUMIS, Line 1

ALTER DATABASE statement failed.

sp_dboption command failed.

Msg 945, Level 14, State 2, Server RATIOSERVER\DRUMIS, Line 2

Database 'msdb' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.

Msg 50000, Level 20, State 127, Server RATIOSERVER\DRUMIS, Line 4

A problem was encountered accessing msdb. INSTMSDB.SQL terminating.

November 14, 2006 6:23 PM
 

Paul Randal - MSFT said:

Looks like the pathname is screwed up (the section "MSSQL$DRUMIS\Data" is repeated - did you enter it manually? Please shoot me email rather than debugging this through the comments - thanks

November 15, 2006 7:50 AM
 

apms said:

Solved the problem by installing MSDE on another computer.

Stopping both SQL servers.

Copy msdb.mdf and msdb.ldf from good installation to bad installation and started SQL server again.

Msdb was now correctly recognized. - thanks

November 17, 2006 4:53 PM
 

thenoo said:

In sql 2005, I also got the same insufficient memory error unable to connect to MSDB DB, I just tried the step 1 with -T3608 Parameter, then tried to stop and restart the sql server.Then i opened the sql server managment studio, it was now able to connect to MSDB DB without no error, before i tried with detach,move and recreate the MSDB DB.So i stopped with Step 1 with trace flag option,then removed the trace flag option in startup parameter.It works fine now.

Thanks for the tips.

Regards

Thenu

February 3, 2007 1:58 AM
 

Braulio_Malaga said:

It's the second time that I got an ugly message from my SQL Server: Database 'msdb' cannot be opened...

April 25, 2007 12:57 PM
 

AmberHand said:

Got some problems with dns can create dns server cuz it cant connect to sql server i got this error from dns

Microsoft SQl server login

Connection failed:

SQL state error:'01000'

SQL server error:10061

[Microsoft][ODBC SQl server driver][TCP/IP sockets]Connectionopened(connect())

Connection failed:

Sql state:'08001'

Sql server error:17

[Microsoft][ODBC SQl server driver][TCP/IP sockets]SQL server does not exist or access diened"

May 4, 2007 3:11 PM
 

pstavroulis said:

I have gone through this exercise. If you have installed SP2 on 2005 and follow the steps above then you will get many problems with Mails. By following the above process, you basically uninstall part of SP2 by running the instmsdb.sql file. The problem is you don't see any real error messages, your emails just stay queued and never sent. A test is stored proc sysmail_delete_profile_sp should contain 3 arguments not 2 if you are on SP2.

To fix it, I had to uninstall the 3054 fix + SP2 then re-install SP2 + 3054 and you should be fine. I think Microsoft should provide an updated instmsdb.sql as part of the SP2 installation. Or something similar.

I have used -m as well as my argument and & please make sure that nothings is connected to your sql server.

Panos.

July 17, 2007 5:48 AM
 

navin_vasnani said:

When i am running the thrid step it is showing in error logs for good backup of msdb database.

what to do now

May 20, 2008 7:40 AM
 

random thoughts » Fixing Suspect SQL databases said:

PingBack from http://thought.mobiforumz.com/2006/09/08/fixing-suspect-sql-databases/

June 7, 2008 2:27 AM
 

Random Thoughts » Blog Archive » Fixing Suspect SQL databases said:

PingBack from http://winzenz.mobiforumz.com/2006/09/08/fixing-suspect-sql-databases/

July 11, 2008 6:23 AM
 

TiborKaraszi said:

Here's my findings trying this on SQL Server 2008:

You need to add startup parameter -s <instancename> if it is a named instance. Now, this I knew, but for the sake of other potential readers...

I initially started the instance from the Windows services applet by adding -T3608. That didn't allow for detaching msdb. So I started from an OS command prompt and also added -c. This allowed me to detach msdb.

I now ran instmsdb, but that produced a number of errors. Here are a few comments about some of them:

* Complaints on xp_cmdshell. I did try enabling this first and then ran instmsdb again but same result.

* Bunch of errors when creating various Data Collector objects. This wasn't good, because cleaning up DC was the reason to rebuild msdb in the frist place.

* 3 errors about sp_configure and -1 wasn't allowed value (two for Agent Xps and one for xp_cmdshell).

Just for the sake of trying, I now tried to connect to the instance using SSMS Object Explorer. But I now got some error regarding Agent Xp's when connecting. I tried to explicitly enabling Agent XP's using sp_configure but same error. When connected there's no node in Objects Explorer for Agent.

I took this as an indication that Agent isn't healthy. Whether it was me doing something fishy or it isn't as easy as just running insmsdb.sql for SQL Server 2008 - I don't know. But I'm in for a rebuild of system databases. This isn't that bad since it is a just a test machine. But these issues might serve as example why you want to follow Paul's initial advice: always backup msdb (also on test machines).

August 27, 2008 6:09 AM
 

Tibor Karaszi said:

Because of the problems I had removing Data Collector I decided to rebuild msdb. You probably heard about

August 27, 2008 6:17 AM
 

SQL Server said:

Because of the problems I had removing Data Collector I decided to rebuild msdb. You probably heard about

August 27, 2008 6:20 AM
 

Rebuilding msdb on SQL Server 2008 : EasyCoded said:

PingBack from http://www.easycoded.com/rebuilding-msdb-on-sql-server-2008/

September 1, 2008 1:17 PM
 

SQL Server Storage Engine How to recreate the msdb database in SQL | Paid Surveys said:

PingBack from http://paidsurveyshub.info/story.php?title=sql-server-storage-engine-how-to-recreate-the-msdb-database-in-sql

May 29, 2009 7:13 PM
 

SQL Server Storage Engine How to recreate the msdb database in SQL | Quick Diets said:

PingBack from http://quickdietsite.info/story.php?id=4056

June 13, 2009 9:26 AM
 

SQL Server Storage Engine How to recreate the msdb database in SQL | fix my credit said:

PingBack from http://fixmycrediteasily.info/story.php?id=1150

June 16, 2009 9:31 PM
 

SQL Server Storage Engine How to recreate the msdb database in SQL | debt settlement program said:

PingBack from http://edebtsettlementprogram.info/story.php?id=24418

June 19, 2009 9:33 AM
Anonymous comments are disabled

About Paul Randal - MSFT

Paul started in the industry in 1994 working for DEC on the VMS file system and check/repair tools. In 1999 he moved to Microsoft to work on SQL Server, specifically on DBCC. For SQL Server 2000, he concentrated on index fragmentation (writing DBCC INDEXDEFRAG and DBCC SHOWCONTIG) plus various algorithms in DBCC CHECKDB. During SQL Server 2005 development Paul was the lead developer/manager of one the core dev teams in the Storage Engine, responsible for data access and storage (DBCC, allocation, indexes & heaps, pages/records, text/LOB storage, snapshot isolation, etc). He also spent several years rewriting DBCC CHECKDB and repair. For SQL Server 2008, Paul managed the Program Management team for the core Storage Engine to become more focused on customer/partner engagement and feature set definition. In 2007, after 8.5 years on the SQL Server team, Paul left Microsoft to join his wife, Kimberly Tripp, running SQLskills.com and pursuing his passion for presenting and consulting. Paul regularly presents at conferences and user groups around the world on high-availability, disaster recovery and Storage Engine internals. His popular blog is at http://www.sqlskills.com/blogs/paul/.

This Blog

Syndication

© 2009 Microsoft Corporation. All rights reserved. Terms of Use  |  Trademarks  |  Privacy Statement
Microsoft
Page view tracker