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
Pinal, thanks for your posts!
Yes it is the common issue for DBA, here’s my troubleshooting steps:
STEP1: identify the db status:
use master
select databaseproperty(‘db_name’,’isShutdown’)
Most of them it would return 1 in this situation
STEP2: Clear up the internal db status:
use master
alter database db_name set offline
it would return with no error in most cases
STEP3: Get detail error message:
use master
alter database db_name set online
After step3, sql server will first verify the log file, if the log file is okay, it will verify the rest of the data file(s). Most of time it is because of the file location or file properties setting. For example if it is file location issue:
alter database db_name
modify (file=’logical name’, filename=’physical name’)
go
This one really solved my problem. Thank you very much!
Hi Z2,
Thank you very much for your help. This really solved my problem which i was facing since last 12 hrs.
Hiten
Thank you!!
This one was the solution for my problem. Thank you.
excellent Z2… thanks for the answer solved my problem in few seconds
Thanks! it fixed my problem. btw which user should have access to the physical files?
thanks this solved the problem for us.
Thank you so much,this solved my problem
Thanks for that post, when I set it back online there were no errors and the db was accessible again, ran checkdb with no error or issues. in the log it showed rolling transactions forward and one back. Also in the log I saw “informational” message regarding writting checkpoint to that db… pretty sure this problem started due to a diskspace shortage. Either way thanks for the response. And thank you Pinal, your blog posts have been very helpful to me in the past, keep up the good work!
Thanks a lot! Worked just fine!
It’s still a very useful
Thank You so much Z2.. Really worked..
Hey you have great instructions but what if someone goes through the steps that you identified then what? What are the possible Scenarios?
YOU ARE THE MAN!, THANKS :D
Its very useful information,
but why this database not showing offline/recovery status?
Hey this worked, thanks …
Z2, many thanks – it helped me to id and fix the problem!
Thank you, It solves my problem.
You are my hero… offline and then online. It looks so simple but we DBAs can’t think of during disaster… Thanks a lot Sir…
It works thnx
thank you very much…..this post is very use full for me
Thanks solved my problem :)
More than 6 years later, this little tidbit of information has saved my bacon. Thank you very much for taking the time to post this.
thanks @phoenixlives
Thanx man you are a lifesaver for us new DBAs
Perfect set offline them online worked like a charm
many thankz
in step 3 am nt clear..
my code
alter database cvssss modify (file=’logical name’, filename=’physical name’)
i got error
Incorrect syntax near ‘(‘.
Try this syntax, it will work :)
alter database cvssss
modify file(name=’logical name’, filename=’physical name’)
go
Hello!!
I am getting follwoing error after executing Alter database db_name set online:
I/O error (bad page ID) detected during read at offset 0x00000000030000 in file ‘C:\Program Files\Microsoft SQL Server\MSSQL\Data\IT_Data.MDF’.
Connection Broken
what to do?
Urgent ):
Hola, si son usuarios genexus, este error puede ser producido debido a que borraron la KB manualmete, es decir, unicamente borraron el folder donde se aloja la KB
c:/…./models/nombre_KB
y seguramente intentaron generar una nueva kb, genexus da por default al nombre de la base de datos: GX_KB_NOMBRE_DE_LA_KB, asà que si esta base de datos ya existia se producira dicho error,
para corregirlo basta con cambiar el nombre de la base de datos que genexus da por default.
Saludos
nice one… its really saved my time… coz most of my databases are corrupted
If you’re getting an incorrect syntax, make sure when you paste the query into SQL Query Analyzer you remove the apostrophe around db_name or filename, etc. and replace with a new apostrophe.
It may some times you just move your database files from the older location to new location… So just veryfy that..
Thanks Pinal and Z2, I did offline and online, my database is up now.
Dear Friends,
I am getting this error when i run the last step of the given patch, please kindly give me the solution my production server has stoped. and feeling too much pressure.
the error message is:
Msg 233, Level 20, State 0, Line 0
A transport-level error has occurred when sending the request to the server. (provider: Named Pipes Provider, error: 0 – No process is on the other end of the pipe.)
STEP1: identify the db status:
use master
select databaseproperty(’AdminBack’,’isShutdown’)
STEP2: Clear up the internal db status:
use master
alter database AdminBack set offline
STEP3: Get detail error message:
use master
alter database AdminBack set online
these three steps is not working in mirror server ….
am facing an error after configure Database mirroring
but the problem is mirror server
” cannot show requested dialog (sqlmgmt)
an exception occured while executing a transact-sql statement
or batch (microsoft.sqlserver.connectioninfo)
the database “testing” cannot be openned.it is acting as a mirror
database(microsoft sql server error:954) “
Hi,
I am not able to access my msdb.
Each time i try to access the database it gives the following error:
MSDB is not accessible. error 945.
Can anyone please help me..??????
Thanks !!!
That was perfect answer for the trouble…Thanks a lot for the solution
Msg 5041, Level 16, State 2, Line 1
MODIFY FILE failed. File ‘MSDBData.mdf’ does not exist.
I am getting this error in the 3rd step. how to get the msdbdata.mdf file. I can able see the msdb database in Management studio physically. but i cant able to access it. how to troubleshoot it. Pls help.
Hi Pinal,
I don’t know if you can help me here.
I can’t start my sql server services. I have tried to move the system databases to the specific drives from the default sql server path…
I have successfully moved the data and log files for master,msdb,model but i can’t start my services back and getting this error…
2009-08-26 04:45:41.89 spid9s Starting up database ‘model’.
2009-08-26 04:45:41.89 spid9s Error: 17207, Severity: 16, State: 1.
2009-08-26 04:45:41.89 spid9s FCB::Open: Operating system error 5(error not found) occurred while creating or opening file ‘S:\MSSQL\Data’. Diagnose and correct the operating system error, and retry the operation.
2009-08-26 04:45:41.89 spid9s Error: 17204, Severity: 16, State: 1.
2009-08-26 04:45:41.89 spid9s FCB::Open failed: Could not open file S:\MSSQL\Data for file number 1. OS error: 5(error not found).
2009-08-26 04:45:41.89 spid9s Error: 5120, Severity: 16, State: 101.
2009-08-26 04:45:41.89 spid9s Unable to open the physical file “S:\MSSQL\Data”. Operating system error 5: “5(error not found)”.
2009-08-26 04:45:41.90 spid9s Error: 17207, Severity: 16, State: 1.
2009-08-26 04:45:41.90 spid9s FCB::Open: Operating system error 5(error not found) occurred while creating or opening file ‘S:\MSSQL\logs’. Diagnose and correct the operating system error, and retry the operation.
2009-08-26 04:45:41.90 spid9s Error: 17204, Severity: 16, State: 1.
2009-08-26 04:45:41.90 spid9s FCB::Open failed: Could not open file S:\MSSQL\logs for file number 2. OS error: 5(error not found).
2009-08-26 04:45:41.90 spid9s Error: 5120, Severity: 16, State: 101.
2009-08-26 04:45:41.90 spid9s Unable to open the physical file “S:\MSSQL\logs”. Operating system error 5: “5(error not found)”.
2009-08-26 04:45:41.90 spid9s File activation failure. The physical file name “S:\MSSQL\logs” may be incorrect.
2009-08-26 04:45:41.90 spid9s Error: 945, Severity: 14, State: 2.
2009-08-26 04:45:41.90 spid9s Database ‘model’ cannot be opened due to inaccessible
Vielen Dank, die Hinwiese mit online/offline waren meine Rettung!
Gr8 Stuff!!
But ill need to know why it crashed like that…
Tisk! Tisk!
It work fine to me, thanks.