SQL Server Performance

how to recover msdb (mark : suspect)

Discussion in 'SQL Server 2005 General Developer Questions' started by isa, May 19, 2008.

  1. isa New Member

    Hello everyone, i m using SQL Server 2005, my system databse "msdb" mark suspected coz of light fluctuations, i have no back up to recover this, i tried many other solution for example:


    1) These steps will recover MSDB DATABASE to the state after Installation of the SQL Server, in this loose your changes in this DB.

    1.Stop SQL Server service
    2.From the instalation CD SQL Server copy msdbdata.mdf and msdblog.ldfto your instalation folder
    3.Run SQL Server service

    =====================================================================
    2)

    EXEC sp_resetstatus 'DBName';
    GO

    DBCC CHECKDB;
    GO
    ============================================
    3)
    Alternate course of action:

    ALTER DATABASE DBNameSET EMERGENCY
    ALTER DATABASE DBNameSET SINGLE_USER
    DBCC CHECKDB (DBName, REPAIR_ALLOW_DATA_LOSS)
    GO


    but nothing happend , it stills maked as suspected, kindly tell me how i recover my MSDB database.

    Plz reply me asap.

    Thanx in Advance.

  2. ghemant Moderator

    Hi,
    stop the SQL SErver Services -> copy files (.mdf & .ldf) -> attached it as 'someotherdb/testdb' -> run dbcc checkdb
    what this returns!!
    It is always advisable to take backup of MSDB especially when you have scheduled job(s).
  3. davidfarr Member

    Please explain a bit further what you mean by "light fluctuations" ? What is the output result of DBCC CHECKDB on the MSDB database ?
    You have said that you followed these steps:
    1.Stop SQL Server service
    2.From the instalation CD SQL Server copy msdbdata.mdf and msdblog.ldf to your instalation folder
    3.Run SQL Server service
    Remember that files copied directly from CD usually have a Read-Only attribute. You will need to remove the Read-Only attribute from the data and log file properties before SQL Server can use them. After your most recent service startup, what messages regarding the MSDB database are displayed in the SQL Server Logs? Do you see any message regarding missing or unreadable files ?
    Remember that a database can also be marked as suspect if the physical data or log files are missing from their disk path location, or if they are incorrectly named, or if the header sections of the files are too badly corrupted to be recognised as SQL server files. The data file is typically installed in the path "C:program FilesMicrosoft SQL ServerMSSQLDATAMSDBData.mdf" but might be different depending on your installation. Can you confirm or verify that the physical MSDB data and log files are still in their installation locations and that the disk and path is accessible by the operating system ?
    If the physical MSDB files are missing or badly corrupted, then most ALTER DATABASE and DBCC commands (including DBCC CHECKDB) will not work because no actual database file is attached to the server instance.
    If your MSDB database files are unrecoverable then copying files from the installation CD is your only option; but you will lose all your SQL Agent jobs, schedules and saved data import/export packages. Be sure that you copy the files to the correct location, with the same file names and same path location at the time of installation, and make sure the physical data files are not Read-Only.

Share This Page