Rip's Domain

Rebulding MSDB in SQL Server 2005

Posted in SQL, TechSupport by rip747 on May 26, 2008

Again I spent a gorgeous day in front of the computer trying to solve a major problem.

Seems that because of some diskspace issures we had in April, my MSSQL configure got hosed and the server hasn’t been able to perform any backups nor has the sql server agent been running. This was due to the fact that the MSDB database has been corrupted.

The following are the steps that I took to get everything back up and running. I will warn you that by doing these steps, you will lose all you Maintenance Plans that you had and you will have to recreate them. Figure that this is a small price to pay because let’s face it, you got to have backups.

I would strongly suggest that you don’t be an idiot like me and make sure that you backup your msdb database. However if you are like me and didn’t do this, these steps should get everything back on track.

  1. Put MSSQL into single user mode
    1. Click -> START -> Microsoft SQL Server 2005 -> Configuration Tools – > SQL Server Configuration Manager
    2. Right click on SQL Server and choose Properties
    3. Click on the Advanced tab. Under Startup Parameters you will be adding the following parameters to the beginning of the string: -m;-c;-T3608
  2. Restart SQL Server
  3. Connect to SQL server through the Management Console. From this point on we will be using TSQL to issue the commands so click the New Query button on the top left. At this point you should be in the master database inside the query window.
  4. Detach the MSDB database using the following commands:
    use master
    go
    sp_detach_db ‘msdb’
    go

    and click Execute

  5. We need to move (or rename, I prefer moving them) the existing MDF and LDF files for the MSDB database so that we can recreate it.
    1. Usually these files are located in the following directory:
      C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
      Your’s might differ.
    2. Move (or rename) the MSDBDATA.mdf and MSDBLOG.ldf files.
  6. Back to the Management Studio. Open up the instmsdb.sql file in a new query window. This file is usually located in the following directory:
    C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install
  7. Execute the file. If you see any errors about xp_cmdshell, just ignore them. They are common and the script will take care of it.
  8. At this point you should have your MSDB database restored. The only thing left is cleanup.
  9. Execute the following command to make sure that xp_cmdshell is once again set to disable for security reasons:
    EXEC sp_configure ‘show advanced options’, 1
    GO
    RECONFIGURE WITH OVERRIDE
    GO
    EXEC sp_configure ‘xp_cmdshell’, 0
    GO
    RECONFIGURE WITH OVERRIDE
    GO
  10. Shutdown SQL Server
  11. Go back into your Startup Paremeters for your server in the SQL Server Configuration Manager and removed the -c;-m;-T3608 parameters we added earlier.
  12. Restart SQL Server

Everything should be cool at this point and you’ll be able to recreate any Maintenance Plans and Jobs.

Let me know if you have a better way or a way to restore the configuration information from the old msdb database.

37 comments

37 Responses

