Skip to main content

 none
Repair msdb database that is labeled Suspect RRS feed

  • Question

  • Can "Suspect" msdb be successfully repaired if there is no backup of the database?

    Can you please send me the process that I should follow to repair the database?

    Tuesday, October 15, 2019 3:53 PM

Answers

All replies

  • https://www.mssqltips.com/sqlservertip/3191/how-to-recover-a-suspect-msdb-database-in-sql-server/

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by Avyayah Tuesday, October 15, 2019 7:25 PM
    Tuesday, October 15, 2019 4:13 PM
    Answerer
  • The steps to recover the MSDB database from suspect mode -

    1.Stop SQL Server DB engine and Agent Services
    2.Rename or move existing msdb data and log files (MSDBData.mdf and MSDBLog.ldf) in the current location
    3.Copy (MSDBData.mdf and MSDBLog.ldf) files from any other working instance to the locations in Step 2
    4. Start SQL Server DB engine and Agent Services

    This should fix the issue, however you would have lost all of your SQL Agent Jobs, History, BackupHistory, SSIS packages and Maintenance Plans, DB mail configurations and other. Make sure you always have backup of system db's. Hope this helps.
    • Marked as answer by Avyayah Tuesday, October 15, 2019 7:25 PM
    Tuesday, October 15, 2019 5:16 PM
  • I wouldn't do it this way. You might have a different collation for your msdb compared to the other system database. But also you have all the stuff that the other instance had in its msdb, for you to clean up. I wouldn't take the option C in Uri's post either, because of the same reason. I'd do a rebuild of the system databases (on a different machine), carefully specifying the right collation, and then attach those files. Or, do a new install (on a different machine, with proper collation) and attach those files.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Wednesday, October 16, 2019 7:55 AM
    Moderator
  • The steps to recover the MSDB database from suspect mode -

    1.Stop SQL Server DB engine and Agent Services
    2.Rename or move existing msdb data and log files (MSDBData.mdf and MSDBLog.ldf) in the current location
    3.Copy (MSDBData.mdf and MSDBLog.ldf) files from any other working instance to the locations in Step 2
    4. Start SQL Server DB engine and Agent Services

    This should fix the issue, however you would have lost all of your SQL Agent Jobs, History, BackupHistory, SSIS packages and Maintenance Plans, DB mail configurations and other. Make sure you always have backup of system db's. Hope this helps.
    Sorry but this method should not be used, you "may" get lucky but believe me this is not the way. One can try checkdb repair but again that would be successful depending on level of corruption

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Thursday, October 17, 2019 10:29 AM
    Moderator
  • Solution to Recover Suspect MSDB Database

    Recover Suspect MSDB Database by using MS SQL Server Management Studio
    You can follow the below mention steps to recover suspect MSDB database.

    • Open MS SQL Server Management
    • In Object Explorer, Right click on SQL Server 10.5 and then Click on Stop
    • Open Control Panel Window, Click on Administrative Tools and then click on Services
    • Select SQL Server (MS SQL Server) and then Right click on Stop
    • Go to C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLServer\MS SQL\Data
    • Move the MSDB data and MSDB Log file to any other location or system
    • Copy both the files from new location and place it on the older location
    • Now open Object Explorer and then Right Click on Start
    • Now refresh your database and you can detach the MSDB file

    Regards,

    Robin

    Wednesday, October 23, 2019 10:23 AM
  • Solution to Recover Suspect MSDB Database

    Recover Suspect MSDB Database by using MS SQL Server Management Studio
    You can follow the below mention steps to recover suspect MSDB database.

    • Open MS SQL Server Management
    • In Object Explorer, Right click on SQL Server 10.5 and then Click on Stop
    • Open Control Panel Window, Click on Administrative Tools and then click on Services
    • Select SQL Server (MS SQL Server) and then Right click on Stop
    • Go to C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLServer\MS SQL\Data
    • Move the MSDB data and MSDB Log file to any other location or system
    • Copy both the files from new location and place it on the older location
    • Now open Object Explorer and then Right Click on Start
    • Now refresh your database and you can detach the MSDB file

    Regards,

    Robin

    Have you tried this ?

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Wednesday, October 23, 2019 11:18 AM
    Moderator
  • Yes Sir... 
    Thursday, October 24, 2019 5:24 AM
  • Yes Sir... 
    Well your steps are not correct, you are moving old mdf and ldf files for MSDB but where is step to bring new one ( if that is what you are thinking on doing) ?.  I do not see any steps mentioned. 

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Thursday, October 24, 2019 6:00 AM
    Moderator
  • The 7:th step refers to some "new location" from where we are supposed to copy the database files from. But there is no information about this location and how we would get database files for msdb to that location in the first place.

    I don't understand why folks complicates recovery of msdb! Restore from a backup. Just like any database. Having backup of master, msdb and model is standard procedure and restoring from an msdb backup is a no-brainer...


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Thursday, October 24, 2019 6:57 AM
    Moderator