SQL Server 2008 R2 Failover Cluster – Error Creating Database on New Storage

failoversql-server-2008-r2storage

Via the Failover Cluster Manager, I recently added a new disk to an existing SQL Server 2008 R2 Cluster, running on Windows Server 2012 R2.

When attempting to create a database on this new storage, I get the following error:

Msg 5123, Level 16, State 1, Line 1
CREATE FILE encountered operating system error 5(failed to retrieve text for this error. Reason: 15105) while attempting to open or create the physical file 'J:\DATA\mydatabase.mdf'.
Msg 1802, Level 16, State 4, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

I can manually add a file to J:\data
The new storage can be moved between nodes without any errors
The SQL Server Service Account has full control over the entire J:\ drive
The SQL Server Service Account has full control over the J:\DATA
Here is a layout of the drive, you will notice that DATA is a seperate volume mounted inside J:\

enter image description here

Can someone please help me figure out why I am getting this error?

Update

From James, if I add a subdirectory to data, then the create database command works

CREATE DATABASE [MCO_DB] ON  PRIMARY 
( NAME = N'MYDATABASE_DB', FILENAME = N'J:\DATA\MYDATABASE\MYDATABASE_DB.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'MYDATABASE_DB_log', FILENAME = N'J:\TRN\MYDATABASE_DB_1.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

Best Answer

Operating system error 5 is a simple Access is denied you can always check those errors in the command prompt net helpmsg 5.

Now Volume mount points are sometimes a bother. In Windows server 2000 the calcs.exe had an error and could not set the permissions on the underlying drive and the only way to do it was to mount the volume using a drive letter, set permissions on the root folder and then create a mountpoint.

Later versions of windows should be able set the permissions on the mountpoint by using the /M switch so that

cacls j:\data /g DOMAIN\ACCOUNT:f /M /E

Will grant the domain\account full permissions on the root of the drive mounted at j:\data, I still haven't leard to do this with icacls.exe and a comment how to do so will be well appriciated.

As you have found out creating a subfolder on the mount point makes it possible for you to create an accesslist for that folder but there still seem to be issues with setting permissions on the root folder of the drive using the GUI.

Usually I dont bother with the calcs command and if I need to set permissions on the root I'll add a driveletter, set the permissions using the GUI and then remove the drive letter.

Related Solutions

Sql-server – restore could not start database

Your restore is failing because there is no E:\ or F:\ on your machine.

Perform a RESTORE FILELISTONLY FROM DISK = 'path\file'; - this will tell you the logical names of the data and log files.
Construct a RESTORE DATABASE command based on this. For example (assuming the data files are named ECP_Data,ECP_Data_2, etc.):
```
RESTORE DATABASE ECO FROM DISK = 'path\file'
  WITH REPLACE, RECOVERY,
  MOVE 'ECP_Data'   TO 'C:\...path to MSSQL\Data\ECP_Data.mdf',
  MOVE 'ECP_Data_2' TO 'C:\...path to MSSQL\Data\ECP_Data2.ndf',
  MOVE 'ECP_Data_3' TO 'C:\...path to MSSQL\Data\ECP_Data3.ndf',
  MOVE 'ECP_Data_4' TO 'C:\...path to MSSQL\Data\ECP_Data4.ndf',
  MOVE 'ECP_Log'    TO 'C:\...path to MSSQL\Data\ECP_Log.ldf';
```
In addition to correcting the logical file names, replace C:\...path to MSSQL\Data\ with whatever path(s) you do want to use to store the data/log files. You don't need an individual folder per file, but you may still want to separate data and log files onto different disks. Note that it still might fail if whatever drive(s) you choose lack sufficient space to perform the restore.

SQL Server 2005 to 2008 R2 Restore Error 15105 – How to Fix

"MSSQL.1" is not the directory for a SQL Server 2008 R2 instance.

You should verify the directory you are trying to place the database in. The default data path for a normal installation is: "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLServer\MSSQL\Data".

Edit

If this is indeed the path of the databases for your SQL Server 2008 R2 instance then you can verify this by running the following query:


SELECT name, physical_name
FROM sys.master_files
WHERE database_id = 1

You should get results similar to this, highlighted the path for my instance: enter image description here

You can also verify this path through the restore prompt in SSMS, clicking on the "files": enter image description here However also noting that the error message operating system returned the error '5, the 5 normally indicates permissions issue with the service account running the database engine. If the full message was provided you would see something like

The operating system returned the error '5(Access is denied)' while attempting 'RestoreContainer::ValidateTargetForCreation'

Best Answer

Related Solutions

Sql-server – restore could not start database

SQL Server 2005 to 2008 R2 Restore Error 15105 – How to Fix

Related Question