Does the Rebuild Master utility have a repair possibility?

Posted on 2006-05-27
Last Modified: 2010-07-27
Hello out there.
I just found out that MS SQLServer ver 8 has not done any backups since May 9'th. The msdb is marked SUSPECT. The Windows Server 2003 application log from May 10 talk about
Error: 823, Severity: 24, State: 2
I/O error (torn page) detected during read at offset 0x00000000bb2000 in file 'd:\apps\SQL Server\MSSQL\data\msdbdata.mdf'.
The Windows System log got corrupted at the same time, us having plenty power problems so I believe the SUSPECT.
I have no backup of the msdb - only production databases, and have not any install CD handy (Has to come down from Europe with DHL, I am in West Africa).
Looking up the Rebuild Master utility is says it can repair a corrupted Master DB. Can it also build a new MSDB. My Master is fine and so are the other DB's, so I would very much like to avoid to run scripts and reload data if possible.
I will be back online Monday.
Kind regards Peter Vibede
Question by:PVibede
    LVL 28

    Expert Comment

    LVL 28

    Accepted Solution

    try running this from query analyzer:
    sp_resetstatus yourdatabasenamehere


    LVL 13

    Expert Comment

    From that link that Imran provided you:

    "Caution  Use sp_resetstatus only when directed by your primary support provider or as recommended in Troubleshooting. Otherwise, you might damage your database."

    Obviously the database is currently having problems, but keep that in mind here, resetting a status is something support will normally direct you to and I believe a waiver is required..

    MSDB is not crucial to have up to date in that your application will still run. I would either restore to an older backup and then recreate any jobs/maintenance plans that were added after that backup, or look at this posting to learn about a SQL script that will rebuild MSDB:
    LVL 13

    Assisted Solution

    From the above link:

    1.. In SQL Server Enterprise Manager, right-click the server name and
    click Properties.
    2.. On the General tab, click Startup Parameters.
    3.. Add a new parameter as "-T3608" (without the quotation marks).
    After you add trace flag 3608, follow these steps:
    1.. Stop, and then restart SQL Server.
    2.. Make sure that the SQL Server Agent service is not currently running.
    3.. Detach the msdb database as follows:
    use master
    sp_detach_db 'msdb'
    4.. Delete or rename the msdb mdf and ldf files.
    5.. Run the INSTMSDB.SQL script
    6.. Remove the -T3608 trace flag from the startup parameters box in
    Enterprise Manager
    7.. Stop, and then restart SQL Server.

    LVL 13

    Expert Comment

    Keep in mind, when rebuilding MSBD, any jobs, maintenance plans and backup history will be lost. This is not the end of the world if you keep a Run Book or know which jobs existed on your server previously. Also if your jobs don't change much, restoring from the backup isn't the end of the world.. Backup History is not necessary and most of your jobs will be the same.

    Your DTS packages that are stored in SQL are also in MSDB..

    Author Comment

    Mike Walsh & imran_fast
    Thank You for your help. I will now study and have a go at reset status first to see if SQLServer can get out of trouble by itself. If not go through the install msdb excersize (will probably do this anyway as I do not trust files that has shown i/o errors too much).
    The Maintenance plans can easily be reapplied. This is a simple installation.
    Should be ready to have a go this afternoon late, after our people has gone home.

    LVL 13

    Expert Comment

    PVibede - Just keep in mind that the reset status is not fully supported as it should only be run at the behest of MS Support and when doing so you sign a waiver with them.. This is usually a last resort sort of step, and if you have a recent backup that has your jobs and maint. plans in it, you are better off just restoring that. Good luck all the same.

    Author Comment

    Mike & Imran
    Thanks for your speedy help.
    I chose the detach, rename and install msdb route. no problems.
    Had to wait for some quiet time without SQL users.
    Have now included master & msdb in maintenance plans.
    Kind regards Peter

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Suggested Solutions

    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now