October 22, 2013 at 12:51 am
I have an auto generated script for creating a SQL Server Express 2012 database. I get error when I run it:
"Cannot use file 'C:\Databases\SQLDevTest.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 2
CREATE DATABASE failed. Some file names listed could not be created. Check related errors."
Please help
CREATE DATABASE [SQLDevTest]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'SQLDevTest', FILENAME = N'C:\Databases\SQLDevTest.mdf' , SIZE = 361472KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'SQLDevTest_log', FILENAME = N'C:\Databases\SQLDevTest.ldf' , SIZE = 9216KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
GO
ALTER DATABASE [SQLDevTest] SET COMPATIBILITY_LEVEL = 100
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [SQLDevTest].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [SQLDevTest] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [SQLDevTest] SET ANSI_NULLS OFF
GO
ALTER DATABASE [SQLDevTest] SET ANSI_PADDING OFF
GO
ALTER DATABASE [SQLDevTest] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [SQLDevTest] SET ARITHABORT OFF
GO
ALTER DATABASE [SQLDevTest] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [SQLDevTest] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [SQLDevTest] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [SQLDevTest] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [SQLDevTest] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [SQLDevTest] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [SQLDevTest] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [SQLDevTest] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [SQLDevTest] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [SQLDevTest] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [SQLDevTest] SET DISABLE_BROKER
GO
ALTER DATABASE [SQLDevTest] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [SQLDevTest] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [SQLDevTest] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [SQLDevTest] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [SQLDevTest] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [SQLDevTest] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [SQLDevTest] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [SQLDevTest] SET READ_WRITE
GO
ALTER DATABASE [SQLDevTest] SET RECOVERY FULL
GO
ALTER DATABASE [SQLDevTest] SET MULTI_USER
GO
ALTER DATABASE [SQLDevTest] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [SQLDevTest] SET DB_CHAINING OFF
GO
October 22, 2013 at 1:02 am
Which files does the script reference that you expect to be sent?
This is just a create database for a SQL Server 2012 database scripted from management studio. Yes, most of the options are set to their defaults, but there's nothing wrong with that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 22, 2013 at 1:08 am
I get error when I run it:
"Cannot use file 'C:\Databases\SQLDevTest.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 2
CREATE DATABASE failed. Some file names listed could not be created. Check related errors."
October 22, 2013 at 1:17 am
The error tells you exactly what's wrong. You're specifying a non-shared drive for database files for a clustered SQL Server.
"Cannot use file 'C:\Databases\SQLDevTest.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.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 22, 2013 at 1:23 am
when I try to create it with SQL Server 2012 I get this error:
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "C:\Databases\SQLDevTest.mdf" failed with the operating system error 2(error not found).
Msg 1802, Level 16, State 1, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
October 22, 2013 at 1:30 am
Does the directory exist? If not, then you will get an error.
These errors are ones you (being the DBA who knows the layout of the server and the drives) needs to fix, not your developer.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 22, 2013 at 1:32 am
Looking at the error you need to ensure that you have your dependencies set correctly in cluster manager.
Check the dependencies under the sql server service and ensure that your disks and server name are there correctly.
October 22, 2013 at 1:35 am
Thank I got it, on the path; FILENAME = N'C:\Databases\SQLDevTest.mdf'
I removed Database and left it as FILENAME = N'C:\SQLDevTest.mdf' and it worked.
Now I'm trying to alter it,: ALTER DATABASE [SQLDevTest] SET COMPATIBILITY_LEVEL = 100
and I get: "Incorrect syntax near '100'.", anything wrong with my syntax?
October 22, 2013 at 1:41 am
Database files in the root of C? Sure that's a good idea?
As for the ALTER DATABASE, the syntax you listed is correct. Make sure you haven't highlighted part of another command as well
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 22, 2013 at 1:45 am
The thing is when I put them under a folder I still get this error:
Directory lookup for the file "C:\Databases\SQLDevTest.mdf" failed with the operating system error 2(error not found).
Msg 1802, Level 16, State 1, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
And as for ALTER, I opened a new query and it's only this statement:
ALTER DATABASE [SQLDevTest] SET COMPATIBILITY_LEVEL = 100
GO
and I still the error: "Incorrect syntax near '100'."
October 22, 2013 at 1:53 am
hoseam (10/22/2013)
The thing is when I put them under a folder I still get this error:Directory lookup for the file "C:\Databases\SQLDevTest.mdf" failed with the operating system error 2(error not found).
Msg 1802, Level 16, State 1, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
Does the folder C:\Databases exist? If not, of course you're going to get an error.
Why don't you put the database in the normal location where you put the database files for the other databases on this instance?
ALTER DATABASE [SQLDevTest] SET COMPATIBILITY_LEVEL = 100
GO
and I still the error: "Incorrect syntax near '100'."
That's syntatically correct, I just ran it on my local instance, no errors.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 22, 2013 at 2:03 am
I do have a folder Databases, and even if I try any other folders I still the error:
Directory lookup for the file "path" failed with the operating system error
October 22, 2013 at 7:36 am
coming into this thread late, forgive me Gail if I repeat what you have said,
Why would you put a DB on the C:\ of a clustered SQL server? I would worry more the clustered drives than the compatibility mode. Do you have drives that are set as resources for the cluster? Have you looked in cluster manager yet to see if your drives are step correctly? Have you ran validation on the cluster yet?
Below is a link for Clustered Instances with Best Practices for 2012.
http://msdn.microsoft.com/en-us/library/ms189910.aspx
Good luck! 🙂
MCSE SQL Server 2012\2014\2016
October 22, 2013 at 7:37 am
Are you sure you're posting exactly what you're trying to run?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 22, 2013 at 11:16 am
Check to make sure the account that SQL runs as has full NTFS permissions to that folder.
I know you are just putting a "Test" DB in there but the fact that you are putting it there in a "Cluster" is just plain bad news and asking for trouble. I highly recommend you putting it on a Disk resource for the SQL cluster. Less headache that way.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply