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.
Posted by at

39 comments:

  1. AnonymousMay 7, 2013 at 7:54 PM

    thank u very much...

    ReplyDelete
  2. AnonymousMay 20, 2013 at 4:30 PM

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

    ReplyDelete
  3. AnonymousOctober 21, 2013 at 11:02 AM

    it worked! thanks a lot

    ReplyDelete
  4. AnonymousJanuary 2, 2014 at 3:16 PM

    Thank you very much! ;-)

    ReplyDelete
  5. AnonymousJanuary 16, 2014 at 6:15 PM

    worked! thank you !

    ReplyDelete
  6. UnknownJanuary 28, 2014 at 1:41 PM

    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. AnonymousFebruary 25, 2014 at 5:55 PM

    This solution is great!
    Thank you.

    ReplyDelete
  8. SakthivelMarch 4, 2014 at 10:14 AM

    thanks, it works for me...

    ReplyDelete
  9. AnonymousMarch 6, 2014 at 4:23 PM

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

    ReplyDelete
  10. UnknownMay 11, 2014 at 4:00 PM

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

    ReplyDelete
  11. UnknownMay 11, 2014 at 4:01 PM

    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
  12. UnknownJune 28, 2014 at 11:57 AM

    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
  13. AnonymousSeptember 10, 2014 at 10:19 AM

    Problem is not solved. Any other solution?

    ReplyDelete
  14. AnonymousSeptember 10, 2014 at 10:22 AM

    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
  15. AnonymousSeptember 18, 2014 at 1:49 PM

    Thanks this worked.

    ReplyDelete
  16. AnonymousSeptember 30, 2014 at 10:55 AM

    Thank you very much.

    ReplyDelete
  17. AnonymousOctober 6, 2014 at 7:20 PM

    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
  18. AnonymousOctober 10, 2014 at 2:38 PM

    it worked fine, thanks

    ReplyDelete
  19. AnonymousOctober 27, 2014 at 5:56 PM

    thanks for this

    ReplyDelete
  20. AnonymousNovember 10, 2014 at 1:15 PM

    Thanks!

    ReplyDelete
  21. UnknownNovember 13, 2014 at 12:31 AM

    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
  22. JohnDecember 11, 2014 at 5:47 PM

    Very helpful indeed! thank you!

    ReplyDelete
  23. AnonymousFebruary 12, 2015 at 11:12 AM

    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
  24. carenconradoApril 22, 2015 at 9:48 AM

    Thanks it works! :D

    ReplyDelete
  25. Siva RamasamySeptember 11, 2015 at 2:52 AM

    very helpful. Thanks...!

    ReplyDelete
  26. UnknownFebruary 9, 2016 at 9:59 AM

    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
  27. AnonymousFebruary 29, 2016 at 10:02 AM

    I love you

    ReplyDelete
  28. UnknownMarch 7, 2016 at 5:24 PM

    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
  29. IQMarch 31, 2016 at 2:52 PM

    This solve my two days problem.
    Thanks

    ReplyDelete
  30. The Hacks of The DayMay 16, 2016 at 3:56 PM

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

    ReplyDelete
  31. KatyNovember 2, 2016 at 9:33 AM

    thank you sooo muchhhh - saved my day!!!

    ReplyDelete
  32. AnonymousJanuary 15, 2017 at 12:50 PM

    Thank you. It works!

    ReplyDelete
  33. UnknownMarch 9, 2017 at 1:31 AM

    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
  34. UnknownApril 6, 2017 at 2:06 PM

    Superb..

    Its working fine for me

    ReplyDelete
  35. SunithaMay 22, 2017 at 2:58 PM

    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
  36. Bakes MarineJune 9, 2017 at 10:12 PM

    thank you it works

    ReplyDelete
  37. UnknownNovember 20, 2017 at 11:02 AM

    thank you it worked for me

    ReplyDelete
  38. First look MediaNovember 20, 2019 at 11:17 AM

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

    ReplyDelete
  39. UnknownDecember 27, 2019 at 5:43 PM

    Thanks alot, 2013 post saved me in 2019.

    ReplyDelete

Subscribe to: Post Comments (Atom)

Share This