none
Database 'mydatabase' cannot be opened due to inaccessible files or insufficient memory or disk space. (need to know actual reason.)

    Question

  • Hello Everyone,

    I have SQL Server Express Edition on my server (Windows Server 2008 R2). And have a database for my website on it.

    My database stopped working today. The error which my website showing was like this:

    Login failed to database 'mydatabase' .... from the user 'user1' ...

    I am sorry I forgot to copy that error.

    At first, to see the reason for the issue I opened the "SQL Management Studio" and found that the database was there but it was not extendable and when I tried to open its properties, I got this error:

    Database 'mydatabase' 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)

    The space on my disk is approx 60GB, so I was sure that disk space is not the issue.

    So I reviewed the log and I found this error message:

    FCB::Open failed: Could not open file C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mydatabase.mdf for file number 1. OS error: 32 (The process cannot access the file because it is being used by another process.)

    Then Second, I searched on web for any solution and after reading many articles I found that my database may be corrupted and I have to reattach it, but should also take a backup of database first. I stopped SQL Server Services, copied the database and took a backup of that. And to reattach the database I started the SQL Server Services again. And see what! my database was accessible this time and my website started working fine, without any error.

    This is completely out of my head. I don't get it, why this issue occurred? why it resolved after an easy restart?

    I don't have much knowledge about SQL Server, I am just an end user. I need help from all you knowledgeable persons, to please tell me what can be the reasons for the issue. So that I can make sure it will not happen again.

    Please reply, I will be very thankful to you.

    Regards,

    Namrata Mathur






    • Edited by nmathur Friday, August 24, 2012 4:44 AM
    Thursday, August 23, 2012 5:37 AM

Answers

  • Hi nmathur

    please check the properties of your database,model  and set them as false in

    auto close ->false

    then run

    DBCC CHECKDB WITH NO_INFOMSGS;
    GO


    Ramesh Babu Vavilla MCTS,MSBI


    • Edited by vr.babu Friday, August 24, 2012 5:12 AM
    • Marked as answer by nmathur Friday, August 24, 2012 6:04 AM
    Friday, August 24, 2012 5:10 AM

