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

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 (http://blog.SQLAuthority.com)

About these ads

103 thoughts on “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

  1. 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

    Like

  2. in step 3 am nt clear..
    my code

    alter database cvssss modify (file=’logical name’, filename=’physical name’)

    i got error

    Incorrect syntax near ‘(‘.

    Like

  3. 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 ):

    Like

  4. 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

    Like

  5. 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.

    Like

  6. Hello,
    After step 3 you have mentioned this query

    alter database db_name
    modify (file=’logical name’, filename=’physical name’)
    go

    whether if we get any error message then only we have to execute this query or is it compulsary to execute this query,

    and that logical name means whether we can mention any name or the name which the error files is having.

    because I am in confusion of this logical name and physical name.

    Thanks
    sharanu.

    Like

  7. 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.)

    Like

  8. 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) “

    Like

  9. 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 !!!

    Like

  10. 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.

    Like

  11. 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

    Like

  12. 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 :)

    Like

  13. 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

    Like

  14. 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.

    Like

  15. 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

    Like

    • 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

      Like

  16. Mind blowing solution….u really save my life…….my job ws on risk…thank u so much for the solution…..thanks a lot

    Like

  17. 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.

    Like

  18. 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?

    Like

  19. 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

    Like

  20. Hi Pinal,

    This very common error when disk space is low that database goes to inassessiable mode. Thanks for usefull article

    Regards
    Jayant Dass

    Like

  21. Thank you Pinal for this post!

    Following the provided solution help to resolve my issue.
    I cleaned the drive off unwanted stuff and then apply this

    use master
    alter database db_name set online

    Issue resolved.

    Like

  22. Hi its Moti
    I have a strange problem
    My Master Database is corrupt
    When i m starting the sqlserver it didn’t start
    Can anyone help me plz….

    Like

  23. How come I can rename an attached mdf file, thereby making the associated databases inaccessible, but when I try to copy said mdf file, it’s locked?

    Like

  24. Right click on the folder C:\ArrowSQL\Arr@Data and click on properties
    Click on security tab
    Click on Add button and add sql service account
    Provide modify privilege and click ok
    Verify both mdf and ldf have modify privilege
    Attach the db!

    Like

  25. Am frequently getting below message from my DMS database, i googled a lot to get a workable solution, no success yet. Can anybody help to resolve this ?

    Could not find database ID 2, name ‘tempdb’. The database may be offline. Wait a few minutes and try again.

    Raghu korambath.

    Like

  26. Msg 5120, Level 16, State 101, Line 1
    Unable to open the physical file “c:\myworks_master.mdf”. Operating system error 5: “5(Access is denied.)”.
    Msg 5120, Level 16, State 101, Line 1
    Unable to open the physical file “c:\myworks_log.ldf”. Operating system error 5: “5(Access is denied.)”.
    Msg 945, Level 14, State 2, Line 1
    Database ‘myworks’ 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.

    Like

  27. Msg 5120, Level 16, State 101, Line 1
    Unable to open the physical file “c:\myworks_master.mdf”. Operating system error 5: “5(Access is denied.)”.
    Msg 5120, Level 16, State 101, Line 1
    Unable to open the physical file “c:\myworks_log.ldf”. Operating system error 5: “5(Access is denied.)”.
    Msg 945, Level 14, State 2, Line 1
    Database ‘myworks’ 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.

    My database is read only , i made it offline and trying increasing file size it show the same error.
    Does any one clear my issue..
    Thanks in advance…!

    Like

  28. 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

    Like

  29. 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?

    Like

  30. 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.

    Like

  31. 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.

    Like

  32. 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..

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s