Tuesday, March 31, 2020

Msg. 945, Database cannot be opened due to inaccessible files or insufficient memory or disk space.

Was doing an health check the other day and I ran into this while trying to list all database file details from the server:

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

So I tried to access the database to see what the problem was -- no good.  The gui would not let me right click/properties, and then I tried to run this statement it failed w/the same error posted above:

     USE dbname
     EXEC sp_helpfile

So.  How do you get in there and look at your files when the server will not let you see them?  Easy sneasy.

       sd.name DBName,
       mf.type_desc FileType,
       physical_name Location
       sys.master_files mf INNER JOIN sys.databases sd
         ON mf.database_id = sd.database_id

In this particular case, this was the output:

That N drive does not exist on the server.  Hence, the engine cannot find and open the file.  These are a couple other messages you may find in the errorlog in a situation like this:

Message  File activation failure. The physical file name "N:\XXXX\Data_100_1.LDF" may be incorrect.

Message  FileMgr::StartLogFiles: Operating system error 2(failed to retrieve text for this error. Reason: 15105) occurred while creating or opening file 'N:\XXXX\Data_100_1.LDF'. Diagnose and correct the operating system Error, and retry the operation.

Little more detail on sys.master_files:

No comments:

Post a Comment