error creating DB

• mjschwenger

  SSCarpal Tunnel

  Points: 4019

  More actions

  November 14, 2007 at 10:18 am

  #119392

  I have a problem creatign DB with SQl server 2005 Standard edition, SP2 on Win 2003 server Sp1. I already have a setup with SQl server 2000 and have no problem cratign the DB.

  This is the code I use:

  CREATE DATABASE [perf05] ON

  (NAME = N'perf05_data01', FILENAME = N'C:\mnt\lun1\perf05_data01.mdf' , SIZE = 800, MAXSIZE = 25000, FILEGROWTH = 100%),

  (NAME = N'perf05_data04', FILENAME = N'C:\mnt\lun4\perf05_data04.Ndf' , SIZE = 800, MAXSIZE = 25000, FILEGROWTH = 1000)

  LOG ON (NAME = N'perf05_log01', FILENAME = N'C:\mnt\lun1\perf05_log.LDF' , SIZE = 56, FILEGROWTH = 10%)

  COLLATE SQL_Latin1_General_CP1_CI_AS

  GO

  Msg 1802, Level 16, State 4, Line 1

  CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

  Msg 5123, Level 16, State 1, Line 1

  CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'C:\mnt\lun1\perf05_data01.mdf'.

  Thanks a lot for the help.

  mj

• SQL_ABD

  SSCertifiable

  Points: 5832

  More actions

  November 14, 2007 at 10:37 am

  #751176

  use master

  go

  CREATE DATABASE [perf05] ON PRIMARY

  (NAME = N'perf05_data01', FILENAME = N'C:\perf05_data01.mdf' , SIZE = 800, MAXSIZE = 25000, FILEGROWTH = 100%),

  (NAME = N'perf05_data04', FILENAME = N'C:\perf05_data04.Ndf' , SIZE = 800, MAXSIZE = 25000, FILEGROWTH = 1000KB)

  LOG ON (NAME = N'perf05_log01', FILENAME = N'C:\perf05_log.LDF' , SIZE = 56, FILEGROWTH = 10%)

  COLLATE SQL_Latin1_General_CP1_CI_AS

  GO

  Works fine

  So you have to create the appropriate directories before C:\mnt\lun1 and C:\mnt\lun4

  Regards

  Ahmed

• mjschwenger

  SSCarpal Tunnel

  Points: 4019

  More actions

  November 14, 2007 at 10:52 am

  #751186

  Thanks a lot for the replay.

  But why SQL 2000 doesn't require that?

  Is this 2005 limitation, or depends on the edition - Standard?

  Thanks a lot, mj

• SQL_ABD

  SSCertifiable

  Points: 5832

  More actions

  November 14, 2007 at 10:54 am

  #751188

  So no idea

  Ahmed

• SQL_ABD

  SSCertifiable

  Points: 5832

  More actions

  November 14, 2007 at 11:00 am

  #751191

  Hi,

  even in SQL Server 2000 enterprise + SP4 you will have the following error:

  Server: Msg 5105, Level 16, State 2, Line 1

  Device activation error. The physical file name 'C:\mnt\lun1\perf05_data01.mdf may be incorrect.

  Server: Msg 1802, Level 16, State 1, Line 1

  CREATE DATABASE failed. Some file names listed could not be created. Check previous errors.

  Regards,

  Ahmed

• mjschwenger

  SSCarpal Tunnel

  Points: 4019

  More actions

  November 14, 2007 at 4:26 pm

  #751302

  It looks that SQL server 2005 is picky - the problem was in the way the luns were setup - as soon as the permission's were updated in Disk Management, everything was OK. This was not needed for SQL 2000 though.

  Thanks a lot for he help.

  mj