Getting little help and learning from each other always helps. When I don’t get to know of something and I need to explore something, I try to check with my SQL friends who can guide me in the right direction. This blog is a classic example of me trying to learn something and help the community. Recently I got an email from my blog reader as below:
<EMAIL>
Hi Pinal,
My Name is Aysha and I have been a regular follower of you blog. Just to let you know that I am a hard-core developer, but also an accidental DBA. So please pardon me if this is a really simple issue for you. Also, I don’t have much knowledge about cluster. Here is the problem: I needed to create a new database on the SQL Server 2012 two node failover cluster to host database for new application under development. By following few internet articles, I added a new drive (M and N) into the SQL Group in the cluster, but I am unable to create database on the newly added drive.
Here is the command I was trying
CREATE DATABASE [TestDatabase]
ON PRIMARY
(NAME = N'TestDatabase', FILENAME = N'M:\SQLDATA\TestDatabase.mdf' )
LOG ON
(NAME = N'TestDatabase_log', FILENAME = N'N:\SQLLOG\TestDatabase_log.ldf')
But here is the error I am receiving
Msg 5184, Level 16, State 2, Line 1
Cannot use file ‘M:\SQLDATA\Testdatabase.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.
Do you think I did something wrong? I have restarted SQL multiple times, but still same error. Can you please help?
Thanks
Aysha
<EMAIL>
Here is my reply.
<Reply>
Hi Aysha.
Thanks for contacting me and giving me all the details which I always expect from anyone who contacts me. Since you are new to cluster, you need to read a little more about the dependencies of cluster resource. Dependency decides the order in which they can come online. The child resource must come online before an attempt is made to bring parent resource online in a cluster.
- The IP Address and the Disk Resource do not depend on anything.
- SQL Network Name depends on the IP Address.
- SQL Server depends on the Network Name and a Disk Resource.
- SQL Agent depends on SQL Server.
Coming back to problem which you are facing (i.e. below error). Here is a breakup of error
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.
See that, dependency terms in error message?
So, all you need to do is add dependency of disk M and N which you have added post installation, to the SQL Server resource. SQL Server is preventing us from creating databases on drive which aren’t a dependency of the SQL Server resource. This is to make sure that the disk is online before the SQL is trying to access database file.
Here are the steps
- Open Failover Cluster Manager from Start Menu OR you can also go to Start > Run > Cluadmin
- Select the clustered application, i.e. the group which has SQL Server.
- Right-click on your “SQL Server” resource then choose Properties.
- Go to Dependencies tab.
- Click the Insert button to add an additional row, then select the shared disk (M) that is going to contain your SQL databases. In next row N also to be added.
- Ensure that all resources are required (there is an AND next to subsequent resources) then click OK.
That’s it. Depending on the version of SQL, you might need to take SQL Server resource offline and bring it back online by choosing from right click.
Hope this helps!
</Reply>
Later she responded back and informed me that she was able to fix the issue after following the steps.
Home work: Can you help me out with this – which SQL version does not need restart after modifying dependencies of the disk? If you already have a working SQL cluster, please test and let me know by commenting. This was a great learning for me too, and felt I would quiz you with some research.
Reference: Pinal Dave (https://blog.sqlauthority.com)
30 Comments. Leave new
Hi Pinal,
SQL Server 2008 R2 and later versions – just a guess.
Thanks,
Anil
No. It’s in older version also.
Confirmed SQL 2014 does not need a restart.
version 12.0.4213
Hi Pinal,
I’m having the same issue. Except that with Windows 2012 R2 the storage is assigned to the SQL Server role, there is no dependency tab in the role properties, the dependencies powershell shows the disk as a dependency for SQL Server… But SQL Server still won’t use it.
Any idea, will be greatly appreciated.
Thanks,
Jane
Jane, I’m sure you already took care of this, but for others… you need to click the properties on the SQL Server Resource and not the SQL Server Role. The resource shows up in the bottom of the screen in the “Other Resources” section.
Correct. Thanks for replying and helping Rob.
Thanks Rob,that helped me.
work fine for me! on similar errors of restore DB with a new LUN.
MSSQL 2012 CLUSTER
Thanks @Juan
Dave I always like your clarity and Rob thanks for point the specific properties resource at the bottom.! ! ! That did the trick for me.
Great tip, got this error after adding adding a second cluster storage drive to my cluster that wasn’t related to SQL
Thanks for this tips
My Data drive not in Dependencies Why ? don’t know ..
while I create database face same issue database working fine ,
but if I add Data drive back in Dependencies I should restart
windows 2012
Sql server 2012 Sp3
Thanks :)
Thank You .. You saved my “CommVault Version 11” day .. Now the restore works,
I had this error during restore session to newly added disk .:
Query Result [Microsoft.SqlServer.Management.Common.ExecutionFailureException:Cannot use file ‘T:\MSSQL11.SQL\MSSQL\DATA\Xxxxxxxxx_restore_31aug2016.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. File ‘Xxxxxxxxx’ cannot be restored to ‘T:\MSSQL11.SQL\MSSQL\DATA\Xxxxxxxxx_restore_31aug2016.mdf’. Use WITH MOVE to identify a valid location for the file.
Regards Martin Roende
You are always welcome!
Hi Pinal,
This is exactly my issue too. but when I opened properties of “Cluster Core Resources” Server name: blasqlxx
the only dependency I could add to it from the drop down menu is “Quorum”
Is this normal? However, I have another working cluster and it behaves the same. can you shed some light please? also, in the particular broken SQL cluster, the user I have can’t access the Quorum files. Is this an indication of failure? Thanks very much in advance.
“Cluster Core Resources” or any other group would show only the resources in same group.
This corrected my above issue. Exact steps mentioned by u Rob…kudos!!
I’m getting the error on this topic while trying to move an existing DB from instance A to instance B inside my SQL Cluster. Now I know that if I add the storage and network from Instance A to dependencies of Instance B, it would work. However, in the end I want to remove Instance A from the whole picture.
I am not able to understand what exactly you want to do. Can you please explain more? I am guessing that you have two Nodes A and B. To remove node from SQL cluster, you need to perform RemoveNode operation in SQL setup. Once done, you need to evict node from Windows Cluster.
I can see the tip successful . I didn’t observed this issue in versions older than 2012 . I faced it in 2016 version. Can you give me a detailed explanation , why we need to add the drives.
its same since SQL 2000.
Great tip, got this error after adding adding a new cluster disk to my cluster that wasn’t in dependencies list
thanks
my pleasure.
Thanks for the article.
Thank you Sir, it helped me address the issue.
SQL Server 2012 SP4 dint required a reboot
Note that the disks need to be assigned to the SQL Server role in the first place otherwise they won’t be available to be added as dependencies – i.e. right-clck the SQL Server role and select ‘Add Storage’.
… always love dropping in here when the blog has something I am checking into and poof the exact answer i looking for … now onto the bigger fish for the day … thanks again