Msg 5133, Level 16, State 1, Line 1

I am using SQL Server 2005 64 bit SP2 on a cluster with SAN. I am getting
the error in subject when I try to create a database using this statement:
CREATE DATABASE [ESBMessageTracking] ON PRIMARY
( NAME = 'ESBMsgTrack_Data', FILENAME = 'F:\Microsoft SQL
Server\MSSQL.1\Data\ESBMessageTracking\ESBMessageTracking_Data.MDF' , SIZE = 33536KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
LOG ON
( NAME = 'ESBMsgTrack_Log', FILENAME = 'E:\Microsoft SQL
Server\MSSQL.1\MSSQL\TLogs\ESBMessageTracking\ESBMessageTracking_Log.LDF' ,
SIZE = 218752KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO
I have confirmed the path is correct. Don't know what is going on. Help
please."SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
news:F5366135-EE7C-4C97-A782-C9A088E0FD96@.microsoft.com...
>I am using SQL Server 2005 64 bit SP2 on a cluster with SAN. I am getting
> the error in subject when I try to create a database using this statement:
> CREATE DATABASE [ESBMessageTracking] ON PRIMARY
> ( NAME = 'ESBMsgTrack_Data', FILENAME = 'F:\Microsoft SQL
> Server\MSSQL.1\Data\ESBMessageTracking\ESBMessageTracking_Data.MDF' , SIZE
> => 33536KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
> LOG ON
> ( NAME = 'ESBMsgTrack_Log', FILENAME = 'E:\Microsoft SQL
> Server\MSSQL.1\MSSQL\TLogs\ESBMessageTracking\ESBMessageTracking_Log.LDF'
> ,
> SIZE = 218752KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
> COLLATE SQL_Latin1_General_CP1_CI_AS
> GO
> I have confirmed the path is correct. Don't know what is going on. Help
> please.
Did you confirm both PATH*S* are correct? Your MDF is on F: but your LDF is
on E: Next time, it would help if you included the entire error message
so - most people have not memorized the error numbers.|||Scott,
Thanks. I Did confirm both paths exist. Here's the error message:
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "F:\Microsoft SQL
Server\MSSQL.1\Data\ESBMessageTracking\ESBMessageTracking_Data.MDF" failed
with the operating system error 1008(An attempt was made to reference a token
that does not exist.).
Msg 1802, Level 16, State 1, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check
related errors.
What is interesting is that when I use xp_cmdshell 'dir f:\microsoft sql
server' it does not find the path even though it exists. It is like it
connot deal with the embedded blank.
"Scott Morris" wrote:
> "SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
> news:F5366135-EE7C-4C97-A782-C9A088E0FD96@.microsoft.com...
> >I am using SQL Server 2005 64 bit SP2 on a cluster with SAN. I am getting
> > the error in subject when I try to create a database using this statement:
> > CREATE DATABASE [ESBMessageTracking] ON PRIMARY
> > ( NAME = 'ESBMsgTrack_Data', FILENAME = 'F:\Microsoft SQL
> > Server\MSSQL.1\Data\ESBMessageTracking\ESBMessageTracking_Data.MDF' , SIZE
> > => > 33536KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
> > LOG ON
> > ( NAME = 'ESBMsgTrack_Log', FILENAME = 'E:\Microsoft SQL
> > Server\MSSQL.1\MSSQL\TLogs\ESBMessageTracking\ESBMessageTracking_Log.LDF'
> > ,
> > SIZE = 218752KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
> > COLLATE SQL_Latin1_General_CP1_CI_AS
> > GO
> >
> > I have confirmed the path is correct. Don't know what is going on. Help
> > please.
> Did you confirm both PATH*S* are correct? Your MDF is on F: but your LDF is
> on E: Next time, it would help if you included the entire error message
> so - most people have not memorized the error numbers.
>
>|||"SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
news:EC0C75CA-DF07-42D6-A414-565C1B1EBF79@.microsoft.com...
> Scott,
> Thanks. I Did confirm both paths exist. Here's the error message:
> Msg 5133, Level 16, State 1, Line 1
> Directory lookup for the file "F:\Microsoft SQL
> Server\MSSQL.1\Data\ESBMessageTracking\ESBMessageTracking_Data.MDF" failed
> with the operating system error 1008(An attempt was made to reference a
> token
> that does not exist.).
> Msg 1802, Level 16, State 1, Line 1
> CREATE DATABASE failed. Some file names listed could not be created. Check
> related errors.
> What is interesting is that when I use xp_cmdshell 'dir f:\microsoft sql
> server' it does not find the path even though it exists. It is like it
> connot deal with the embedded blank.
The embedded blank shouldn't be a problem - the log file has one as well.
Apparently you have the exact same path on two different disks, using one
for data and one for logs. A bit strange that these paths appear to be sql
server installation paths - but what do I know.
First, things like this generally mean that there is a security or account
issue. Just because you personally have access to an E or F drive doesn't
mean that sql server and the accounts it uses internally do. Second,
clusters have their own complicated setup - something beyond my experience.
BTW - it appears that you posted this question last month in the cluster NG.
Is this a follow-on? Have you done the same things Geoff recommended?
http://groups.google.com/group/microsoft.public.sqlserver.clustering/browse_thread/thread/63ea092f04d74333/e447afb0ba5e592b?lnk=st&q=&rnum=1&hl=en#e447afb0ba5e592b|||I agree with Scott, in your SQL cluster group, both E:\ and F:\ disks must
have a cluster resource in the same group and SQL Server cluster resource
must depend on both drives in order to use them.
Ayad Shammout
"Scott Morris" <bogus@.bogus.com> wrote in message
news:OCN$M014HHA.600@.TK2MSFTNGP05.phx.gbl...
> "SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
> news:EC0C75CA-DF07-42D6-A414-565C1B1EBF79@.microsoft.com...
>> Scott,
>> Thanks. I Did confirm both paths exist. Here's the error message:
>> Msg 5133, Level 16, State 1, Line 1
>> Directory lookup for the file "F:\Microsoft SQL
>> Server\MSSQL.1\Data\ESBMessageTracking\ESBMessageTracking_Data.MDF"
>> failed
>> with the operating system error 1008(An attempt was made to reference a
>> token
>> that does not exist.).
>> Msg 1802, Level 16, State 1, Line 1
>> CREATE DATABASE failed. Some file names listed could not be created.
>> Check
>> related errors.
>> What is interesting is that when I use xp_cmdshell 'dir f:\microsoft sql
>> server' it does not find the path even though it exists. It is like it
>> connot deal with the embedded blank.
> The embedded blank shouldn't be a problem - the log file has one as well.
> Apparently you have the exact same path on two different disks, using one
> for data and one for logs. A bit strange that these paths appear to be
> sql server installation paths - but what do I know.
> First, things like this generally mean that there is a security or account
> issue. Just because you personally have access to an E or F drive doesn't
> mean that sql server and the accounts it uses internally do. Second,
> clusters have their own complicated setup - something beyond my
> experience. BTW - it appears that you posted this question last month in
> the cluster NG. Is this a follow-on? Have you done the same things Geoff
> recommended?
> http://groups.google.com/group/microsoft.public.sqlserver.clustering/browse_thread/thread/63ea092f04d74333/e447afb0ba5e592b?lnk=st&q=&rnum=1&hl=en#e447afb0ba5e592b
>

MSDNs Estimating the Size of a Table

I was hoping I can get some help regarding subject mater located at
http://msdn.microsoft.com/library/d...des_02_92k3.asp

MyTable has 23 fields with 100,000 records.
Field1 nvarchar 90
Field2 char 6
Field3 varchar 8000
Field4 nvarchar 200
Field5 nvarchar 200
Field6 nvarchar 200
Field7 nvarchar 200
Field8 nvarchar 200
Field9 char 30
Field10 char 30
Field11 nvarchar 200
Field12 nvarchar 200
Field13 float 8
Field14 datetime 8
Field15 datetime 8
Field16 datetime 8
Field17 nvarchar 200
Field18 nvarchar 200
Field19 varchar 8000
Field20 nvarchar 200
Field21 nvarchar 200
Field22 nvarchar 200
Field23 varchar 8000

Data Types:
nvarchar = Variable-length, Storage is 2 times the # of characters entered
char = Fixed-length. Storage is n bytes
varchar = Variable-length, Storage is actual length in bytes of data entered
float = Is float Fixed-length?, my precisions are 15 digits so 8 bytes.
datetime = Is datetime Fixed-length?, Storage is 8 bytes

I need the following blanks filled in for me, please :-)

Num_Rows 100,000
Num_Cols 23
Fixed_Data_Size
Num_Variable_Cols 16
Max_Var_Size
Null_Bitmap Int(2 + ((23 + 7) / 8)) = 5?
Variable_Data_Size
Row_Size
Rows_Per_Page
Free_Rows_Per_Page 100 (no clustered index created)
Num_Pages
Table Size"TZone" <TZone@.optus.com.au> wrote in message
news:3f2e4086$0$14563$afc38c87@.news.optusnet.com.a u...
> I was hoping I can get some help regarding subject mater located at
http://msdn.microsoft.com/library/d...des_02_92k3.asp

<snip
You're correct that float and datetime are fixed length, and both are indeed
8 bytes (for your float precision, as you noted), which seems to be your
main question. You just need to follow the formulas in the documentation.

One point you may need to consider is how full the average variable length
column will be - if you know that almost all data in an nvarchar(100) column
will be around 50 Unicode characters, ie. half the length of the field, then
you could count that as 100 bytes instead of 200 to get a more accurate
estimate. Or if the average will be around 75, then count 150 etc. But
you're the only person who knows what your data looks like, so you're the
best person to calculate the size of the table.

Simon|||After following formula in documentation I arrive at:

Num_Rows = 100,000
Num_Cols = 23
Fixed_Data_Size = 98 bytes
Num_Variable_Cols = 16 fields
Max_Var_Size = 8000 bytes ** Field3 is varchar with length 8000, so is this
8000 bytes?***
Null_Bitmap = 5
Variable_Data_Size = 8034
Row_Size = 8141
Rows_Per_Page = (INT(8096/(Row_Size + 2)) ** is zero correct?**
Free_Rows_Per_Page = 100 (I have no clustered index)
Num_Pages = -1000 ** is this correct, a negative Num_Pages?**
Total Size (Mb) = -7.81

MyTable has 23 fields with 100,000 records.
Field1 nvarchar 90 * variable*
Field2 char 6 *fixed*
Field3 varchar 8000 *variable*
Field4 nvarchar 200 *variable*
Field5 nvarchar 200 *variable*
Field6 nvarchar 200 *variable*
Field7 nvarchar 200 *variable*
Field8 nvarchar 200 *variable*
Field9 char 30 *fixed*
Field10 char 30 *fixed*
Field11 nvarchar 200 *variable*
Field12 nvarchar 200 *variable*
Field13 float 8 *fixed*
Field14 datetime 8 *fixed*
Field15 datetime 8 *fixed*
Field16 datetime 8 *fixed*
Field17 nvarchar 200 *variable*
Field18 nvarchar 200 *variable*
Field19 varchar 8000 *variable*
Field20 nvarchar 200 *variable*
Field21 nvarchar 200 *variable*
Field22 nvarchar 200 *variable*
Field23 varchar 8000 *variable*

I'm going to assume all variable fields are close to maximum capacity.

Thanks for previous reply Simon!

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:3f2ec4e7$1_1@.news.bluewin.ch...
> "TZone" <TZone@.optus.com.au> wrote in message
> news:3f2e4086$0$14563$afc38c87@.news.optusnet.com.a u...
> > I was hoping I can get some help regarding subject mater located at
http://msdn.microsoft.com/library/d...des_02_92k3.asp
> <snip>
> You're correct that float and datetime are fixed length, and both are
indeed
> 8 bytes (for your float precision, as you noted), which seems to be your
> main question. You just need to follow the formulas in the documentation.
> One point you may need to consider is how full the average variable length
> column will be - if you know that almost all data in an nvarchar(100)
column
> will be around 50 Unicode characters, ie. half the length of the field,
then
> you could count that as 100 bytes instead of 200 to get a more accurate
> estimate. Or if the average will be around 75, then count 150 etc. But
> you're the only person who knows what your data looks like, so you're the
> best person to calculate the size of the table.
> Simon