Msg 945, Level 14, State 2, Line 1 Database cannot be opened due to inaccessible files or insufficient memory or disk space.

If SQL Server has a problem when it tries to open a user database or its transaction log, it marks the database state as RECOVERY_PENDING.

When you try to access the database, through a query or perhaps by trying to look at the database properties in Management Studio, you’ll see the following message:

Msg 945, Level 14, State 2, Line 1
Database ‘Northwind’ cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

In Management Studio run the following query to check the state of your databases:

SELECT name, state_desc FROM sys.databases

Or if you want to see the state of just the database that you’re getting the error for, as in this example I've got a problem with Northwind:

SELECT name, state_desc FROM sys.databases WHERE name = ‘Northwind’

You’ll most likely see in SQL Server Management Studio’s object browser that all your other databases are ONLINE, but the one with the error will show RECOVERY_PENDING. This will be reflected by the results of running the first query.

Take a look at your SQL Server errorlog to see further information for the database you are getting the error for. This may help you to determine the next action you need to take to fix the error.

You can view the error log in Management Studio: open the server which the problem database is located on and navigate to the Management folder; you'll find the SQL Server Logs folder under here. Expand this to see the recent logs:

SQL Server Error Logs

Here's the entry for the problem with Northwind from my errorlog (yours may well be different):

Date 29/03/2010 11:35:03
Log SQL Server (Current - 29/03/2010 17:30:00)
Source spid13s

Message
FCB::Open failed: Could not open file C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\northwnd.mdf for file number 1. OS error: 5(Access is denied.).

If either of the MDF or LDF are READ-ONLY or you don’t have permission to view the files then this will generate the error message (which is the problem with my Northwind database). Check if the folder(s) where the MDF and LDF were created has read/write permissions on them and their contents for the NT account under which the SQL Server service is running.

To fix the problem of insufficient disk space, which is another likely cause, you’ll need to increase the amount of space available to the database. To do this, free up space on the disk, move the database to another drive, or replace the disk with a higher capacity one.

When you’ve fixed the problem, you’ll need to get your database back ONLINE. The RECOVERY_PENDING state is a persisted state, which means it will remain in place until you change it.

This can be done by issuing the following statement:

ALTER DATABASE Northwind SET ONLINE

If no errors are reported, then your database is production ready again. However it may be wise at this point to make a full backup and run DBCC CHECKDB against the database to ensure that there are no problems with it.

End of Technical Article

SQL Server Club is a free community service from Norb Technologies - Making SQL Server Faster - www.norbtechnologies.com