SQL SERVER – FIX : Error 945 Database cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server error log for details
This error is very common and many times, I have seen affect of this error as Suspected Database, Database Operation Ceased, Database Stopped transactions. Solution to this error is simple but very important.
Fix/Solution/WorkAround:
1) If possible add more hard drive space either by removing of unnecessary files from hard drive or add new hard drive with larger size.
2) Check if the database is set to Autogrow on.
3) Check if the account which is trying to access the database has enough permission to perform operation.
4) Make sure that .mdf and .ldf file are not marked as read only on operating system file system level.
Reference : Pinal Dave (https://blog.sqlauthority.com)
122 Comments. Leave new
This post saved my bacon, thanks gents!!
Thank you for the post, this saved the bacon!!
This my first stop when looking for answers. Great site and great commenters.
thanks a lot
TY TY, worked like a charm
Database cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server error log for details
Please help me as i cant log in
I have a DB with two data files in the filegroup — one large (2T) and one small (3G) added in case of spillover. SQL had not yet started to use the spillover file, but I lost the disk that housed it. The large file is still available, but I can’t get the DB online because one of the files in the filegroup is gone.
Any thoughts on how I can get this DB online again?
Failing that, any thoughts on how I could recover the data from the large file?
In Data directory, I am not able to see .mdf & .ldf file of my Database but when I am opening sqlmanagement studio here it showing my database name,
when I am running the below query
use master
alter database Biharedistrict set online
It is showing: Msg 5120, Level 16, State 101, Line 2
Unable to open the physical file “c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\BIHAREDISTRICT.mdf”. Operating system error 2: “2(The system cannot find the file specified.)”.
File activation failure. The physical file name “c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\BIHAREDISTRICT_1.ldf” may be incorrect.
Msg 945, Level 14, State 2, Line 2
Database ‘BIHAREDISTRICT’ 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 help me, how the files are missing but DB name is showing.
Hello Guru Sharan,
Were you able to fix your problem? I have the same issue. Thank you
It Really helped me. I was using SAN box behind all my databases and one of the database was showing this error. I followed the steps and get the db online. Thank you very much for posting this.
Hi Z2, Thanks a lot…Love you and your solution.. Really Thanks.
hello sir , i do not know whether my problem is related to this topic or not.
i am using sql server management studio in my project ..i can easily access some file numbers like ab-123/01 or ab/kte/1234/01 but others not like ab-7266/01 or ab-10022/01..why is it so?former numbers are new numbers while later ones are old file numbers in database..one more thing recently i have formatted my PC..also i am already accessing another database remotely which works fine..but the local one showing the aforementioned issue..
Thanks Z2 and Pinal.
And of course make sure you haven’t relocated the source file after attaching it, which was my problem.
nice!!!!!!!!!!!!!!!!!!!!!!!!
it’s nice , thank you very much
Just awesome… it was 1, made it offline, then online. Solved issue in seconds.
Hi, in this case i dropped my distribution database location , i m getting this error frequently,
can u please provide the solution to drop the entire replication setup in my sql server instance .
Thanks in Advance.
distribution is a system database which can’t be dropped by drop database command. you need to use SSMS or T-SQL to clean replication.
hi tried all the ways which you mentioned still i have the same problem
Thanks
anita – thanks for the comment.
Msg 3634, Level 16, State 1, Line 1
The operating system returned the error ‘2(The system cannot find the file specified.)’ while attempting ‘FsFileHeader::Open’ on ‘H:DatabasesGroupFiles’.
Msg 5105, Level 16, State 14, Line 1
A file activation error occurred. The physical file name ‘H:DatabasesGroupFiles’ may be incorrect. Diagnose and correct additional errors, and retry the operation.
Msg 1813, Level 16, State 2, Line 1
Could not open new database ‘MyDatabase’. CREATE DATABASE is aborted.