CREATE DATABASE failed on SQL Server FCI cluster: Only formatted files on which the cluster resource of the server has a dependency can be used

Problem

Today, I have a request to create a new database on two-node SQL Server 2016 failover cluster instance, which is in an active/passive configuration. I connected to the clustered SQL Server instance and ran the CREATE DATABASE command similar to the one below:

USE [master]
GO

CREATE DATABASE [MyDatabase]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'MyDatabase_001_Data', FILENAME = N'E:\DATA_MyServer\MyDatabase.mdf' , SIZE = 2097152KB , MAXSIZE = 2097152KB , FILEGROWTH = 262144KB )
 LOG ON
( NAME = N'MyDatabase_001_Log', FILENAME = N'E:\LOG_MyServer\MyDatabase_log.ldf' , SIZE = 4390912KB , MAXSIZE = 2048GB , FILEGROWTH = 262144KB )
GO

However, when I executed the command, I received the following error:

Msg 5184, Level 16, State 2, Line 1
Cannot use file 'E:\DATA_MyServer\MyDatabase.mdf' for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used. Either the disk resource containing the file is not present in the cluster group or the cluster resource of the Sql Server does not have a dependency on it.
Msg 1802, Level 16, State 1, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

Reason for error

This error is self-explanatory. This error occurs when the disks resources containing SQL Server database data and log files are not added as a dependency of the SQL Server cluster resource.

Solution

  • Open the Failover Cluster Manager snap-in.
  • Select the SQL Server resource, open the Properties dialog box, and use the Dependencies tab to add the disk to the set of SQL Server dependencies.
  • Next, click OK to save the setting and to close the Properties dialog box.

How to change Integration Services Catalog (SSISDB) database Master Key encryption password?

To change the Integration Services Catalog (SSISDB) database Master Key encryption password, run the following Transact-SQL statement:

USE [SSISDB];
GO

OPEN MASTER KEY DECRYPTION BY PASSWORD = N'[old_password]'; -- Password used when creating SSISDB
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = N'[new_password]';
GO

In the above Transact-SQL fragment, we first open the Integration Services Catalog (SSISDB) database Master Key with the existing password, and then regenerated it with a new one.

Database Issue: The transaction log for database ‘db_name’ is full due to ‘ACTIVE_BACKUP_OR_RESTORE’.

Today, I was called in the early hours because the transaction log of one of the SQL Server 2016 database was full. I logged onto the SQL server instance via SQL Server Management Studio, and saw the following errors in the SQL Server error log:

The transaction log for database 'OLTPDB' is full
due to 'ACTIVE_BACKUP_OR_RESTORE'.

Reason for failure

This occurs because the full backup for the ‘OLTPDB’ database was still running, and as a result, the transaction log of this database was not freed up for reuse. This caused the transactions that were running against this database to fail.

Issue resolution

As we know, the portion of the transaction log file that is used during the full backup operation must be maintained while the full backup operation is running, which will also be backed up at the end of the full backup operation. That’s because the full database backup contains all the database data at the start of the full backup operation plus the transition log used until the end of the full backup operation. Therefore, to fix the issue, I had to manually increase the size of the transaction log file, to accommodate the high load against the database.