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
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.
thank you this solved my issue on production server .. :)
Hi sir,
I am Dev and I am working on Executive Build _& Release and i have responsibility of all client Database. So pls tell me reasons of database on offline mode and suspect mode
@@ I have major issue in database is mydatabase ldf file size increase automatic from 5 GB to 54 Gb and all database is offline mode and now free space in C:\ drive 17 GB pls suggest How to reduce the size of ldf file and open database in online mode
I am facing this issue at one of my clients system.
When we are connecting to to SQL server 2008 DB engine through SSMS, I am getting the error
Database ‘msdb’ cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. (Microsoft SQL Server,
Error: 945)
Then also connects to Database. But clients databases are not visible. If I tried to expand ‘System Database’ or ‘Security’ or ‘Management’ again I am getting this error.
Hard disk has 80 gb free space, account has permissions, .mdf and .ldf files are not read only
In SQL Server configuration manager -> SQL Server properties -> Log on as -> selected ‘This Account’ and using account name ‘admin’ and its password.
They are able to do data operation through applications from other PCs. Issue is only on accessing from SSMS.
In error log we can see
2016-08-10 18:02:56.17 spid13s Error: 824, Severity: 24, State: 2.
2016-08-10 18:02:56.17 spid13s SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x2411d175; actual: 0x2411dfd1). It occurred during a read of page (2:0) in database ID 4 at offset 0000000000000000 in file ‘C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf’. 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.
2016-08-10 18:02:56.17 spid13s Error: 5105, Severity: 16, State: 1.
2016-08-10 18:02:56.17 spid13s A file activation error occurred. The physical file name ‘C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf’ may be incorrect. Diagnose and correct additional errors, and retry the operation.
2016-08-10 18:02:56.17 spid13s File activation failure. The physical file name “C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf” may be incorrect.
2016-08-10 18:02:56.17 spid13s The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.
2016-08-10 18:02:56.17 spid36s Error: 945, Severity: 14, State: 2.
2016-08-10 18:02:56.17 spid36s Database ‘msdb’ cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
2016-08-10 18:02:56.17 spid36s Problems recording information in the msdb..suspect_pages table were encountered. This error does not interfere with any activity except maintenance of the suspect_pages table. Check the error log for more information.
Please suggest me how we can rectify this
Try this: https://blog.sqlauthority.com/2007/08/02/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/
Thanks Pinal Dave Sir
I think the MSDB log file encountered an I/O error. If Log file corrupted, then what we have to do ? Since the affected file is MSDB , many actions are not possible from SSMS
One person suggests replacement of MSDB from a same version SQL Server ? But I am worried to do so ?
Hi
Since my msdb file was corrupted and there was no backup of system DBs, I have to rebuild MSDB database. Fortunately there was no jobs or alerts etc…. I tried the steps from following link
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/dd207003(v=sql.105)
Only one change at execution plan was at 6th step in Rebuild MSDB from instmsdb.sql. Since an error occurred, I stopped the SQL Server service at Command Prompt and started from Configuration Manager.
Then opened SSMS , Connected to DB engine (I got an error, Clicked ok for that), then, opened the instmsdb.sql from
C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLInstallinstmsdb.sql
and executed.
Thanks
Johnson
I am getting this error on a SQL Express instance, and I cannot even connect SQL Server Manager to try and trouble shoot. Is there a way to fix?
what is the .rsa file generated in the mssql data folder for