SQL Server Club
Home Articles Essential Guides Blogs Links Twitter Facebook Join Contact About
Msg 945, Level 14, State 2, Line 1 Database cannot be opened due to inaccessible files or insufficient memory or disk space.
Rachel Clements About the Author

Rachel has been working with SQL Server for ten years, building her database skills on top of her roles as both a web and Windows developer.

As well as being Technical Editor here at SQL Server Club, she is a founder and organiser of the SQL Server Club User Group Southwest, based in Bristol, UK. Her favorite activities include stepping through code, reading about the internal workings of SQL Server and drinking coffee.

Linked In Typepad Profile Rachel Clements on Facebook Rachel Clements RSS Feed Rachel Clements on Twitter
Send to a Friend Printer friendly version

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.

Send to a Friend Printer friendly version
Top of Page
© Norb Technologies 2007-2010 Privacy Policy Norb Technologies Devdex Feedback Home
Feedback Form