All replies

  • error says that insufficient disk space, please run xp_readerrorlog in the SQL Server Management Studio to get the error details ,

    check the below

    1) database growth ( any database limit is set)

    2) disk Space on the drive

    3) Log file space of the database ,use DBCC SQLPERF(LOGSPACE) ,To get the ldf file size, it the database is not set for Log shipping or mirroring,then set the database to simple recovery mode and shrink the file

    4) database growth size of the tempdb


    Ramesh Babu Vavilla MCTS,MSBI

    Thursday, August 23, 2012 8:35 AM
  • Hello,

    Thanks for reply.

    Please tell me how to check if there is any limit set for size of database and if yes then how to increase that.

    And can you please tell me what can be the reason that the database get accessible after restarting the services.

    Thanks again.


    -- NMathur

    Thursday, August 23, 2012 9:01 AM
  • Hi,

    I checked all these, mentioned below:

    The auto-growth for database is " MB, unrestricted growth " and for log file it is "By 10 percent, restricted growth to 2097152 MB".

    Log Size (MB) is - 23.80469
    Log space used (%) - 10.7811

    Disk space on the drive is 60GB.

    So, any of above can be the cause for the issue or not? What else I should check.

    Please reply.

    Thanks and Regards.



    • Edited by nmathur Thursday, August 23, 2012 9:14 AM
    Thursday, August 23, 2012 9:11 AM
  • did you check the available disk space

    check for user level permissions alloted to the database


    Ramesh Babu Vavilla MCTS,MSBI


    • Edited by vr.babu Thursday, August 23, 2012 10:17 AM
    Thursday, August 23, 2012 10:16 AM
  • Available space on my hard disk is 60 GB. In database's properties the space available for database is 2 MB.

    The user is the owner of database and has full control on database, even user has full access to the folder where the database (.mdf and .log files) is saved.

    I do not know, why properties showing space available when size limit is unrestricted for the database.


    -- NMathur

    Thursday, August 23, 2012 11:35 AM
  • please run xp_readerrorlog command in the sql server management studio.

    and paste the error ,


    Ramesh Babu Vavilla MCTS,MSBI

    Thursday, August 23, 2012 11:40 AM
  • Hello Ramesh,

    Thanks a lot for your help.

    Here is the code:

    LogDate                           ProcessInfo        Text
    2012-08-22 21:53:43.340    Server    Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64)    Jul  9 2008 14:17:44    Copyright (c) 1988-2008 Microsoft Corporation   Express Edition with Advanced Services (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)  
    2012-08-22 21:53:43.340    Server    (c) 2005 Microsoft Corporation.
    2012-08-22 21:53:43.340    Server    All rights reserved.
    2012-08-22 21:53:43.340    Server    Server process ID is 448.
    2012-08-22 21:53:43.340    Server    System Manufacturer: 'Parallels Software International Inc.', System Model: 'Parallels Virtual Platform'.
    2012-08-22 21:53:43.340    Server    Authentication mode is MIXED.
    2012-08-22 21:53:43.340    Server    Logging SQL Server messages in file 'c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Log\ERRORLOG'.
    2012-08-22 21:53:43.340    Server    This instance of SQL Server last reported using a process ID of 1116 at 8/22/2012 9:50:43 PM (local) 8/23/2012 4:50:43 AM (UTC). This is an informational message only; no user action is required.
    2012-08-22 21:53:43.340    Server    Registry startup parameters:     -d c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\master.mdf    -e c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Log\ERRORLOG    -l c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\mastlog.ldf
    2012-08-22 21:53:43.360    Server    SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
    2012-08-22 21:53:43.360    Server    Detected 1 CPUs. This is an informational message; no user action is required.
    2012-08-22 21:53:43.420    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.
    2012-08-22 21:53:48.890    Server    Node configuration: node 0: CPU mask: 0x0000000000000001 Active CPU mask: 0x0000000000000001. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
    2012-08-22 21:53:48.950    spid7s    Starting up database 'master'.
    2012-08-22 21:53:49.100    spid7s    Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
    2012-08-22 21:53:49.200    spid7s    FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'SQLEXPRESS'.
    2012-08-22 21:53:49.230    spid7s    SQL Trace ID 1 was started by login "sa".
    2012-08-22 21:53:49.230    spid7s    Starting up database 'mssqlsystemresource'.
    2012-08-22 21:53:49.250    spid7s    The resource database build version is 10.00.1600. This is an informational message only. No user action is required.
    2012-08-22 21:53:49.570    Server    A self-generated certificate was successfully loaded for encryption.
    2012-08-22 21:53:49.590    Server    Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\SQLEXPRESS ].
    2012-08-22 21:53:49.590    Server    Server local connection provider is ready to accept connection on [ \\.\pipe\MSSQL$SQLEXPRESS\sql\query ].
    2012-08-22 21:53:49.590    spid10s    Starting up database 'model'.
    2012-08-22 21:53:49.590    Server    Dedicated administrator connection support was not started because it is disabled on this edition of SQL Server. If you want to use a dedicated administrator connection, restart SQL Server using the trace flag 7806. This is an informational message only. No user action is required.
    2012-08-22 21:53:49.590    Server    The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
    2012-08-22 21:53:49.590    spid7s    Server name is 'CSSVPS03\SQLEXPRESS'. This is an informational message only. No user action is required.
    2012-08-22 21:53:49.600    Server    SQL Server is now ready for client connections. This is an informational message; no user action is required.
    2012-08-22 21:53:49.760    spid13s    A new instance of the full-text filter daemon host process has been successfully started.
    2012-08-22 21:53:49.790    spid7s    Starting up database 'msdb'.
    2012-08-22 21:53:49.910    spid10s    Clearing tempdb database.
    2012-08-22 21:53:53.010    spid10s    Starting up database 'tempdb'.
    2012-08-22 21:53:53.040    spid13s    The Service Broker protocol transport is disabled or not configured.
    2012-08-22 21:53:53.040    spid13s    The Database Mirroring protocol transport is disabled or not configured.
    2012-08-22 21:53:53.080    spid13s    Service Broker manager has started.
    2012-08-22 21:53:53.080    spid7s    Recovery is complete. This is an informational message only. No user action is required.
    2012-08-22 21:53:55.940    spid51    Attempting to load library 'xpstar.dll' into memory. This is an informational message only. No user action is required.
    2012-08-22 21:53:55.940    spid51    Using 'xpstar.dll' version '2007.100.1600' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.
    2012-08-22 21:54:00.450    spid51    Starting up database 'ReportServer$SQLEXPRESS'.
    2012-08-22 21:54:00.630    spid51    Starting up database 'ReportServer$SQLEXPRESSTempDB'.
    2012-08-22 21:54:00.690    spid51    Starting up database 'mydatabase'.
    2012-08-22 21:54:02.630    spid24s    Starting up database 'mydatabase'.
    2012-08-22 21:54:03.610    spid23s    Starting up database 'mydatabase'.
    2012-08-22 21:54:03.760    spid29s    Starting up database 'mydatabase'.
    2012-08-22 21:54:04.660    spid23s    Starting up database 'mydatabase'.
    2012-08-22 21:54:05.030    spid51    Starting up database 'mydatabase'.
    2012-08-22 21:54:05.820    spid28s    Starting up database 'mydatabase'.
    2012-08-22 21:54:06.120    spid22s    Starting up database 'mydatabase'.
    2012-08-22 21:54:06.200    spid52    Starting up database 'ReportServer$SQLEXPRESS'.
    2012-08-22 21:54:06.310    spid27s    Starting up database 'mydatabase'.
    2012-08-22 21:54:06.870    spid26s    Starting up database 'mydatabase'.
    2012-08-22 21:54:07.820    spid29s    Starting up database 'mydatabase'.
    2012-08-22 21:54:08.160    spid22s    Starting up database 'mydatabase'.
    2012-08-22 21:54:08.390    spid27s    Starting up database 'mydatabase'.
    2012-08-22 21:54:09.840    spid27s    Starting up database 'mydatabase'.
    2012-08-22 21:54:10.890    spid26s    Starting up database 'mydatabase'.
    2012-08-22 21:54:11.890    spid27s    Starting up database 'mydatabase'.
    2012-08-22 21:54:13.010    spid26s    Starting up database 'mydatabase'.
    2012-08-22 21:54:13.950    spid27s    Starting up database 'mydatabase'.
    2012-08-22 21:54:15.120    spid26s    Starting up database 'mydatabase'.
    2012-08-22 21:54:20.230    spid23s    A new instance of the full-text filter daemon host process has been successfully started.
    2012-08-22 21:59:28.400    spid54    Starting up database 'ReportServer$SQLEXPRESSTempDB'.
    2012-08-22 21:59:28.740    spid52    Starting up database 'ReportServer$SQLEXPRESSTempDB'.
    2012-08-22 22:06:58.010    spid52    Starting up database 'mydatabase'.
    2012-08-22 22:09:19.520    spid51    Starting up database 'ReportServer$SQLEXPRESSTempDB'.
    2012-08-22 22:19:21.510    spid52    Starting up database 'ReportServer$SQLEXPRESSTempDB'.
    2012-08-22 22:29:29.520    spid51    Starting up database 'ReportServer$SQLEXPRESSTempDB'.
    2012-08-22 22:39:34.530    spid52    Starting up database 'ReportServer$SQLEXPRESSTempDB'.
    2012-08-22 22:49:41.530    spid51    Starting up database 'ReportServer$SQLEXPRESSTempDB'.
    2012-08-22 22:59:45.530    spid52    Starting up database 'ReportServer$SQLEXPRESSTempDB'.
    2012-08-22 23:09:49.520    spid51    Starting up database 'ReportServer$SQLEXPRESSTempDB'.
    2012-08-22 23:19:53.510    spid52    Starting up database 'ReportServer$SQLEXPRESSTempDB'.
    2012-08-22 23:29:55.520    spid51    Starting up database 'ReportServer$SQLEXPRESSTempDB'.
    2012-08-22 23:39:59.500    spid52    Starting up database 'ReportServer$SQLEXPRESSTempDB'.
    2012-08-22 23:49:09.230    spid51    Starting up database 'mydatabase'.
    2012-08-22 23:50:05.520    spid53    Starting up database 'ReportServer$SQLEXPRESSTempDB'.
    2012-08-23 00:00:08.520    spid51    Starting up database 'ReportServer$SQLEXPRESSTempDB'.
    2012-08-23 00:04:45.340    spid51    Starting up database 'ReportServer$SQLEXPRESSTempDB'.
    2012-08-23 00:04:45.850    spid51    Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer$SQLEXPRESS.
    2012-08-23 00:04:45.920    spid51    Starting up database 'ReportServer$SQLEXPRESSTempDB'.
    2012-08-23 00:04:45.960    spid51    Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer$SQLEXPRESSTempDB.
    2012-08-23 00:14:54.520    spid52    Starting up database 'ReportServer$SQLEXPRESSTempDB'.
    2012-08-23 00:24:56.460    spid51    Starting up database 'ReportServer$SQLEXPRESSTempDB'.
    2012-08-23 00:33:07.450    spid52    Starting up database 'mydatabase'.
    2012-08-23 00:35:01.460    spid53    Starting up database 'ReportServer$SQLEXPRESSTempDB'.
    2012-08-23 00:45:04.450    spid51    Starting up database 'ReportServer$SQLEXPRESSTempDB'.
    2012-08-23 00:55:08.430    spid52    Starting up database 'ReportServer$SQLEXPRESSTempDB'.
    2012-08-23 01:05:11.460    spid51    Starting up database 'ReportServer$SQLEXPRESSTempDB'.
    2012-08-23 01:15:14.460    spid52    Starting up database 'ReportServer$SQLEXPRESSTempDB'.
    2012-08-23 01:25:16.460    spid51    Starting up database 'ReportServer$SQLEXPRESSTempDB'.
    2012-08-23 01:31:49.830    spid52    Starting up database 'mydatabase'.
    2012-08-23 01:35:20.460    spid53    Starting up database 'ReportServer$SQLEXPRESSTempDB'.
    2012-08-23 01:45:24.430    spid51    Starting up database 'ReportServer$SQLEXPRESSTempDB'.
    2012-08-23 01:45:30.960    spid51    Starting up database 'ReportServer$SQLEXPRESSTempDB'.
    2012-08-23 01:46:34.760    spid54    Starting up database 'mydatabase'.
    2012-08-23 01:54:53.990    spid51    Starting up database 'mydatabase'.
    2012-08-23 01:55:27.460    spid53    Starting up database 'ReportServer$SQLEXPRESSTempDB'.
    2012-08-23 01:56:58.290    spid56    Starting up database 'ReportServer$SQLEXPRESSTempDB'.
    2012-08-23 01:57:27.640    spid54    Starting up database 'ReportServer$SQLEXPRESSTempDB'.
    2012-08-23 02:00:46.510    spid53    Starting up database 'ReportServer$SQLEXPRESSTempDB'.
    2012-08-23 02:00:48.750    spid53    Starting up database 'ReportServer$SQLEXPRESSTempDB'.
    2012-08-23 02:02:49.330    spid51    Starting up database 'ReportServer$SQLEXPRESSTempDB'.
    2012-08-23 02:02:49.340    spid51    Starting up database 'mydatabase'.
    2012-08-23 02:02:50.430    spid27s    Starting up database 'mydatabase'.
    2012-08-23 02:02:50.610    spid23s    Starting up database 'mydatabase'.
    2012-08-23 02:02:51.110    spid22s    Starting up database 'mydatabase'.
    2012-08-23 02:02:51.410    spid35s    Starting up database 'mydatabase'.
    2012-08-23 02:02:51.650    spid23s    Starting up database 'mydatabase'.
    2012-08-23 02:02:52.150    spid22s    Starting up database 'mydatabase'.

    Regards,

    NMathur



    • Edited by nmathur Friday, August 24, 2012 4:40 AM
    Friday, August 24, 2012 4:39 AM
  • Hi nmathur

    please check the properties of your database,model  and set them as false in

    auto close ->false

    then run

    DBCC CHECKDB WITH NO_INFOMSGS;
    GO


    Ramesh Babu Vavilla MCTS,MSBI


    • Edited by vr.babu Friday, August 24, 2012 5:12 AM
    • Marked as answer by nmathur Friday, August 24, 2012 6:04 AM
    Friday, August 24, 2012 5:10 AM
  • Hi Ramesh,

    Auto close for my database is already false and I also executed the command.

    Please explain the effect of the command which you want me to run, how it will help me. And were you able to find out the reason for the issue after reviewing error log I posted in previous reply. If yes then please also explain that, so I and anyone else can understand it. It will be very helpful for us.

    Thanks and Regards.


    NMathur


    • Edited by nmathur Friday, August 24, 2012 5:41 AM
    Friday, August 24, 2012 5:33 AM
  • hi NMathur,

    there is a problem in starting your database, this generally happens when your model database is set in auto close option,

    dbcc checkdb

    command checks your database and repairs them


    Ramesh Babu Vavilla MCTS,MSBI

    Friday, August 24, 2012 5:43 AM
  • Hello Ramesh,

    Thanks for your help.

    Is there any chance that I can get this error again?

    What should I do to make sure that it will not happen again.

    Thanks again.


    NMathur

    Friday, August 24, 2012 5:48 AM
  • Hi Matur.

    please make sure that your have optimal database setting in your SQL Server,

    Mark as answer if you are happy with my answer


    Ramesh Babu Vavilla MCTS,MSBI

    Friday, August 24, 2012 6:02 AM
  • Hi Ramesh,

    What are the setting that you will consider as optimal settings for database. Please share some major settings, I will be very thankful to you.

    Regards.


    NMathur


    • Edited by nmathur Friday, August 24, 2012 6:06 AM
    Friday, August 24, 2012 6:06 AM
  • Hi Ramesh,

       I am not able to access the my offline database how can i  check the autogrowth for offline database. Please helpme.

    my error.

    RAhulreddy

     

         

    Friday, September 06, 2013 4:34 AM