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_MyServerMyDatabase.mdf' , SIZE = 2097152KB , MAXSIZE = 2097152KB , FILEGROWTH = 262144KB ) LOG ON ( NAME = N'MyDatabase_001_Log', FILENAME = N'E:LOG_MyServerMyDatabase_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_MyServerMyDatabase.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.