Tuesday, December 18, 2012

How to Fix SQL Database Error: 'msdb' cannot be opened


Have you ever suffer with this error, when you are trying to open your SQL database:

"'msdb' cannot be opened due to inaccessible files or insufficient memory or disk space"

Most of the time, this error leads to database as suspected or recovery pending state

There are numerous causes behind this error message such as: 

1.This error occurs due to insufficient memory or disk space
2.When user account don't have sufficient permission to perform any operation on database or to view database files. 
3.Due to .mdf and .ldf file are not marked as read only 

Solutions:

1) If the error occurs due to insufficient memory or disk space then try to add more space by removing the unnecessary files from hard drive or move the database to another drive. 

2) You should check that the folder(s) where the MDF and LDF were created has read/write permissions on them or not. Set the enough permission to user account to perform the operation on the database.

3) Check operating system file system level, Are.mdf and .ldf file marked as read only or not?
In some cases, this error occurs when the database is set to Autogrow on. So set it to off to resolve this error.

Another Recommended Solution:

STEP1: First identify the database status by following command:

use master
select databaseproperty(‘db_name’,'isShutdown’)

Most of them it would return 1 in this situation

STEP2: After that clear up the internal database status:

use master
alter database db_name set offline

it would return with no error in most cases

STEP3: Get detail error message by following command:

use master
alter database db_name set online

After this step, sql server will first verify the log file, if the log file is okay, it will verify the rest of the data file(s). Most of time it is because of the file location or file properties setting. For example if it is file location issue: 

alter database db_name
modify (file=’logical name’, filename=’physical name’)
go

Important Tip: After resolving error & get back your database in working state, you should create full backup of your database and run DBCC CHECKDB against the database to make sure that now there are no more problem exist.

No comments:

Post a Comment