[Fixed]: SQL Error 945 - Database cannot be Opened due to Inaccessible Files

The error 945 in MS SQL Server can occur when the server fails to access the system databases (master or msdb). It may arise during or after attaching or detaching a SQL database. Sometimes, restore operations can also trigger this error. In an Always On Availability group environment, you may encounter this error while downloading updates on the server, especially the cumulative updates. When it occurs, the affected database may be marked as suspect or IsShutdown. This can interrupt your database operations and you may be unable to execute queries on the database.

Here, we will discuss the reasons behind the SQL Server error 945 and some easy methods to resolve it.

Causes for SQL Server Error 945

The Error 945: DB_IS_SHUTDOWN may occur due to the following reasons:

• You might be restoring the corrupt backup.
• Your system’s disk is out-of-space.
• The MDF/NDF files are damaged or corrupted.
• Lack of permissions to read or write files in SQL Service Account.
• You might-be using the incorrect file path while restoring the backup file.
• Auto-grow feature is disabled.
• The transaction log file is missing in msdb database.
• When SSISDB upgrade fails in Always on Availability group due to mode conflict.
• The .mdf or .ldf file is marked as read-only at the operating system file level.

Methods to Resolve SQL Error 945

When you encounter this error, first check the status of the problematic database by executing the DATABASEPROPERTY function (see below).

SELECT DATABASEPROPERTYEX('database15', 'IsShutdown');

The above command will display the current status of the mentioned database. If the status shows the value as 1, it indicates that the database is in the IsShutdown state. So, you need to bring its status to Online. For this, use the ALTER DATABASE SET options. If it fails to bring the database into consistent state, then open the SQL Server error log to find the reason responsible for the issue. Accordingly, follow the troubleshooting methods given below.

Method 1 - Use sp_add_log_file_recover_suspect_db()

If the transaction log is out-of-space, the SQL Server may be unsuccessful in loading the log file, causing the error 945. In this instance, the solution is quite straightforward - use sp_add_log_file_recover_suspect_db() to generate a new log file and add it to database. This method is helpful in a specific case where your database recovery gets stuck in recovery mode due to this error. When the new transaction log file is created, it automatically removes the suspect flag from the database. Syntax of sp_add_log_file_recover_suspect_db() is:

If the error has occurred due to insufficient data space in the file group, then you can use

Once you have recreated the log/data file, add it to your database.

Suggested Read: How to Resolve SQL Server error 9002 due to transaction log file

Method 2 - Check the AutoGrowth Feature

If the AutoGrowth option is disabled and the database has utilized its maximum allocated size, it may lead to the error 945. In this case, you can enable it to let the database easily expand as needed. Here's how to enable it and fix the issue.

• In your SQL Server instance, go to the problematic database.
• Click View and then select Object Explorer.

• Right-click on the database and select Properties.
• In Properties window, select the files, click Autogrowth/Maxsize, select enable Autogrowth and click OK.

Method 3 - Fix SSISDB Upgrade Conflicts with Always on Availability Group

You may encounter SQL Server error 945 during an upgrade when SQL Server attempts to apply script-level changes to the system database. It usually happens when you’re accessing databases in Always On Availability mode. The error arises because the server enters single-user mode while applying cumulative updates or a service pack (Upgrading script). However, if the Always On Availability Group is configured with SSISDB, the system requires multi-user mode. During the upgrade, SSISDB goes offline, resulting in this error.

To resolve it, try removing SSISDB from the Availability Group just before applying the update. Next, run the cumulative update on the system individually. Once the update is complete, re-add SSISDB to your Always On Availability Group. Refer Use the Availability Group Wizard.

Method 4 - Check and Grant Permissions

Do you have all the permissions to the SQL Server Service Account, MDF/NDF file, and backup file? If not, this might be the cause of the ‘database cannot be opened’ error. If this case, follow these simple instructions to verify and grant the required permissions to the SQL Server service account by following these steps:

• First, find the SQL Server Service Account. For this,
  • Open SQL Server Configuration Manager.
  • Under SQL Server services, right-click on the service and select Properties.
  • In the SQL Server (MSSQL SERVER) Properties window, click Log on. Note down the Server account name.

• Next, go to the MS SQL Data Directory by following this path:

• Right-click on DATA and select Properties.

• In Properties, click on the Security tab and click Edit.
• Check if the service account (noted earlier) is present. If it is missing, then add the Service Account.
• Check and grant all the necessary permissions, including Full Control, Read, Write, and Modify.
• Click Apply and then click OK.
• Restart the SQL Server service.  

Method 5 - Check File Path during Backup Restore

If the path of backup file is incorrect or invalid, then SQL Server may fail to read the backup file, leading to SQL Server error 945. In this case, the solution is quite simple - verify your backup file and make sure it has .BAK extension and its path is correct.

Method 6 - Check and Repair MDF/NDF Files

This SQL Server error 945 can occur if the system databases (master database or msdb) are corrupted, damaged, or inaccessible. You can check and repair system databases, including master and msdb, using the DBCC CHECKDB command as given below:

If this command fails to bring the desired outcome, then another option is to rebuild the system databases. Nevertheless, you need to keep in mind that this process involves reconfiguration and restoration of the database from backup, which is quite time-taking.

Repair Database using Stellar Repair for MS SQL

To prevent data loss and quickly repair the SQL database files, you can use Stellar Repair for MS SQL. It is an advanced SQL database repair tool that can repair corrupt database file and recover all the objects from the file with complete integrity. It can help resolve all types of errors, like SQL error 945, error 8909, etc., that majorly occur due to corruption in SQL database files.

Watch this video to know how Stellar Repair for MS SQL repairs SQL database:

Conclusion

As mentioned in this article, you’ll experience the error 945 in SQL Server due to varied reasons. The major consequence of this error is that the database enters the suspect mode and becomes unavailable to users. You can check the status of the database and the error log. And follow the different troubleshooting methods discussed in this article to resolve it. You can manage the transaction log file or repair the SQL Server databases using Stellar Repair for MS SQL with precision and integrity.