It’s very rare but may be happen with us ,even we are trying to connect SQL Instance using sysadmin login and SSMS through error saying  Database ‘msdb’ cannot be opened. it has been marked suspect by recovery.


The error 926 commonly lead to inconsistencies in SQL database and affect it file format. The main factor is the due to corruption of the SQL MDF database file like hardware malfunctioning or less disk space on file system. The reason can be find to digging SQL Server Error Log.

We can use sp_resetstatus‘msdb’ at very initial stage, if problem not get resolved find more information using
DBCC CHECKDB.

Anyway MSDB has information on backups, jobs, dts/ssis etc, so it has not very frequent changes, so restoring last night backup work.
Other simplest way, if you are ready to bear/handle the loss of backup,jobs,dts/ssis information, Just simply find the MSDB’s MDF and LDF file named as stop the SQL server Instance service, rename both MDF and LDF files. Take a copy from running SQl server’s instance MDF and LDF file and copy it to problematic instance’s location where previously msdb db filename changed. (Note : To copy MDF and LDF files from running instance, first we have to stop that instance). After copying MDF n LDF files, just restart SQL server instance service and problem will got resolved.

 

Advertisements
Comments
  1. Taiob says:

    Are yo sure less disk space on file system will cause this error? Before restoring from a different database I will try this :
    http://www.sqlskills.com/blogs/paul/creating-detaching-re-attaching-and-fixing-a-suspect-database/

    Like

    • As we know know each table data stored in a page whose maximum storage limit is 8 KB, so page is a basic unit of SQL server data.This has been a point of concern that many SQL users tried to use because as they have observed free space but sometimes, disproportionate storage consumed by memory optimized tables causes exceptional error like SQL Error 926 as well as SQL Error 945.

      Your suggestion on this point heartily welcome.

      Like

  2. Jeff Bennett says:

    Typically, whenever I see msdb in a non-recoverable status, it usually follows system security patching. determine which patches, hot-fixes, and updates were applied can help you the next time around.

    Like

  3. Parthik Parikh says:

    One more thing to add: I believe the version of the Instance from which we are trying to copy new MDF and LDF files, should be the same as corrupt msdb instance.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s