Microsoft has added a new feature in MS SQL server 2005 that is known as Microsoft SQL server management studio. It provides an integrated environment for the managing, accessing, developing, configuring all component of MS SQL server database. It works with all the SQL server component such reporting services, notification services, integration services. If MS SQL detects any corruption in SQL server management studio then put database in inaccessible mode and database administrator might got some error message like:
“Database ‘msdb’ cannot be opened. It has been marked suspect.”
Cause: When SQL server tries to start and found any of the data file for the database is unavailable then mark database in suspect mode. You can check SQL server error log to verify the cause for the database in suspect mode.
Resolution Options: You have three options for solving the above problem. First option is to restore suspect database second option is to repair database file with DBCC CHECKDB command and last one is to recreate suspect database.
1) Restore from Backup: It is the best option to restore your database which has gone in suspect mode. If you don’t have valid backup then go with second option.
2) Run DBCC CHECKDB: Run this command with repair_allow_data_loss. After running this command you may loss some data.
ALTER DATABASE DatabaseName SET EMERGENCY
GO
dbcc checkdb ('DatabaseName', REPAIR_ALLOW_DATA_LOSS)
If both the above option fails to restore your database, Recreate msdb is the best option for you:
3) Recreate msdb: if you don’t have database backup and DBCC CHECKDB is not working then recreating msdb is the best option for you. Create msdb by the given below methods:
1.Shutdown the server and go to bin folder of sql server in the program files and start sqlserver.exe -c – T3608.
2.Move the corrupt msdb files (master & log) into MSSQL directory.
3.Run instmsdb.sql command in the MSSQL directory.
4.Restart the sql server without T 3608.
Last Minute: Database recreation is not feasible all time. It takes time and money. If you want any other cheap solution for fixing suspect database then you should try any third party repair software for SQL. Third party SQL recovery software works even DBCC CHECKDB command fails and support MS SQL server 2008 R2 and prior versions.
“Database ‘msdb’ cannot be opened. It has been marked suspect.”
Cause: When SQL server tries to start and found any of the data file for the database is unavailable then mark database in suspect mode. You can check SQL server error log to verify the cause for the database in suspect mode.
Resolution Options: You have three options for solving the above problem. First option is to restore suspect database second option is to repair database file with DBCC CHECKDB command and last one is to recreate suspect database.
1) Restore from Backup: It is the best option to restore your database which has gone in suspect mode. If you don’t have valid backup then go with second option.
2) Run DBCC CHECKDB: Run this command with repair_allow_data_loss. After running this command you may loss some data.
ALTER DATABASE DatabaseName SET EMERGENCY
GO
dbcc checkdb ('DatabaseName', REPAIR_ALLOW_DATA_LOSS)
If both the above option fails to restore your database, Recreate msdb is the best option for you:
3) Recreate msdb: if you don’t have database backup and DBCC CHECKDB is not working then recreating msdb is the best option for you. Create msdb by the given below methods:
1.Shutdown the server and go to bin folder of sql server in the program files and start sqlserver.exe -c – T3608.
2.Move the corrupt msdb files (master & log) into MSSQL directory.
3.Run instmsdb.sql command in the MSSQL directory.
4.Restart the sql server without T 3608.
Last Minute: Database recreation is not feasible all time. It takes time and money. If you want any other cheap solution for fixing suspect database then you should try any third party repair software for SQL. Third party SQL recovery software works even DBCC CHECKDB command fails and support MS SQL server 2008 R2 and prior versions.
This comment has been removed by a blog administrator.
ReplyDelete