SQL Server - SQL SERVER DATABASE CREATION ERROR'S

Asked By Ricus

01-Sep-09 05:25 AM

I have a new server we are installing Sql STANDARD 2005 x64 and windows
server STANDARD 2008 r2 x64 on .

I have installed sp3 for Sql 2005 as per the prompts you receive while
installing Sql.
Database service is installed but not analysis services.

I originally installed analysis services but then uninstalled it.

I then went  and specified the data and logs folder paths in Sql config.

The data and log folders are on 2 separate volumes setup as mount points.

On trying to create a new database I get the following error.

****
------------------------------

Create failed for Database 'TEST'.  (Microsoft.SqlServer.Smo)

For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.4035.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Database&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)

------------------------------

CREATE DATABASE failed. Some file names listed could not be created. Check
related errors.
CREATE FILE encountered operating system error 5(error not found) while
attempting to open or create the physical file
'C:\SQLDATABASES\DATA\TEST.mdf'. (Microsoft SQL Server, Error: 1802)

For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.4035&EvtSrc=MSSQLServer&EvtID=1802&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------
***
SQL EVENT LOG MESSAGE

2009-09-01 11:12:33.40 spid53      CREATE FILE encountered operating system
error 5(error not found) while attempting to open or create the physical file
'C:\SQLDATABASES\LOGS\TEST_log.ldf'.
2009-09-01 11:12:33.40 spid53      Error: 5123, Severity: 16, State: 1.
2009-09-01 11:12:33.40 spid53      CREATE FILE encountered operating system
error 5(error not found) while attempting to open or create the physical file
'C:\SQLDATABASES\DATA\TEST.mdf'.
***

Now as as far as I am aware Sql server 2005 natively supports mount points.

Yet this issue is specific to mount points being used.

The way I tested is by creating a different file folder and creating 2 file
folders in it called DATA and LOGS.

I then in Sql config changed the data and logs path to this directory and
files.

Upon creating a new database everything works flawlessly and the database
gets created without any errors.

I am not a Sql DBA. So any help will be appreciated.

I currently have a Server 2003r2 standard x64 machine running sql 2000
standard with a very similar setup and it works without any issues.

--
General System Admin and IT manager

01-Sep-09 11:01 AM

One thing that I can think of is the permission on the mount points. You
might want to check and see if the account which SQL Server Service is
running has the permission to write on the mount points.

01-Sep-09 11:28 AM

Thanks for the reply .I went as far as to give the EVERYONE group full
control .

And nothing.

I have to try and get this sorted by tommorow . Should I phone MS support?

I cannot think what else to try?
--
General System Admin and IT manager

01-Sep-09 11:57 AM

What happen if you try to create/copy any file to the mount point volumes?

As for calling MS support, I highly recommend it. They cost money (although
if you have certain MSDN subscription, you might be entitle to some support
calls as part of the subscription). They are highly knowledgable and also
pretty quick to response.

Thank you
Lucas

04-Sep-09 02:34 AM

Ricus (Ricus@) writes:

it is definitely a permissions issue:

2009-09-01 11:12:33.40 spid53      CREATE FILE encountered operating
system error 5(error not found)


Error 5 is "Access is denied".

What service accounts do you use for SQL Server? If you use LocalSystem
or somesuch change to a domain user. Not that it should matter for a
mount point, but LocalSystem is bad practice anyway.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

02-Sep-09 03:40 AM

Please note I gave the EVERYONE group full control and the db did not get
created if it was a permission issue that should have negated it shurely?

Also I used the network service account for the sql services and I gave this
account full access  to the mount points as file permission access and it
still did not work.
--
General System Admin and IT manager

02-Sep-09 06:12 AM

Just to let everyone know it seems that the network service does not get any
file or folder level permission even if you explicitly grant it those.

Once I changed my sal db engine service to local admin the file and folder
permission works.
--
General System Admin and IT manager