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
Off-line script and then on-line script worked like a charm.
Cheers Peenal.
Saved my day again.
Thanks so much!!
I had a database on a computer and somebody wiped out my user account.. and I couln’t get access to it… This saved me!!
set databases offline and then online worked.
Excellent ,after taking offline and bringing online it worked awesonly….thanks very much
Hallo
Unchecking the “compress contents to save disk space” on .mdf and .lds files also helps.
Thanks
Life saver :)
Off-line script and then on-line script worked like a charm.
Thanks a lot.
another easy solution could be, when you take the database online, it will tell you which files is it looking for.
i came across the same issue and came to know that the dbname
reportserver2TempDB was actually having files reportservertempdb.mdf and .ldf
but when it tries to load the database it looks the files with the nam reportserver2tempdb.mdf and.ldf
so what you can do is, stop the sql service and rename the files and now restart the service. and your db will be accesible :)
Thanks man!!! it works:)
i got error on step 1, so i skip step 1 and execute step 2 and 3, and it still works.
Thanks alot
I got following error while trying to run
alter database db_name set online
Msg 945, Level 14, State 2, Line 1
Database ‘db_name’ 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.
Msg 1101, Level 17, State 12, Line 1
Could not allocate a new page for database ‘db_name’ because of insufficient disk space in filegroup ‘DEFAULT’. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
USE master;
GO
ALTER DATABASE TEKADIM SET EMERGENCY
GO
ALTER DATABASE TEKADIM SET SINGLE_USER
GO
DBCC CHECKDB (TEKADIM, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
GO
USE TEKADIM;
DBCC CHECKDB WITH NO_INFOMSGS;
GO
USE TEKADIM
ALTER DATABASE [TEKADIM] SET RECOVERY SIMPLE
GO
DBCC SHRINKFILE ([TEKADIM_log])
GO
ALTER DATABASE [TEKADIM] SET RECOVERY FULL
DBCC SHRINKFILE (TEKADIM, 1) WITH NO_INFOMSGS
ALTER DATABASE TEKADIM SET MULTI_USER
Ali,
What exactly does this script do? I am getting this error on some, but not all after failing over a SQL server using VMWare site recovery manager. Am I losing data here with this script? The “Repair_allow_data_loss” option sounds like we are losing data. Is there a way to compare against a good copy of the database to see if there really is any data loss?
Jake
Mind blowing solution….u really save my life…….my job ws on risk…thank u so much for the solution…..thanks a lot
Excellent…really good and thanks for the solution
Thank you very much! It solved the problem.
2010-09-10 12:02:56.10 Server Microsoft SQL Server 2005 – 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
2010-09-10 12:02:56.10 Server (c) 2005 Microsoft Corporation.
2010-09-10 12:02:56.10 Server All rights reserved.
2010-09-10 12:02:56.10 Server Server process ID is 2200.
2010-09-10 12:02:56.10 Server Logging SQL Server messages in file ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG’.
2010-09-10 12:02:56.10 Server This instance of SQL Server last reported using a process ID of 1380 at 10-Sep-10 11:09:08 AM (local) 10-Sep-10 5:39:08 AM (UTC). This is an informational message only; no user action is required.
2010-09-10 12:02:56.10 Server Registry startup parameters:
2010-09-10 12:02:56.10 Server -d C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2010-09-10 12:02:56.10 Server -e C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2010-09-10 12:02:56.10 Server -l C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2010-09-10 12:02:56.12 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2010-09-10 12:02:56.14 Server Detected 2 CPUs. This is an informational message; no user action is required.
2010-09-10 12:02:56.92 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2010-09-10 12:02:56.95 Server Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2010-09-10 12:02:56.98 Server The Microsoft Distributed Transaction Coordinator (MS DTC) service could not be contacted. If you would like distributed transaction functionality, please start this service.
2010-09-10 12:02:56.98 Server Database Mirroring Transport is disabled in the endpoint configuration.
2010-09-10 12:02:56.98 spid4s Starting up database ‘master’.
2010-09-10 12:02:56.98 spid4s Error: 17207, Severity: 16, State: 1.
2010-09-10 12:02:56.98 spid4s FCB::RemoveAlternateStreams: Operating system error (null) occurred while creating or opening file ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf’. Diagnose and correct the operating system error, and retry the operation.
2010-09-10 12:02:57.06 spid4s SQL Trace ID 1 was started by login “sa”.
2010-09-10 12:02:57.09 spid4s Starting up database ‘mssqlsystemresource’.
2010-09-10 12:02:57.15 spid4s Error: 15297, Severity: 16, State: 1.
2010-09-10 12:02:57.15 spid4s The certificate, asymmetric key, or private key data is invalid.
2010-09-10 12:02:57.15 spid4s SQL Trace was stopped due to server shutdown. Trace ID = ‘1’. This is an informational message only; no user action is required.
I get this error when trying to put the db online:
An error occurred while processing the log for database ‘UtlititiesPlus’. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log. what can i do?
Thanks, I got the solution.
THIS DOES WORK AND IS THE ONLY SCRIPT THAT DOES
USE master;
GO
ALTER DATABASE TEKADIM SET EMERGENCY
GO
ALTER DATABASE TEKADIM SET SINGLE_USER
GO
DBCC CHECKDB (TEKADIM, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
GO
USE TEKADIM;
DBCC CHECKDB WITH NO_INFOMSGS;
GO
USE TEKADIM
ALTER DATABASE [TEKADIM] SET RECOVERY SIMPLE
GO
DBCC SHRINKFILE ([TEKADIM_log])
GO
ALTER DATABASE [TEKADIM] SET RECOVERY FULL
DBCC SHRINKFILE (TEKADIM, 1) WITH NO_INFOMSGS
ALTER DATABASE TEKADIM SET MULTI_USER
Thanks a lot its perfectly worked. Solve my problem of 1 week.
Thanks for the help. It saved my time.