Subscribe to comments with RSS.

  1. Tom Gilmour said, on June 7, 2008 at 10:18 am

    Hi:
    Thanks for this Post Rebuilding MSDB.

    Saved me a complete re-install.

    I forced the install of Master and MSDB from another server. I did this so I could restore production databases from the Production server to a backup server with out breaking the Logins to Database user SID’s.

    The problem I had was all the Maintenance Plans on the backup machine still pointed at the Production server, and if I deleted a Maintenance plan it deleted it from Both Servers ??? MSDB must have the severs name hard coded in it. Everything I did was on the Production server.

    What I really wanted to tell you about your Post Rebuilding MSDB is; When I was done I couldn’t start the SQL Agent and Got errors trying to look or create any Maintenance plans.

    Had to re-run SQL Srver 2005 Hotfix 4458, After that I appear to be OK !!!! You might add a note about rerunning the Hotfix to your Post.

    Thanks — Tom Gilmour

  2. Peter McEvoy said, on September 29, 2008 at 4:54 am

    Super article and saved my bacon more than once. However, your editor has pasted “smart-quotes” into the article, so that copy-paste of the commands doesn’t exactly work without editing.

    I also found that once SQL server was in single user mode, that I could not really use Management Studio effectively (it seems to want to open 1 connection for the brower window, and 1 for the query editor window). So I found it easier to use sqlcmd.exe. YMMV

  3. nathan said, on January 16, 2009 at 11:18 am

    I know it’s been awhile since this was posted but I wanted to thank you for the write up. Saved me a lot of trouble and from having to reinstall.

    One thing I wasn’t able to do though was to log into the Management Studio once in single user mode. Like the previous poster stated, that was fixed with sqlcmd though.

    Thanks again.

  4. Borislav Traikov said, on May 11, 2009 at 11:11 am

    Almost a year later this post is still top-notch :D.

    Thanks alot Rip – this really saved the day.

    Before finding this solution I came across the official Microsoft solution for this sort of situations:

    http://msdn.microsoft.com/en-us/library/dd207003.aspx

    Needless to say that that one seemed far more general and far more time-consuming than this.

    All I can add is that the execution time for the solution in this blos is: 5 minutes (litteraly)
    // However it seems to be restricted only to problems with MSDB. Having trouble with the master DB might require a different solution.

    Finally, I didn’t run any hotfixes after this – only did a DBCC CHECKDB to make sure that all of the databases were OK.

    PS: I detached all the non-system databases I could before applying the solution just in case.

    PPS: Thanks again 🙂

  5. rip747 said, on May 12, 2009 at 5:57 pm

    To all,

    You are most welcome. Can’t believe this post has been up for almost a year and people still find it helpful. God I love blogging 🙂

  6. Samuel Martis said, on May 30, 2009 at 9:41 am

    Thanks to the author… this article saved my extra work. I tried many other options and was planning to re-install.. but this article saved me from that.

  7. Mohit said, on June 12, 2009 at 6:41 am

    I am facing a similar kind of problem so any link available for SQL Srver 2005 Hotfix 4458 as i could not find it 😦

  8. Rajesh said, on July 28, 2009 at 9:04 am

    You have done a great job. It saved me a lot of pain and was smooth for me. I had a corrupt master and msdb and wanted to migrate production server at the same time.
    Thanks a lot!!!!
    Rajesh

  9. LukePet said, on July 28, 2009 at 10:33 am

    It works! Thanks.

  10. Lol said, on October 21, 2009 at 1:50 am

    Hi,
    I tried this to get my msdb database back, but though there is no other sessions opened, I cant rename or delete the msdb and so when i try to install it , its saying the file cant be installed because its already there.
    Any idea how can i rectify this.
    I checked all the sessions and no users in that.

    Thanks

  11. rip747 said, on October 21, 2009 at 8:22 am

    did you make sure you followed the instructions in this post and setup sqlserver to run in single user mode?

  12. Lol said, on October 22, 2009 at 9:00 pm

    Hi,
    Yes, I followed all the same step..
    Now, I tried again to restart the server in the trace mode and when i tried the detach command for the msdb database, it is giving me the error-
    Msg 15010, Level 16, State 1, Procedure sp_detach_db, Line 34
    The database ‘msdb’ does not exist. Supply a valid database name. To see available databases, use sys.databases.

    and I tried to rename, delete the msdb data file , but still it is saying the data file is in use.
    No application is connected to it.
    There was a disk space issue which corrupted the databse, is the the reason why it is saying its in use??

    Lol..

  13. rip747 said, on October 23, 2009 at 11:20 am

    hmmmm… sounds like you’ve got something funky going on. try this: stop the sql services altogether. Go and manually rename the msdb.mdf and msdb.ldf file, then bring up sql server in single user mode and see what happens. another thing you can do is install sql server to another instance or on a separate box and then copy those two file from the new instance or machine.

  14. Lol said, on November 1, 2009 at 8:08 pm

    Hi,
    I tried to manually rename the file , but still it is saying it is used by another program and so couldnt rename.
    I copied the databases needed to another server and its working, but I need this installation to be up ad running again…
    so trying to stop each and every services to rename it.but still saying its in use.
    if the file is corrupted whether it will say that its in use??
    its making crazy…

    Lol

  15. Lol said, on November 1, 2009 at 8:09 pm

    Just to add one more thing. I can rename msdb.ldf file but just the .mdf file is saying it is in use.
    tried to copy from another server, but still cant rename it.

    thanks

    Lol

  16. Yong said, on November 4, 2009 at 8:18 pm

    Thanks for the post. I just restored MSDB and it worked.

  17. Maz said, on December 27, 2009 at 4:24 pm

    Thank you!!! Worked!!

  18. pleasehelpthis'tard said, on January 10, 2010 at 1:46 am

    How do you access management studios in single user mode? How do you use ‘sqlcmd.exe’ ?

  19. rip747 said, on January 10, 2010 at 12:24 pm

    @pleasehelpthis’tard

    you’re not putting management studio into single user mode, you’re putting sql server itself.

  20. Quitman Gregory said, on April 13, 2010 at 2:59 pm

    Yep! Still works like a charm. Thanks!

  21. netrid3r said, on June 30, 2010 at 4:20 am

    many thanks!!
    this solve my day with a customer!

  22. Pam Herbst said, on September 9, 2010 at 4:14 pm

    Worked like a charm, Many Thanks! I found out my users were running a rogue SQL Server 2005 instance on a BizTalk build server. The server had a hard drive crash which took out MSDB. While they may or may not appreciate that I was able to “fix it”, I was happy to learn something!

  23. ganeshan said, on November 26, 2010 at 5:50 am

    HI;

    I want Rebulding MODEL DATABASE in SQL Server 2005,,

    Thanks for the Post Rebuilding MSDB.

    Thanks
    Ganeshan.k

  24. Andytt1 said, on December 7, 2010 at 11:29 pm

    Thank you!!!
    you solve me a big problem with a subject I have in university!!! It was taking me nuts!!!

    thnxs!!!!

  25. James said, on May 17, 2011 at 12:06 pm

    hello, i have problem when i execute instmsdb.sql file in a new query window, when instmsdb.sql open on sql management studio, the login page is appear, when i choose login with sql server authorication, error messages is appear, and these message : Login failed for user ‘Lenovo-NB\Studenti’. Reason: Server is in single user mode. Only one administrator can connect at this time. (.Net SqlClient Data Provider)..
    hello all, whats wrong about me ? i was follow all step…
    thanks..
    am waiting for reply…
    regards.

  26. John said, on June 7, 2011 at 3:09 pm

    James, in order to login with SQL Management Studio, you have to stop all the other SQL services using the Windows Service Manager, because one of them is logging into the database before you do, and only one can connect in Single User Mode. After stopping all the other services, you should be able to login.

  27. Yusuf said, on June 9, 2011 at 8:22 am

    Thanks, it’s work fine and solve my problem.

  28. AWM said, on November 14, 2011 at 12:07 am

    You need to login as sa in management studio before you change to single user mode

  29. Nordin said, on November 25, 2011 at 3:21 am

    It’s already 3 years after this post….
    Today I’m glad to announce at last I’m able to recover my msdb which in RECOVERY MODE since 3 days ago. But it’s not that straight forward to detach and rebuild the msdb. I have several times, after each step/command listed here, to restart the service. I used SQLCMD to execute the command since it’s faster and more straight forward. Each time hit error “restricted mode”,had to restart the services. And at last, SUCCESS.
    Thanks rip747 for this great post.

  30. Rocket said, on February 16, 2012 at 1:06 pm

    Yep, this still works, bailed me out big time today, everything was nice and smooth. The only issue I ran in to was the “only one admin can access at a time”, stopped SQL Services and worked without issue but I could only keep one quarry window open at a time.

  31. Happy Man said, on May 10, 2012 at 2:03 pm

    I guess as long as SQL 2005 is around, we’ll be using this post. It saved my tukas today! As long as you disconnect from the object browser, SSMS works fine in single user mode. Thanks!!

  32. Lubo said, on June 7, 2012 at 8:28 pm

    Excellent, thank you. Saved me a lot of time when my msdb got damaged on my laptop.Thanks to this post I was able to resume development work on a critical task quickly.

  33. Matt said, on August 15, 2012 at 11:20 am

    Great post! Love it!

  34. Sadashiv said, on October 27, 2012 at 9:01 am

    nice post with screenshots!! very useful !!

    I observed below error when I put startup parameters as shown in screenshot:
    Location: dbmgr.cpp:6728
    Expression: !FindDB(dbid)
    SPID: 58
    Process ID: 4720
    Msg 3624, Level 20, State 1, Line 1
    A system assertion check has failed. Check the SQL Server error log for details

    logically..it does matter where you put your trace flag (-T3608).

    Ref: http://www.sqlservercentral.com/Forums/Topic255477-146-1.aspx#bm291392

    But It went fine after putting -T option at last in startup parameters as :
    -dC:\Program Files (x86)\Microsoft SQL Server\MSSQL.3\MSSQL\DATA\master.mdf;-eC:\Program Files (x86)\Microsoft SQL Server\MSSQL.3\MSSQL\LOG\ERRORLOG;-lC:\Program Files (x86)\Microsoft SQL Server\MSSQL.3\MSSQL\DATA\mastlog.ldf;-c;-T3608

    Thanks RIP

  35. Todd said, on December 19, 2012 at 12:06 am

    Thank you!

    For those having trouble with SQL Mgmt Studio, you may have tried to double-click instmsdb.sql. Instead open it in Notepad and paste contents into the New Query window you opened when you ran sp_detach_db…

  36. lowtech said, on January 14, 2013 at 4:16 am

    THANK YOU!!!!!!! – saved my butt – not a SQL DBA but play one one TV for Work. either way this really save me getting our application up and running on this new server we shipped to a customer. not sure how the msdb got corrupted, but these steps did the trick and we are moving forward.

  37. enes ataberk said, on April 8, 2013 at 11:40 am

    thank you so much it worked perfectly


Leave a comment