I need help trying to figure out an sql error message?

1

I am trying figure out what i am doing wrong?

Msg 153, Level 15, State 1, Line 7
Invalid usage of the option SIZE in the CREATE/ALTER DATABASE statement.

USE MASTER
GO
CREATE DATABASE Movies_2
ON
(NAME = Movie_dat,
FILENAME = 'C:\IT234_Data\movie.mdf'
SIZE= 50,
MAXSIZE = 100,
FILEGROWTH = 5 )
LOG ON
NAME = Movie_log,
FILENAME = 'C:\IT234_Log\movie.ldf'
SIZE = 5MB
MAXSIZE = 25MB
FILEGROWTH = 5MB ) ;
GO

2

If this is a copy where is the comma after the file name?

3

USE MASTER
GO
CREATE DATABASE Movies_2
ON
(NAME = Movie_dat,
FILENAME, = 'C:\IT234_Data\movie.mdf'
SIZE= 50,
MAXSIZE = 100,
FILEGROWTH = 5 )
LOG ON
NAME = Movie_log,
FILENAME = 'C:\IT234_Log\movie.ldf'
SIZE = 5MB
MAXSIZE = 25MB
FILEGROWTH = 5MB ) ;
GO
Msg 153, Level 15, State 1, Line 6
Invalid usage of the option FILENAME in the CREATE/ALTER DATABASE statement.

4

What @djj55 meant was the comma after the actual name, not the keyword "FILENAME". Here is the corrected version with appropriate commas etc.

USE MASTER
GO
CREATE DATABASE Movies_2
ON
(NAME = Movie_dat,
FILENAME = 'C:\IT234_Data\movie.mdf',
SIZE= 50, 
MAXSIZE = 100,
FILEGROWTH = 5 )
LOG ON
(NAME = Movie_log,
FILENAME = 'C:\IT234_Log\movie.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB ) ;
GO

I don't know if the values you are providing are appropriate or not, or whether there are other errors in there. The only thing I did was to make sure that the script parses correctly. That does not mean it will run correctly.

What I would suggest is the following:
Open up SQL Management Studio, connect to your server, and in object explorer, right click database node and select "New Database". It gives you a dialog where you can set all the options for your new database. After setting all the options, don't click OK button, instead click the Script button at the top left of the right panel. This will generate the script for you, which when executed will create the database. You can the modify this script to your liking if it is not exactly what you want. This way you will be sure that you are using the correct syntax, without any missing commas etc.

1 Like
5

Thank you very much for your help. I place the comma in the correct place, and the error message is no longer there once executed.