Wednesday, March 6, 2013

Database cannot be opened due to inaccessible files or insufficient memory or disk space

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


One of my friends called me that their application is giving error message of disk space , and i asked him to increase the space of drive. later he responded me that he shrinked the files and space has been raised to 70GB, but the error is still there.
I asked him to access the Database through management studio
When he tried to access the database, through a query or by trying to look at the database properties in Management Studio, he got the following message:

Database 'db' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. 


Here is Solution/Fix/workaround of this problem.

1. check the DB status, most of the time , it will return 1

use master
select databaseproperty('dbname','isShutdown')

2. Change the database to offline to clear the db status

use master
alter database
dbname set offline

3. Now change the database to online, at this step log file and data files will be verified by sql server


use master
alter database dbname set online


This solution solved the problem.

22 comments:

  1. thank u very much...

    ReplyDelete
  2. yes this solution save my time. thank a lot....

    ReplyDelete
  3. it worked! thanks a lot

    ReplyDelete
  4. Thank you very much! ;-)

    ReplyDelete
  5. worked! thank you !

    ReplyDelete
  6. Msg 5120, Level 16, State 101, Line 2
    Unable to open the physical file "D:\All ! Pro Shushil 2014\TransactionOnlineSafeWay\AppData\DB_Transaction.mdf". Operating system error 5: "5(Access is denied.)".
    Msg 5120, Level 16, State 101, Line 2
    Unable to open the physical file "D:\All ! Pro Shushil 2014\TransactionOnlineSafeWay\AppData\DB_Transaction_log.ldf". Operating system error 5: "5(Access is denied.)".
    Msg 945, Level 14, State 2, Line 2
    Database 'DB_Transaction' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
    Msg 5069, Level 16, State 1, Line 2
    ALTER DATABASE statement failed.


    .
    .
    .
    .give me solution

    ReplyDelete
  7. This solution is great!
    Thank you.

    ReplyDelete
  8. Thanks bro...
    But if possible cn u explain why the error occured!!

    ReplyDelete
  9. This is a severe error condition that threatens database integrity and must be corrected immediately

    ReplyDelete
  10. While select the tables value i am getting this error
    Msg 824, Level 24, State 2, Line 1
    SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:41978; actual 0:0). It occurred during a read of page (1:41978) in database ID 16 at offset 0x000000147f4000 in file 'D:\Program Files\RayMedi RPOS 7\database\RPOS7.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

    ReplyDelete
  11. Here is one more solution to fix SQL error 945 & get back database which is inaccessible due to full disk space. http://www.sqlrecoverysoftware.net/blog/sql-error-926-and-945.html

    ReplyDelete
  12. Problem is not solved. Any other solution?

    ReplyDelete
  13. I am using SQL Server 2005, I have processed these 3 steps but my problem is not solve, please provide any other solution for this. Thanks in advance.

    ReplyDelete
  14. Thanks this worked.

    ReplyDelete
  15. Thank you very much.

    ReplyDelete
  16. Thanks for solution ,

    But I got the below error message when I executed the third step .
    Plz support me , it's urgent

    Msg 945, Level 14, State 2, Line 2
    Database 'Tanzania2014' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
    Msg 5069, Level 16, State 1, Line 2
    ALTER DATABASE statement failed.
    Msg 945, Level 14, State 2, Line 2
    Database 'Tanzania2014' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
    Msg 5069, Level 16, State 1, Line 2
    ALTER DATABASE statement failed.
    Msg 823, Level 24, State 6, Line 2
    The operating system returned error 38(Reached the end of the file.) to SQL Server during a read at offset 0000000000000000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Tanzania2014.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

    ReplyDelete
  17. it worked fine, thanks

    ReplyDelete
  18. I am still getting below error :
    File activation failure. The physical file name "D:\Databackup\Ashish\test.ldf" may be incorrect.
    Msg 945, Level 14, State 2, Line 1
    Database 'Test' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
    Msg 5069, Level 16, State 1, Line 1
    ALTER DATABASE statement failed.

    ReplyDelete
  19. Very helpful indeed! thank you!

    ReplyDelete

Share This