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.

39 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
  20. Msg 5173, Level 16, State 1, Line 2
    One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.
    Log file 'c:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\NewDB_log.ldf' does not match the primary file. It may be from a different database or the log may have been rebuilt previously.
    Msg 945, Level 14, State 2, Line 2
    Database 'NewDB' 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.

    please give solution

    ReplyDelete
  21. Thanks it works! :D

    ReplyDelete
  22. I too have come across SQL Server error 945. Really it's frustrating issue but i was easily able to resolve the issue going this way:- http://en.mssqldatabaserepair.org/

    ReplyDelete
  23. SQL Server error 945. The error appears when you try to mount the database and you are unable to mount it. Really, it desperate situation but need not to panic as it's a resolvable issue:- http://en.mssqldatabaserepair.org/

    ReplyDelete
  24. This solve my two days problem.
    Thanks

    ReplyDelete
  25. Hi guys u can visit it. http://contentssharing.blogspot.com/

    ReplyDelete
  26. thank you sooo muchhhh - saved my day!!!

    ReplyDelete
  27. Thank you. It works!

    ReplyDelete
  28. Nice Article !
    This is my pleasure to read your article and it is very helpful for SQL Server Community.

    I have also prepared a similar article about, How to fix the error 945 in SQL Server.
    You can also visit my article, your comments and reviews are most welcome.

    http://www.dbrnd.com/2017/03/sql-server-fix-error-945-database-cannot-be-opened-due-to-inaccessible-files-or-insufficient-memory-or-insufficient-space-on-disk/

    ReplyDelete
  29. Superb..

    Its working fine for me

    ReplyDelete
  30. Hi All,
    We had a planned server reboot and one of the database has gone to "Recovery pending" when the server came online. When tried to access the database, it throwing the error of Database cannot be opened due to inaccessible files or insufficient memory or disk space.
    I checked the files, the log file is showing around 100GB as usual and .mdF file is showing 0KB. Please advise

    ReplyDelete
  31. dear sir this solution not working msdb database plz any other option

    ReplyDelete
  32. Thanks alot, 2013 post saved me in 2019.

    ReplyDelete

Share This