How to recreate the msdb database in SQL Server 2005?

MSDN Blogs > SQL Server Storage Engine > How to recreate the msdb database in SQL Server 2005?
Server & Tools Blogs > Data Platform Blogs > SQL Server Storage Engine Blog
Live Now on Server & Tools Blogs
Menu
Recent Posts
Tags
More ▼
Archives
Archives
More ▼

How to recreate the msdb database in SQL Server 2005?

Rate This
6 Jun 2006 2:01 PM
  • Comments 30

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.

Leave a Comment
  • Please add 7 and 6 and type the answer here:
  • Post
Comments
  • 8 Jun 2006 8:24 AM
    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.
  • 8 Jun 2006 4:41 PM
    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
  • 22 Jun 2006 3:00 AM
    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
  • 23 Jun 2006 8:04 PM
    Try using 'osql' instead of 'sqlcmd'
  • Vinod Kumar's Blog
    28 Jun 2006 1:58 AM
    Good step by step approach by the Storage engine blog. Will help quite a lot of folks :) ...
    How to...
  • 14 Nov 2006 6:23 PM

    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.

  • 15 Nov 2006 7:50 AM

    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

  • 17 Nov 2006 4:53 PM

    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

  • 3 Feb 2007 1:58 AM

    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

  • Braulio_Malaga
    25 Apr 2007 12:57 PM

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

  • 4 May 2007 3:11 PM

    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"

  • 17 Jul 2007 5:48 AM

    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.

  • 20 May 2008 7:40 AM

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

    what to do now

  • random thoughts » Fixing Suspect SQL databases
    7 Jun 2008 2:27 AM

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

  • Random Thoughts » Blog Archive » Fixing Suspect SQL databases
    11 Jul 2008 6:23 AM

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

Page 1 of 2 (30 items) 12