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.
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/
LikeLike
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.
LikeLike
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.
LikeLike
Thanks jeff, Please explain your concern in details.. its a very good things you highlighted. Please share some more on this.
Thanking You,
Virendra
LikeLike
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.
LikeLike
Yesss!
LikeLike
Thanks Virendra for the information, your suggestions really helping us in real-time. Request to share some more information on high availability concepts mostly on log shipping, Replication, we are following you quit some time.
— Thank you once again.
LikeLike
You can also take the help of this link: http://www.sqlrecoverysoftware.net/blog/sql-error-926-and-945.html
LikeLike