locked
SQL Database corrupted RRS feed

  • Question

  • Hi,

    One of my database was inaccessible and we are getting below error, I have check on local drive .mdf file size is 0KB. It possible to recover the database.

    Msg 945, Level 14, State 2, Line 1
    Database 'IT_Support_Services' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.

    Thursday, August 11, 2016 11:21 AM

Answers

  • after that mdf file size is 0 KB
    Then you have to restore your latest database backup.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, August 11, 2016 4:25 PM
    Answerer
  • it seems like a disk space issue If possible add more hard drive space either by removing of unnecessary files from hard drive or add new hard drive with larger size or Check if the account which is trying to access the database has enough permission to perform operation. if this workaround doesn't fix your problem then you can try SQL Database Recovery Tool to fix the problem.
    Friday, August 12, 2016 4:44 AM

All replies

  • How odd,

    Check SQL Error log
    Do you have a backup? how old is?
    Was the DB on full recovery or simple?

    Thursday, August 11, 2016 11:29 AM
  • Hi, 

    I have check i think backup was happen, Need to check with storage team database in full recovery mode

    In case backup not there any possibility to recover the database
    • Edited by karthik1202 Thursday, August 11, 2016 12:20 PM
    Thursday, August 11, 2016 12:20 PM
  • Check the SQL Server error log.  It is likely a file is missing or a drive has failed and is unavailable.

    Thursday, August 11, 2016 12:25 PM
    Answerer
  • local drive was failed wintel team recovered the drive after that mdf file size is 0 KB
    Thursday, August 11, 2016 12:55 PM
  • after that mdf file size is 0 KB
    Then you have to restore your latest database backup.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, August 11, 2016 4:25 PM
    Answerer
  • it seems like a disk space issue If possible add more hard drive space either by removing of unnecessary files from hard drive or add new hard drive with larger size or Check if the account which is trying to access the database has enough permission to perform operation. if this workaround doesn't fix your problem then you can try SQL Database Recovery Tool to fix the problem.
    Friday, August 12, 2016 4:44 AM
  • Hi, karthik1202

    For your case, the best solution is to restore a previous backup. See https://msdn.microsoft.com/en-us/library/ms186858.aspx for more detailed info.

    However, if the backup is not available, or you want to recover the data AFTER the latest backup, you may have to recover the data from your MDF file or the drive.

    The SQL Server MDF database file format is very complex. It starts with a file header containing the meta information of the whole file, followed by several parts containing the actual data. You can find more detailed information about the SQL Server MDF database file format by searching on Google with the keyword “SQL Server MDF database file format”. If the file header containing the meta information is corrupt, or any follow-up parts are damaged or corrupted, then you will not be able to open the file correctly with SQL Server. In such a case, the first step is to try to use the built-in repair or recovery function in the SQL Server, to see if it can repair SQL Server file. If not, then you need to resort to a third-party SQL Server repair software to scan the corrupted file and retrieve all recoverable data for you. It is also possible to find a data recovery expert or company to do the task, but in most of the cases, they will also use a third-party tool to do the recovery for you.

    The main difference between using the software by yourself and by a third-party service company is:
    (1) The company is familiar with the data recovery industry so he knows which software is the best one. While you need to search and compare the software online by yourself, which is time-consuming.
    (2) The company purchases the software for multiple usages, while you purchase the software for one-time usage only. So normally the service fee may be cheaper than the license fee for the software.
    (3) DIY will keep your data confidential while do the recovery via a company may cause data breach.

    If you want to perform a DIY recovery, then you can create a disk image of the local drive where you have stored your .MDF file in the past,  and then recover the data from the image file. See https://www.datanumen.com/sql-recovery/disk-file-recovery.htm

    Hope this helps.

    Good luck!




    • Edited by chcw Saturday, April 1, 2017 11:25 PM
    Tuesday, September 6, 2016 2:16 AM
  • Hi,

    To fix the issue, check the following points: 

    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.

    Ref: http://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/

    Tuesday, September 6, 2016 3:55 AM