Posts Tagged ‘SSMS Error’

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

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.


In case of following

  • Adding a new column to the middle of the table
  • Dropping a column
  • Changing column nullability
  • Changing the order of the columns
  • Changing the data type of a column

SSMS’s table designer may throw following error,

Saving changes is not permitted. The changes that you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created.

This message problem occurs when the Prevent saving changes that require the table re-creation option is enabled, to resolve this, follow the steps as

Click the Tools menu, click Options, expand Designers, and then click Table and Database Designers. Select or clear the Prevent saving changes that require the table to be re-created check box.