Hi...
I was in the process of synchronzing the indexes in two seperate databases;
production and development. I ran into a problem with one table.
The current production index looks like this:
CREATE NONCLUSTERED INDEX [INX_Table1] ON
[dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field3], [Field4], [Field5],
[Field6], [Field7])
The current development database index looks like this
CREATE NONCLUSTERED INDEX [INX_Table1] ON
[dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field4], [Field3], [Field5],
[Field6], [Field7])
I need the make changes on the production database so it uses the same
structure as the development database, so I delete the index on the
production database (successful).
I then execute the development index command on the production server.
What is hard to believe is I get the same structure as was there BEFORE. No
matter what I do, (including change the index name), the field
order in the "INCLUDE" portion never changes.
Has anyone seen this before? I realize that the order of the fields in the
"include" area isn't important, but the synchronization software reports
differences (which is a real PITA).
Thanks,
ForchHi
"Forch" wrote:
> Hi...
> I was in the process of synchronzing the indexes in two seperate databases;
> production and development. I ran into a problem with one table.
> The current production index looks like this:
> CREATE NONCLUSTERED INDEX [INX_Table1] ON
> [dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field3], [Field4], [Field5],
> [Field6], [Field7])
> The current development database index looks like this
> CREATE NONCLUSTERED INDEX [INX_Table1] ON
> [dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field4], [Field3], [Field5],
> [Field6], [Field7])
> I need the make changes on the production database so it uses the same
> structure as the development database, so I delete the index on the
> production database (successful).
> I then execute the development index command on the production server.
> What is hard to believe is I get the same structure as was there BEFORE. No
> matter what I do, (including change the index name), the field
> order in the "INCLUDE" portion never changes.
> Has anyone seen this before? I realize that the order of the fields in the
> "include" area isn't important, but the synchronization software reports
> differences (which is a real PITA).
> Thanks,
> Forch
>
I thought this may be that on the develop machine the column [Field3] had
been dropped and re-created at some point, although my tests on a SP2 machine
have not replicated this!
John|||Are you sure that Field3 and Field4 have the same data definition in
both databases?
Are you sure the clustered index has the same definition in both
databases?
Are you sure Field3 comes before Field4 in the table definition in both
databases?
If you have table modification (such as changing a column's definition)
you could try to reindex the clustered index and recreate the
nonclustered index.
HTH,
Gert-Jan
Forch wrote:
> Hi...
> I was in the process of synchronzing the indexes in two seperate databases;
> production and development. I ran into a problem with one table.
> The current production index looks like this:
> CREATE NONCLUSTERED INDEX [INX_Table1] ON
> [dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field3], [Field4], [Field5],
> [Field6], [Field7])
> The current development database index looks like this
> CREATE NONCLUSTERED INDEX [INX_Table1] ON
> [dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field4], [Field3], [Field5],
> [Field6], [Field7])
> I need the make changes on the production database so it uses the same
> structure as the development database, so I delete the index on the
> production database (successful).
> I then execute the development index command on the production server.
> What is hard to believe is I get the same structure as was there BEFORE. No
> matter what I do, (including change the index name), the field
> order in the "INCLUDE" portion never changes.
> Has anyone seen this before? I realize that the order of the fields in the
> "include" area isn't important, but the synchronization software reports
> differences (which is a real PITA).
> Thanks,
> Forch
Showing posts with label index. Show all posts
Showing posts with label index. Show all posts
Tuesday, March 20, 2012
Can not change index INCLUDE fields order....
Hi...
I was in the process of synchronzing the indexes in two seperate databases;
production and development. I ran into a problem with one table.
The current production index looks like this:
CREATE NONCLUSTERED INDEX [INX_Table1] ON
[dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field3], &
#91;Field4], [Field5],
[Field6], [Field7])
The current development database index looks like this
CREATE NONCLUSTERED INDEX [INX_Table1] ON
[dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field4], &
#91;Field3], [Field5],
[Field6], [Field7])
I need the make changes on the production database so it uses the same
structure as the development database, so I delete the index on the
production database (successful).
I then execute the development index command on the production server.
What is hard to believe is I get the same structure as was there BEFORE. No
matter what I do, (including change the index name), the field
order in the "INCLUDE" portion never changes.
Has anyone seen this before? I realize that the order of the fields in the
"include" area isn't important, but the synchronization software reports
differences (which is a real PITA).
Thanks,
ForchHi
"Forch" wrote:
> Hi...
> I was in the process of synchronzing the indexes in two seperate databases
;
> production and development. I ran into a problem with one table.
> The current production index looks like this:
> CREATE NONCLUSTERED INDEX [INX_Table1] ON
> [dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field3],
[Field4], [Field5],
> [Field6], [Field7])
> The current development database index looks like this
> CREATE NONCLUSTERED INDEX [INX_Table1] ON
> [dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field4],
[Field3], [Field5],
> [Field6], [Field7])
> I need the make changes on the production database so it uses the same
> structure as the development database, so I delete the index on the
> production database (successful).
> I then execute the development index command on the production server.
> What is hard to believe is I get the same structure as was there BEFORE.
No
> matter what I do, (including change the index name), the field
> order in the "INCLUDE" portion never changes.
> Has anyone seen this before? I realize that the order of the fields in th
e
> "include" area isn't important, but the synchronization software reports
> differences (which is a real PITA).
> Thanks,
> Forch
>
I thought this may be that on the develop machine the column [Field3] ha
d
been dropped and re-created at some point, although my tests on a SP2 machin
e
have not replicated this!
John|||Are you sure that Field3 and Field4 have the same data definition in
both databases?
Are you sure the clustered index has the same definition in both
databases?
Are you sure Field3 comes before Field4 in the table definition in both
databases?
If you have table modification (such as changing a column's definition)
you could try to reindex the clustered index and recreate the
nonclustered index.
HTH,
Gert-Jan
Forch wrote:
> Hi...
> I was in the process of synchronzing the indexes in two seperate databases
;
> production and development. I ran into a problem with one table.
> The current production index looks like this:
> CREATE NONCLUSTERED INDEX [INX_Table1] ON
> [dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field3],
[Field4], [Field5],
> [Field6], [Field7])
> The current development database index looks like this
> CREATE NONCLUSTERED INDEX [INX_Table1] ON
> [dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field4],
[Field3], [Field5],
> [Field6], [Field7])
> I need the make changes on the production database so it uses the same
> structure as the development database, so I delete the index on the
> production database (successful).
> I then execute the development index command on the production server.
> What is hard to believe is I get the same structure as was there BEFORE.
No
> matter what I do, (including change the index name), the field
> order in the "INCLUDE" portion never changes.
> Has anyone seen this before? I realize that the order of the fields in th
e
> "include" area isn't important, but the synchronization software reports
> differences (which is a real PITA).
> Thanks,
> Forch
I was in the process of synchronzing the indexes in two seperate databases;
production and development. I ran into a problem with one table.
The current production index looks like this:
CREATE NONCLUSTERED INDEX [INX_Table1] ON
[dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field3], &
#91;Field4], [Field5],
[Field6], [Field7])
The current development database index looks like this
CREATE NONCLUSTERED INDEX [INX_Table1] ON
[dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field4], &
#91;Field3], [Field5],
[Field6], [Field7])
I need the make changes on the production database so it uses the same
structure as the development database, so I delete the index on the
production database (successful).
I then execute the development index command on the production server.
What is hard to believe is I get the same structure as was there BEFORE. No
matter what I do, (including change the index name), the field
order in the "INCLUDE" portion never changes.
Has anyone seen this before? I realize that the order of the fields in the
"include" area isn't important, but the synchronization software reports
differences (which is a real PITA).
Thanks,
ForchHi
"Forch" wrote:
> Hi...
> I was in the process of synchronzing the indexes in two seperate databases
;
> production and development. I ran into a problem with one table.
> The current production index looks like this:
> CREATE NONCLUSTERED INDEX [INX_Table1] ON
> [dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field3],
[Field4], [Field5],
> [Field6], [Field7])
> The current development database index looks like this
> CREATE NONCLUSTERED INDEX [INX_Table1] ON
> [dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field4],
[Field3], [Field5],
> [Field6], [Field7])
> I need the make changes on the production database so it uses the same
> structure as the development database, so I delete the index on the
> production database (successful).
> I then execute the development index command on the production server.
> What is hard to believe is I get the same structure as was there BEFORE.
No
> matter what I do, (including change the index name), the field
> order in the "INCLUDE" portion never changes.
> Has anyone seen this before? I realize that the order of the fields in th
e
> "include" area isn't important, but the synchronization software reports
> differences (which is a real PITA).
> Thanks,
> Forch
>
I thought this may be that on the develop machine the column [Field3] ha
d
been dropped and re-created at some point, although my tests on a SP2 machin
e
have not replicated this!
John|||Are you sure that Field3 and Field4 have the same data definition in
both databases?
Are you sure the clustered index has the same definition in both
databases?
Are you sure Field3 comes before Field4 in the table definition in both
databases?
If you have table modification (such as changing a column's definition)
you could try to reindex the clustered index and recreate the
nonclustered index.
HTH,
Gert-Jan
Forch wrote:
> Hi...
> I was in the process of synchronzing the indexes in two seperate databases
;
> production and development. I ran into a problem with one table.
> The current production index looks like this:
> CREATE NONCLUSTERED INDEX [INX_Table1] ON
> [dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field3],
[Field4], [Field5],
> [Field6], [Field7])
> The current development database index looks like this
> CREATE NONCLUSTERED INDEX [INX_Table1] ON
> [dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field4],
[Field3], [Field5],
> [Field6], [Field7])
> I need the make changes on the production database so it uses the same
> structure as the development database, so I delete the index on the
> production database (successful).
> I then execute the development index command on the production server.
> What is hard to believe is I get the same structure as was there BEFORE.
No
> matter what I do, (including change the index name), the field
> order in the "INCLUDE" portion never changes.
> Has anyone seen this before? I realize that the order of the fields in th
e
> "include" area isn't important, but the synchronization software reports
> differences (which is a real PITA).
> Thanks,
> Forch
Can not change index INCLUDE fields order....
Hi...
I was in the process of synchronzing the indexes in two seperate databases;
production and development. I ran into a problem with one table.
The current production index looks like this:
CREATE NONCLUSTERED INDEX [INX_Table1] ON
[dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field3], [Field4], [Field5],
[Field6], [Field7])
The current development database index looks like this
CREATE NONCLUSTERED INDEX [INX_Table1] ON
[dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field4], [Field3], [Field5],
[Field6], [Field7])
I need the make changes on the production database so it uses the same
structure as the development database, so I delete the index on the
production database (successful).
I then execute the development index command on the production server.
What is hard to believe is I get the same structure as was there BEFORE. No
matter what I do, (including change the index name), the field
order in the "INCLUDE" portion never changes.
Has anyone seen this before? I realize that the order of the fields in the
"include" area isn't important, but the synchronization software reports
differences (which is a real PITA).
Thanks,
Forch
Hi
"Forch" wrote:
> Hi...
> I was in the process of synchronzing the indexes in two seperate databases;
> production and development. I ran into a problem with one table.
> The current production index looks like this:
> CREATE NONCLUSTERED INDEX [INX_Table1] ON
> [dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field3], [Field4], [Field5],
> [Field6], [Field7])
> The current development database index looks like this
> CREATE NONCLUSTERED INDEX [INX_Table1] ON
> [dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field4], [Field3], [Field5],
> [Field6], [Field7])
> I need the make changes on the production database so it uses the same
> structure as the development database, so I delete the index on the
> production database (successful).
> I then execute the development index command on the production server.
> What is hard to believe is I get the same structure as was there BEFORE. No
> matter what I do, (including change the index name), the field
> order in the "INCLUDE" portion never changes.
> Has anyone seen this before? I realize that the order of the fields in the
> "include" area isn't important, but the synchronization software reports
> differences (which is a real PITA).
> Thanks,
> Forch
>
I thought this may be that on the develop machine the column [Field3] had
been dropped and re-created at some point, although my tests on a SP2 machine
have not replicated this!
John
I was in the process of synchronzing the indexes in two seperate databases;
production and development. I ran into a problem with one table.
The current production index looks like this:
CREATE NONCLUSTERED INDEX [INX_Table1] ON
[dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field3], [Field4], [Field5],
[Field6], [Field7])
The current development database index looks like this
CREATE NONCLUSTERED INDEX [INX_Table1] ON
[dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field4], [Field3], [Field5],
[Field6], [Field7])
I need the make changes on the production database so it uses the same
structure as the development database, so I delete the index on the
production database (successful).
I then execute the development index command on the production server.
What is hard to believe is I get the same structure as was there BEFORE. No
matter what I do, (including change the index name), the field
order in the "INCLUDE" portion never changes.
Has anyone seen this before? I realize that the order of the fields in the
"include" area isn't important, but the synchronization software reports
differences (which is a real PITA).
Thanks,
Forch
Hi
"Forch" wrote:
> Hi...
> I was in the process of synchronzing the indexes in two seperate databases;
> production and development. I ran into a problem with one table.
> The current production index looks like this:
> CREATE NONCLUSTERED INDEX [INX_Table1] ON
> [dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field3], [Field4], [Field5],
> [Field6], [Field7])
> The current development database index looks like this
> CREATE NONCLUSTERED INDEX [INX_Table1] ON
> [dbo].[Table1] ([Field1], [Field2]) INCLUDE ([Field4], [Field3], [Field5],
> [Field6], [Field7])
> I need the make changes on the production database so it uses the same
> structure as the development database, so I delete the index on the
> production database (successful).
> I then execute the development index command on the production server.
> What is hard to believe is I get the same structure as was there BEFORE. No
> matter what I do, (including change the index name), the field
> order in the "INCLUDE" portion never changes.
> Has anyone seen this before? I realize that the order of the fields in the
> "include" area isn't important, but the synchronization software reports
> differences (which is a real PITA).
> Thanks,
> Forch
>
I thought this may be that on the develop machine the column [Field3] had
been dropped and re-created at some point, although my tests on a SP2 machine
have not replicated this!
John
Can not allocate space for rebuilding index
I got the error msg below while rebuilding index for a large table. I have
allocated plenty of space for the primary filegroup and "auto" grow by 10% i
s
set. There are plenty of space in the drive. Please help me to resolve thi
s
isse.
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft]&#
91;ODBC SQL
Server Driver][SQL Server]Could not allocate space for object '(SYSTEM t
able
id: -739371491)' in database 'FDIT' because the 'PRIMARY' filegroup is full.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has
been
terminated.Hi
Rather grow the DB to the correct size before you do such an operation.
Yes, autogrow may be on, but autogrowing a e.g. 20Gb DB by 10% does take a
while. During this time, the DBCC command will find the DB full and abort.
The DBCC command does not know that more space will be available shortly.
Regards
Mike
"Bill Little" wrote:
> I got the error msg below while rebuilding index for a large table. I hav
e
> allocated plenty of space for the primary filegroup and "auto" grow by 10%
is
> set. There are plenty of space in the drive. Please help me to resolve t
his
> isse.
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft]
[ODBC SQL
> Server Driver][SQL Server]Could not allocate space for object '(SYSTEM
table
> id: -739371491)' in database 'FDIT' because the 'PRIMARY' filegroup is ful
l.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement h
as been
> terminated.
>|||Thank you very much. I have set the space for the primary filegroup the sam
e
size as the size of the whole database. Do I still need to increase the siz
e
or the size of the log file?
Regards
Bill
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> Rather grow the DB to the correct size before you do such an operation.
> Yes, autogrow may be on, but autogrowing a e.g. 20Gb DB by 10% does take a
> while. During this time, the DBCC command will find the DB full and abort.
> The DBCC command does not know that more space will be available shortly.
> Regards
> Mike
> "Bill Little" wrote:
>|||> I have set the space for the primary filegroup the same
> size as the size of the whole database.
Whether that is enough or not is impossible to answer without more informati
on. If the table has a
clustered index, rebuilding is essentially copying the table to a new locati
on and after the copy
removing the old table. I.e., you need as much free space as the size of the
table (plus some ore)
on the file group where the table resides.
> Do I still need to increase the size
> or the size of the log file?
Rebuilding is a type of modification. If you are in FULL recovery mode, the
amount of data moves (as
explained above) will be logged to the transaction log.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
news:71BB84AB-C440-480F-BD09-090A7E18C13B@.microsoft.com...[vbcol=seagreen]
> Thank you very much. I have set the space for the primary filegroup the s
ame
> size as the size of the whole database. Do I still need to increase the s
ize
> or the size of the log file?
> Regards
> Bill
> "Mike Epprecht (SQL MVP)" wrote:
>|||Thanks. I tried to add more space to the database but I got the error msg
below. What shall we do to resolve the issue?
Server: Msg 1827, Level 16, State 2, Line 1
CREATE/ALTER DATABASE failed because the resulting cumulative database size
would exceed your licensed limit of 2048 MB per database.
Extending database by 1024.00 MB on disk 'Primary'.
"Tibor Karaszi" wrote:
> Whether that is enough or not is impossible to answer without more informa
tion. If the table has a
> clustered index, rebuilding is essentially copying the table to a new loca
tion and after the copy
> removing the old table. I.e., you need as much free space as the size of t
he table (plus some ore)
> on the file group where the table resides.
>
> Rebuilding is a type of modification. If you are in FULL recovery mode, th
e amount of data moves (as
> explained above) will be logged to the transaction log.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
> news:71BB84AB-C440-480F-BD09-090A7E18C13B@.microsoft.com...
>
>|||Seems you are running MSDE, which has a max database size of 2GB data per da
tabase. You might want
to use DBCC INDEXDEFRAG in stead of DBCC DBREINDEX:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
news:9CC68E6E-1380-47A8-969C-DF9AE204C3F3@.microsoft.com...[vbcol=seagreen]
> Thanks. I tried to add more space to the database but I got the error msg
> below. What shall we do to resolve the issue?
> Server: Msg 1827, Level 16, State 2, Line 1
> CREATE/ALTER DATABASE failed because the resulting cumulative database siz
e
> would exceed your licensed limit of 2048 MB per database.
> Extending database by 1024.00 MB on disk 'Primary'.
>
> "Tibor Karaszi" wrote:
>|||Thank you very much. We are using standard edition. So we cannot rebuild
index with this lisence limit condition, right? Any workaround or
suggestions. We have clustered indexes so I still want to use rebuild index
function.
Regards
Bill
"Tibor Karaszi" wrote:
> Seems you are running MSDE, which has a max database size of 2GB data per
database. You might want
> to use DBCC INDEXDEFRAG in stead of DBCC DBREINDEX:
> http://www.microsoft.com/technet/pr...ver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
> news:9CC68E6E-1380-47A8-969C-DF9AE204C3F3@.microsoft.com...
>
>|||You wouldn't get this error if you are on standard edition. Are you sure you
work against the right
instance of SQL Server. Can you check the licensing?
SELECT @.@.VERSION
Also, INDEXDEFRAG work just fine on nc indexes...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
news:DFF76B92-66DD-47F9-9D63-EADEFFFFE9B2@.microsoft.com...[vbcol=seagreen]
> Thank you very much. We are using standard edition. So we cannot rebuild
> index with this lisence limit condition, right? Any workaround or
> suggestions. We have clustered indexes so I still want to use rebuild ind
ex
> function.
> Regards
> Bill
> "Tibor Karaszi" wrote:
>|||We have having the same exact problem. There is 30+ GB Free on the Drive
(Dynamic Disk, by the way). Database is set to unrestricted file growth.
Everything looks fine with the configuration of the sql database...however,
the database thinks there is no free space on the drive. Very wierd!!
Database is just shy of 5gb, but General Tab says 0.00Mb Free. Looks like
ill be calling MS soon!!
Dale
txskibum@.hotmail.com
"Tibor Karaszi" wrote:
> You wouldn't get this error if you are on standard edition. Are you sure y
ou work against the right
> instance of SQL Server. Can you check the licensing?
> SELECT @.@.VERSION
> Also, INDEXDEFRAG work just fine on nc indexes...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
> news:DFF76B92-66DD-47F9-9D63-EADEFFFFE9B2@.microsoft.com...
>
>|||> We have having the same exact problem.
Same as which problem? The thread has several issues discussed. Anyhow, you
don't seem to get the
"MSDE" error message, as MSDE has max size of 2GB for data storage per datab
ase.
Are you getting error messages or are you only worried that EM say 0MB free?
Remember that EM
reports free space *inside* the database files, without regard to free space
on disk SQL Server
pre-allocates storage.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Dale" <Dale@.discussions.microsoft.com> wrote in message
news:922A620C-5AEE-4DD6-93BD-36645F72B881@.microsoft.com...[vbcol=seagreen]
> We have having the same exact problem. There is 30+ GB Free on the Drive
> (Dynamic Disk, by the way). Database is set to unrestricted file growth.
> Everything looks fine with the configuration of the sql database...however
,
> the database thinks there is no free space on the drive. Very wierd!!
> Database is just shy of 5gb, but General Tab says 0.00Mb Free. Looks like
> ill be calling MS soon!!
> Dale
> txskibum@.hotmail.com
>
> "Tibor Karaszi" wrote:
>
allocated plenty of space for the primary filegroup and "auto" grow by 10% i
s
set. There are plenty of space in the drive. Please help me to resolve thi
s
isse.
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft]&#
91;ODBC SQL
Server Driver][SQL Server]Could not allocate space for object '(SYSTEM t
able
id: -739371491)' in database 'FDIT' because the 'PRIMARY' filegroup is full.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has
been
terminated.Hi
Rather grow the DB to the correct size before you do such an operation.
Yes, autogrow may be on, but autogrowing a e.g. 20Gb DB by 10% does take a
while. During this time, the DBCC command will find the DB full and abort.
The DBCC command does not know that more space will be available shortly.
Regards
Mike
"Bill Little" wrote:
> I got the error msg below while rebuilding index for a large table. I hav
e
> allocated plenty of space for the primary filegroup and "auto" grow by 10%
is
> set. There are plenty of space in the drive. Please help me to resolve t
his
> isse.
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft]
[ODBC SQL
> Server Driver][SQL Server]Could not allocate space for object '(SYSTEM
table
> id: -739371491)' in database 'FDIT' because the 'PRIMARY' filegroup is ful
l.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement h
as been
> terminated.
>|||Thank you very much. I have set the space for the primary filegroup the sam
e
size as the size of the whole database. Do I still need to increase the siz
e
or the size of the log file?
Regards
Bill
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> Rather grow the DB to the correct size before you do such an operation.
> Yes, autogrow may be on, but autogrowing a e.g. 20Gb DB by 10% does take a
> while. During this time, the DBCC command will find the DB full and abort.
> The DBCC command does not know that more space will be available shortly.
> Regards
> Mike
> "Bill Little" wrote:
>|||> I have set the space for the primary filegroup the same
> size as the size of the whole database.
Whether that is enough or not is impossible to answer without more informati
on. If the table has a
clustered index, rebuilding is essentially copying the table to a new locati
on and after the copy
removing the old table. I.e., you need as much free space as the size of the
table (plus some ore)
on the file group where the table resides.
> Do I still need to increase the size
> or the size of the log file?
Rebuilding is a type of modification. If you are in FULL recovery mode, the
amount of data moves (as
explained above) will be logged to the transaction log.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
news:71BB84AB-C440-480F-BD09-090A7E18C13B@.microsoft.com...[vbcol=seagreen]
> Thank you very much. I have set the space for the primary filegroup the s
ame
> size as the size of the whole database. Do I still need to increase the s
ize
> or the size of the log file?
> Regards
> Bill
> "Mike Epprecht (SQL MVP)" wrote:
>|||Thanks. I tried to add more space to the database but I got the error msg
below. What shall we do to resolve the issue?
Server: Msg 1827, Level 16, State 2, Line 1
CREATE/ALTER DATABASE failed because the resulting cumulative database size
would exceed your licensed limit of 2048 MB per database.
Extending database by 1024.00 MB on disk 'Primary'.
"Tibor Karaszi" wrote:
> Whether that is enough or not is impossible to answer without more informa
tion. If the table has a
> clustered index, rebuilding is essentially copying the table to a new loca
tion and after the copy
> removing the old table. I.e., you need as much free space as the size of t
he table (plus some ore)
> on the file group where the table resides.
>
> Rebuilding is a type of modification. If you are in FULL recovery mode, th
e amount of data moves (as
> explained above) will be logged to the transaction log.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
> news:71BB84AB-C440-480F-BD09-090A7E18C13B@.microsoft.com...
>
>|||Seems you are running MSDE, which has a max database size of 2GB data per da
tabase. You might want
to use DBCC INDEXDEFRAG in stead of DBCC DBREINDEX:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
news:9CC68E6E-1380-47A8-969C-DF9AE204C3F3@.microsoft.com...[vbcol=seagreen]
> Thanks. I tried to add more space to the database but I got the error msg
> below. What shall we do to resolve the issue?
> Server: Msg 1827, Level 16, State 2, Line 1
> CREATE/ALTER DATABASE failed because the resulting cumulative database siz
e
> would exceed your licensed limit of 2048 MB per database.
> Extending database by 1024.00 MB on disk 'Primary'.
>
> "Tibor Karaszi" wrote:
>|||Thank you very much. We are using standard edition. So we cannot rebuild
index with this lisence limit condition, right? Any workaround or
suggestions. We have clustered indexes so I still want to use rebuild index
function.
Regards
Bill
"Tibor Karaszi" wrote:
> Seems you are running MSDE, which has a max database size of 2GB data per
database. You might want
> to use DBCC INDEXDEFRAG in stead of DBCC DBREINDEX:
> http://www.microsoft.com/technet/pr...ver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
> news:9CC68E6E-1380-47A8-969C-DF9AE204C3F3@.microsoft.com...
>
>|||You wouldn't get this error if you are on standard edition. Are you sure you
work against the right
instance of SQL Server. Can you check the licensing?
SELECT @.@.VERSION
Also, INDEXDEFRAG work just fine on nc indexes...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
news:DFF76B92-66DD-47F9-9D63-EADEFFFFE9B2@.microsoft.com...[vbcol=seagreen]
> Thank you very much. We are using standard edition. So we cannot rebuild
> index with this lisence limit condition, right? Any workaround or
> suggestions. We have clustered indexes so I still want to use rebuild ind
ex
> function.
> Regards
> Bill
> "Tibor Karaszi" wrote:
>|||We have having the same exact problem. There is 30+ GB Free on the Drive
(Dynamic Disk, by the way). Database is set to unrestricted file growth.
Everything looks fine with the configuration of the sql database...however,
the database thinks there is no free space on the drive. Very wierd!!
Database is just shy of 5gb, but General Tab says 0.00Mb Free. Looks like
ill be calling MS soon!!
Dale
txskibum@.hotmail.com
"Tibor Karaszi" wrote:
> You wouldn't get this error if you are on standard edition. Are you sure y
ou work against the right
> instance of SQL Server. Can you check the licensing?
> SELECT @.@.VERSION
> Also, INDEXDEFRAG work just fine on nc indexes...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
> news:DFF76B92-66DD-47F9-9D63-EADEFFFFE9B2@.microsoft.com...
>
>|||> We have having the same exact problem.
Same as which problem? The thread has several issues discussed. Anyhow, you
don't seem to get the
"MSDE" error message, as MSDE has max size of 2GB for data storage per datab
ase.
Are you getting error messages or are you only worried that EM say 0MB free?
Remember that EM
reports free space *inside* the database files, without regard to free space
on disk SQL Server
pre-allocates storage.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Dale" <Dale@.discussions.microsoft.com> wrote in message
news:922A620C-5AEE-4DD6-93BD-36645F72B881@.microsoft.com...[vbcol=seagreen]
> We have having the same exact problem. There is 30+ GB Free on the Drive
> (Dynamic Disk, by the way). Database is set to unrestricted file growth.
> Everything looks fine with the configuration of the sql database...however
,
> the database thinks there is no free space on the drive. Very wierd!!
> Database is just shy of 5gb, but General Tab says 0.00Mb Free. Looks like
> ill be calling MS soon!!
> Dale
> txskibum@.hotmail.com
>
> "Tibor Karaszi" wrote:
>
Can not allocate space for rebuilding index
I got the error msg below while rebuilding index for a large table. I have
allocated plenty of space for the primary filegroup and "auto" grow by 10% is
set. There are plenty of space in the drive. Please help me to resolve this
isse.
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC SQL
Server Driver][SQL Server]Could not allocate space for object '(SYSTEM table
id: -739371491)' in database 'FDIT' because the 'PRIMARY' filegroup is full.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
terminated.Hi
Rather grow the DB to the correct size before you do such an operation.
Yes, autogrow may be on, but autogrowing a e.g. 20Gb DB by 10% does take a
while. During this time, the DBCC command will find the DB full and abort.
The DBCC command does not know that more space will be available shortly.
Regards
Mike
"Bill Little" wrote:
> I got the error msg below while rebuilding index for a large table. I have
> allocated plenty of space for the primary filegroup and "auto" grow by 10% is
> set. There are plenty of space in the drive. Please help me to resolve this
> isse.
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC SQL
> Server Driver][SQL Server]Could not allocate space for object '(SYSTEM table
> id: -739371491)' in database 'FDIT' because the 'PRIMARY' filegroup is full.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> terminated.
>|||Thank you very much. I have set the space for the primary filegroup the same
size as the size of the whole database. Do I still need to increase the size
or the size of the log file?
Regards
Bill
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Rather grow the DB to the correct size before you do such an operation.
> Yes, autogrow may be on, but autogrowing a e.g. 20Gb DB by 10% does take a
> while. During this time, the DBCC command will find the DB full and abort.
> The DBCC command does not know that more space will be available shortly.
> Regards
> Mike
> "Bill Little" wrote:
> > I got the error msg below while rebuilding index for a large table. I have
> > allocated plenty of space for the primary filegroup and "auto" grow by 10% is
> > set. There are plenty of space in the drive. Please help me to resolve this
> > isse.
> >
> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC SQL
> > Server Driver][SQL Server]Could not allocate space for object '(SYSTEM table
> > id: -739371491)' in database 'FDIT' because the 'PRIMARY' filegroup is full.
> > [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> > terminated.
> >|||> I have set the space for the primary filegroup the same
> size as the size of the whole database.
Whether that is enough or not is impossible to answer without more information. If the table has a
clustered index, rebuilding is essentially copying the table to a new location and after the copy
removing the old table. I.e., you need as much free space as the size of the table (plus some ore)
on the file group where the table resides.
> Do I still need to increase the size
> or the size of the log file?
Rebuilding is a type of modification. If you are in FULL recovery mode, the amount of data moves (as
explained above) will be logged to the transaction log.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
news:71BB84AB-C440-480F-BD09-090A7E18C13B@.microsoft.com...
> Thank you very much. I have set the space for the primary filegroup the same
> size as the size of the whole database. Do I still need to increase the size
> or the size of the log file?
> Regards
> Bill
> "Mike Epprecht (SQL MVP)" wrote:
>> Hi
>> Rather grow the DB to the correct size before you do such an operation.
>> Yes, autogrow may be on, but autogrowing a e.g. 20Gb DB by 10% does take a
>> while. During this time, the DBCC command will find the DB full and abort.
>> The DBCC command does not know that more space will be available shortly.
>> Regards
>> Mike
>> "Bill Little" wrote:
>> > I got the error msg below while rebuilding index for a large table. I have
>> > allocated plenty of space for the primary filegroup and "auto" grow by 10% is
>> > set. There are plenty of space in the drive. Please help me to resolve this
>> > isse.
>> >
>> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC SQL
>> > Server Driver][SQL Server]Could not allocate space for object '(SYSTEM table
>> > id: -739371491)' in database 'FDIT' because the 'PRIMARY' filegroup is full.
>> > [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
>> > terminated.
>> >|||Thanks. I tried to add more space to the database but I got the error msg
below. What shall we do to resolve the issue?
Server: Msg 1827, Level 16, State 2, Line 1
CREATE/ALTER DATABASE failed because the resulting cumulative database size
would exceed your licensed limit of 2048 MB per database.
Extending database by 1024.00 MB on disk 'Primary'.
"Tibor Karaszi" wrote:
> > I have set the space for the primary filegroup the same
> > size as the size of the whole database.
> Whether that is enough or not is impossible to answer without more information. If the table has a
> clustered index, rebuilding is essentially copying the table to a new location and after the copy
> removing the old table. I.e., you need as much free space as the size of the table (plus some ore)
> on the file group where the table resides.
>
> > Do I still need to increase the size
> > or the size of the log file?
> Rebuilding is a type of modification. If you are in FULL recovery mode, the amount of data moves (as
> explained above) will be logged to the transaction log.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
> news:71BB84AB-C440-480F-BD09-090A7E18C13B@.microsoft.com...
> > Thank you very much. I have set the space for the primary filegroup the same
> > size as the size of the whole database. Do I still need to increase the size
> > or the size of the log file?
> >
> > Regards
> >
> > Bill
> >
> > "Mike Epprecht (SQL MVP)" wrote:
> >
> >> Hi
> >>
> >> Rather grow the DB to the correct size before you do such an operation.
> >>
> >> Yes, autogrow may be on, but autogrowing a e.g. 20Gb DB by 10% does take a
> >> while. During this time, the DBCC command will find the DB full and abort.
> >> The DBCC command does not know that more space will be available shortly.
> >>
> >> Regards
> >> Mike
> >>
> >> "Bill Little" wrote:
> >>
> >> > I got the error msg below while rebuilding index for a large table. I have
> >> > allocated plenty of space for the primary filegroup and "auto" grow by 10% is
> >> > set. There are plenty of space in the drive. Please help me to resolve this
> >> > isse.
> >> >
> >> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC SQL
> >> > Server Driver][SQL Server]Could not allocate space for object '(SYSTEM table
> >> > id: -739371491)' in database 'FDIT' because the 'PRIMARY' filegroup is full.
> >> > [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> >> > terminated.
> >> >
>
>|||Seems you are running MSDE, which has a max database size of 2GB data per database. You might want
to use DBCC INDEXDEFRAG in stead of DBCC DBREINDEX:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
news:9CC68E6E-1380-47A8-969C-DF9AE204C3F3@.microsoft.com...
> Thanks. I tried to add more space to the database but I got the error msg
> below. What shall we do to resolve the issue?
> Server: Msg 1827, Level 16, State 2, Line 1
> CREATE/ALTER DATABASE failed because the resulting cumulative database size
> would exceed your licensed limit of 2048 MB per database.
> Extending database by 1024.00 MB on disk 'Primary'.
>
> "Tibor Karaszi" wrote:
>> > I have set the space for the primary filegroup the same
>> > size as the size of the whole database.
>> Whether that is enough or not is impossible to answer without more information. If the table has
>> a
>> clustered index, rebuilding is essentially copying the table to a new location and after the copy
>> removing the old table. I.e., you need as much free space as the size of the table (plus some
>> ore)
>> on the file group where the table resides.
>>
>> > Do I still need to increase the size
>> > or the size of the log file?
>> Rebuilding is a type of modification. If you are in FULL recovery mode, the amount of data moves
>> (as
>> explained above) will be logged to the transaction log.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
>> news:71BB84AB-C440-480F-BD09-090A7E18C13B@.microsoft.com...
>> > Thank you very much. I have set the space for the primary filegroup the same
>> > size as the size of the whole database. Do I still need to increase the size
>> > or the size of the log file?
>> >
>> > Regards
>> >
>> > Bill
>> >
>> > "Mike Epprecht (SQL MVP)" wrote:
>> >
>> >> Hi
>> >>
>> >> Rather grow the DB to the correct size before you do such an operation.
>> >>
>> >> Yes, autogrow may be on, but autogrowing a e.g. 20Gb DB by 10% does take a
>> >> while. During this time, the DBCC command will find the DB full and abort.
>> >> The DBCC command does not know that more space will be available shortly.
>> >>
>> >> Regards
>> >> Mike
>> >>
>> >> "Bill Little" wrote:
>> >>
>> >> > I got the error msg below while rebuilding index for a large table. I have
>> >> > allocated plenty of space for the primary filegroup and "auto" grow by 10% is
>> >> > set. There are plenty of space in the drive. Please help me to resolve this
>> >> > isse.
>> >> >
>> >> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC SQL
>> >> > Server Driver][SQL Server]Could not allocate space for object '(SYSTEM table
>> >> > id: -739371491)' in database 'FDIT' because the 'PRIMARY' filegroup is full.
>> >> > [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
>> >> > terminated.
>> >> >
>>|||Thank you very much. We are using standard edition. So we cannot rebuild
index with this lisence limit condition, right? Any workaround or
suggestions. We have clustered indexes so I still want to use rebuild index
function.
Regards
Bill
"Tibor Karaszi" wrote:
> Seems you are running MSDE, which has a max database size of 2GB data per database. You might want
> to use DBCC INDEXDEFRAG in stead of DBCC DBREINDEX:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
> news:9CC68E6E-1380-47A8-969C-DF9AE204C3F3@.microsoft.com...
> > Thanks. I tried to add more space to the database but I got the error msg
> > below. What shall we do to resolve the issue?
> >
> > Server: Msg 1827, Level 16, State 2, Line 1
> > CREATE/ALTER DATABASE failed because the resulting cumulative database size
> > would exceed your licensed limit of 2048 MB per database.
> > Extending database by 1024.00 MB on disk 'Primary'.
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> > I have set the space for the primary filegroup the same
> >> > size as the size of the whole database.
> >>
> >> Whether that is enough or not is impossible to answer without more information. If the table has
> >> a
> >> clustered index, rebuilding is essentially copying the table to a new location and after the copy
> >> removing the old table. I.e., you need as much free space as the size of the table (plus some
> >> ore)
> >> on the file group where the table resides.
> >>
> >>
> >> > Do I still need to increase the size
> >> > or the size of the log file?
> >>
> >> Rebuilding is a type of modification. If you are in FULL recovery mode, the amount of data moves
> >> (as
> >> explained above) will be logged to the transaction log.
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> http://www.sqlug.se/
> >>
> >>
> >> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
> >> news:71BB84AB-C440-480F-BD09-090A7E18C13B@.microsoft.com...
> >> > Thank you very much. I have set the space for the primary filegroup the same
> >> > size as the size of the whole database. Do I still need to increase the size
> >> > or the size of the log file?
> >> >
> >> > Regards
> >> >
> >> > Bill
> >> >
> >> > "Mike Epprecht (SQL MVP)" wrote:
> >> >
> >> >> Hi
> >> >>
> >> >> Rather grow the DB to the correct size before you do such an operation.
> >> >>
> >> >> Yes, autogrow may be on, but autogrowing a e.g. 20Gb DB by 10% does take a
> >> >> while. During this time, the DBCC command will find the DB full and abort.
> >> >> The DBCC command does not know that more space will be available shortly.
> >> >>
> >> >> Regards
> >> >> Mike
> >> >>
> >> >> "Bill Little" wrote:
> >> >>
> >> >> > I got the error msg below while rebuilding index for a large table. I have
> >> >> > allocated plenty of space for the primary filegroup and "auto" grow by 10% is
> >> >> > set. There are plenty of space in the drive. Please help me to resolve this
> >> >> > isse.
> >> >> >
> >> >> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC SQL
> >> >> > Server Driver][SQL Server]Could not allocate space for object '(SYSTEM table
> >> >> > id: -739371491)' in database 'FDIT' because the 'PRIMARY' filegroup is full.
> >> >> > [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> >> >> > terminated.
> >> >> >
> >>
> >>
> >>
>
>|||You wouldn't get this error if you are on standard edition. Are you sure you work against the right
instance of SQL Server. Can you check the licensing?
SELECT @.@.VERSION
Also, INDEXDEFRAG work just fine on nc indexes...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
news:DFF76B92-66DD-47F9-9D63-EADEFFFFE9B2@.microsoft.com...
> Thank you very much. We are using standard edition. So we cannot rebuild
> index with this lisence limit condition, right? Any workaround or
> suggestions. We have clustered indexes so I still want to use rebuild index
> function.
> Regards
> Bill
> "Tibor Karaszi" wrote:
>> Seems you are running MSDE, which has a max database size of 2GB data per database. You might
>> want
>> to use DBCC INDEXDEFRAG in stead of DBCC DBREINDEX:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
>> news:9CC68E6E-1380-47A8-969C-DF9AE204C3F3@.microsoft.com...
>> > Thanks. I tried to add more space to the database but I got the error msg
>> > below. What shall we do to resolve the issue?
>> >
>> > Server: Msg 1827, Level 16, State 2, Line 1
>> > CREATE/ALTER DATABASE failed because the resulting cumulative database size
>> > would exceed your licensed limit of 2048 MB per database.
>> > Extending database by 1024.00 MB on disk 'Primary'.
>> >
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> > I have set the space for the primary filegroup the same
>> >> > size as the size of the whole database.
>> >>
>> >> Whether that is enough or not is impossible to answer without more information. If the table
>> >> has
>> >> a
>> >> clustered index, rebuilding is essentially copying the table to a new location and after the
>> >> copy
>> >> removing the old table. I.e., you need as much free space as the size of the table (plus some
>> >> ore)
>> >> on the file group where the table resides.
>> >>
>> >>
>> >> > Do I still need to increase the size
>> >> > or the size of the log file?
>> >>
>> >> Rebuilding is a type of modification. If you are in FULL recovery mode, the amount of data
>> >> moves
>> >> (as
>> >> explained above) will be logged to the transaction log.
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >> http://www.sqlug.se/
>> >>
>> >>
>> >> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
>> >> news:71BB84AB-C440-480F-BD09-090A7E18C13B@.microsoft.com...
>> >> > Thank you very much. I have set the space for the primary filegroup the same
>> >> > size as the size of the whole database. Do I still need to increase the size
>> >> > or the size of the log file?
>> >> >
>> >> > Regards
>> >> >
>> >> > Bill
>> >> >
>> >> > "Mike Epprecht (SQL MVP)" wrote:
>> >> >
>> >> >> Hi
>> >> >>
>> >> >> Rather grow the DB to the correct size before you do such an operation.
>> >> >>
>> >> >> Yes, autogrow may be on, but autogrowing a e.g. 20Gb DB by 10% does take a
>> >> >> while. During this time, the DBCC command will find the DB full and abort.
>> >> >> The DBCC command does not know that more space will be available shortly.
>> >> >>
>> >> >> Regards
>> >> >> Mike
>> >> >>
>> >> >> "Bill Little" wrote:
>> >> >>
>> >> >> > I got the error msg below while rebuilding index for a large table. I have
>> >> >> > allocated plenty of space for the primary filegroup and "auto" grow by 10% is
>> >> >> > set. There are plenty of space in the drive. Please help me to resolve this
>> >> >> > isse.
>> >> >> >
>> >> >> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC SQL
>> >> >> > Server Driver][SQL Server]Could not allocate space for object '(SYSTEM table
>> >> >> > id: -739371491)' in database 'FDIT' because the 'PRIMARY' filegroup is full.
>> >> >> > [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
>> >> >> > terminated.
>> >> >> >
>> >>
>> >>
>> >>
>>|||We have having the same exact problem. There is 30+ GB Free on the Drive
(Dynamic Disk, by the way). Database is set to unrestricted file growth.
Everything looks fine with the configuration of the sql database...however,
the database thinks there is no free space on the drive. Very wierd!!
Database is just shy of 5gb, but General Tab says 0.00Mb Free. Looks like
ill be calling MS soon!!
Dale
txskibum@.hotmail.com
"Tibor Karaszi" wrote:
> You wouldn't get this error if you are on standard edition. Are you sure you work against the right
> instance of SQL Server. Can you check the licensing?
> SELECT @.@.VERSION
> Also, INDEXDEFRAG work just fine on nc indexes...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
> news:DFF76B92-66DD-47F9-9D63-EADEFFFFE9B2@.microsoft.com...
> > Thank you very much. We are using standard edition. So we cannot rebuild
> > index with this lisence limit condition, right? Any workaround or
> > suggestions. We have clustered indexes so I still want to use rebuild index
> > function.
> >
> > Regards
> >
> > Bill
> >
> > "Tibor Karaszi" wrote:
> >
> >> Seems you are running MSDE, which has a max database size of 2GB data per database. You might
> >> want
> >> to use DBCC INDEXDEFRAG in stead of DBCC DBREINDEX:
> >> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> http://www.sqlug.se/
> >>
> >>
> >> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
> >> news:9CC68E6E-1380-47A8-969C-DF9AE204C3F3@.microsoft.com...
> >> > Thanks. I tried to add more space to the database but I got the error msg
> >> > below. What shall we do to resolve the issue?
> >> >
> >> > Server: Msg 1827, Level 16, State 2, Line 1
> >> > CREATE/ALTER DATABASE failed because the resulting cumulative database size
> >> > would exceed your licensed limit of 2048 MB per database.
> >> > Extending database by 1024.00 MB on disk 'Primary'.
> >> >
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> > I have set the space for the primary filegroup the same
> >> >> > size as the size of the whole database.
> >> >>
> >> >> Whether that is enough or not is impossible to answer without more information. If the table
> >> >> has
> >> >> a
> >> >> clustered index, rebuilding is essentially copying the table to a new location and after the
> >> >> copy
> >> >> removing the old table. I.e., you need as much free space as the size of the table (plus some
> >> >> ore)
> >> >> on the file group where the table resides.
> >> >>
> >> >>
> >> >> > Do I still need to increase the size
> >> >> > or the size of the log file?
> >> >>
> >> >> Rebuilding is a type of modification. If you are in FULL recovery mode, the amount of data
> >> >> moves
> >> >> (as
> >> >> explained above) will be logged to the transaction log.
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >> http://www.sqlug.se/
> >> >>
> >> >>
> >> >> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
> >> >> news:71BB84AB-C440-480F-BD09-090A7E18C13B@.microsoft.com...
> >> >> > Thank you very much. I have set the space for the primary filegroup the same
> >> >> > size as the size of the whole database. Do I still need to increase the size
> >> >> > or the size of the log file?
> >> >> >
> >> >> > Regards
> >> >> >
> >> >> > Bill
> >> >> >
> >> >> > "Mike Epprecht (SQL MVP)" wrote:
> >> >> >
> >> >> >> Hi
> >> >> >>
> >> >> >> Rather grow the DB to the correct size before you do such an operation.
> >> >> >>
> >> >> >> Yes, autogrow may be on, but autogrowing a e.g. 20Gb DB by 10% does take a
> >> >> >> while. During this time, the DBCC command will find the DB full and abort.
> >> >> >> The DBCC command does not know that more space will be available shortly.
> >> >> >>
> >> >> >> Regards
> >> >> >> Mike
> >> >> >>
> >> >> >> "Bill Little" wrote:
> >> >> >>
> >> >> >> > I got the error msg below while rebuilding index for a large table. I have
> >> >> >> > allocated plenty of space for the primary filegroup and "auto" grow by 10% is
> >> >> >> > set. There are plenty of space in the drive. Please help me to resolve this
> >> >> >> > isse.
> >> >> >> >
> >> >> >> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC SQL
> >> >> >> > Server Driver][SQL Server]Could not allocate space for object '(SYSTEM table
> >> >> >> > id: -739371491)' in database 'FDIT' because the 'PRIMARY' filegroup is full.
> >> >> >> > [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> >> >> >> > terminated.
> >> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||> We have having the same exact problem.
Same as which problem? The thread has several issues discussed. Anyhow, you don't seem to get the
"MSDE" error message, as MSDE has max size of 2GB for data storage per database.
Are you getting error messages or are you only worried that EM say 0MB free? Remember that EM
reports free space *inside* the database files, without regard to free space on disk SQL Server
pre-allocates storage.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Dale" <Dale@.discussions.microsoft.com> wrote in message
news:922A620C-5AEE-4DD6-93BD-36645F72B881@.microsoft.com...
> We have having the same exact problem. There is 30+ GB Free on the Drive
> (Dynamic Disk, by the way). Database is set to unrestricted file growth.
> Everything looks fine with the configuration of the sql database...however,
> the database thinks there is no free space on the drive. Very wierd!!
> Database is just shy of 5gb, but General Tab says 0.00Mb Free. Looks like
> ill be calling MS soon!!
> Dale
> txskibum@.hotmail.com
>
> "Tibor Karaszi" wrote:
>> You wouldn't get this error if you are on standard edition. Are you sure you work against the
>> right
>> instance of SQL Server. Can you check the licensing?
>> SELECT @.@.VERSION
>> Also, INDEXDEFRAG work just fine on nc indexes...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
>> news:DFF76B92-66DD-47F9-9D63-EADEFFFFE9B2@.microsoft.com...
>> > Thank you very much. We are using standard edition. So we cannot rebuild
>> > index with this lisence limit condition, right? Any workaround or
>> > suggestions. We have clustered indexes so I still want to use rebuild index
>> > function.
>> >
>> > Regards
>> >
>> > Bill
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> Seems you are running MSDE, which has a max database size of 2GB data per database. You might
>> >> want
>> >> to use DBCC INDEXDEFRAG in stead of DBCC DBREINDEX:
>> >> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >> http://www.sqlug.se/
>> >>
>> >>
>> >> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
>> >> news:9CC68E6E-1380-47A8-969C-DF9AE204C3F3@.microsoft.com...
>> >> > Thanks. I tried to add more space to the database but I got the error msg
>> >> > below. What shall we do to resolve the issue?
>> >> >
>> >> > Server: Msg 1827, Level 16, State 2, Line 1
>> >> > CREATE/ALTER DATABASE failed because the resulting cumulative database size
>> >> > would exceed your licensed limit of 2048 MB per database.
>> >> > Extending database by 1024.00 MB on disk 'Primary'.
>> >> >
>> >> >
>> >> > "Tibor Karaszi" wrote:
>> >> >
>> >> >> > I have set the space for the primary filegroup the same
>> >> >> > size as the size of the whole database.
>> >> >>
>> >> >> Whether that is enough or not is impossible to answer without more information. If the
>> >> >> table
>> >> >> has
>> >> >> a
>> >> >> clustered index, rebuilding is essentially copying the table to a new location and after
>> >> >> the
>> >> >> copy
>> >> >> removing the old table. I.e., you need as much free space as the size of the table (plus
>> >> >> some
>> >> >> ore)
>> >> >> on the file group where the table resides.
>> >> >>
>> >> >>
>> >> >> > Do I still need to increase the size
>> >> >> > or the size of the log file?
>> >> >>
>> >> >> Rebuilding is a type of modification. If you are in FULL recovery mode, the amount of data
>> >> >> moves
>> >> >> (as
>> >> >> explained above) will be logged to the transaction log.
>> >> >> --
>> >> >> Tibor Karaszi, SQL Server MVP
>> >> >> http://www.karaszi.com/sqlserver/default.asp
>> >> >> http://www.solidqualitylearning.com/
>> >> >> http://www.sqlug.se/
>> >> >>
>> >> >>
>> >> >> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
>> >> >> news:71BB84AB-C440-480F-BD09-090A7E18C13B@.microsoft.com...
>> >> >> > Thank you very much. I have set the space for the primary filegroup the same
>> >> >> > size as the size of the whole database. Do I still need to increase the size
>> >> >> > or the size of the log file?
>> >> >> >
>> >> >> > Regards
>> >> >> >
>> >> >> > Bill
>> >> >> >
>> >> >> > "Mike Epprecht (SQL MVP)" wrote:
>> >> >> >
>> >> >> >> Hi
>> >> >> >>
>> >> >> >> Rather grow the DB to the correct size before you do such an operation.
>> >> >> >>
>> >> >> >> Yes, autogrow may be on, but autogrowing a e.g. 20Gb DB by 10% does take a
>> >> >> >> while. During this time, the DBCC command will find the DB full and abort.
>> >> >> >> The DBCC command does not know that more space will be available shortly.
>> >> >> >>
>> >> >> >> Regards
>> >> >> >> Mike
>> >> >> >>
>> >> >> >> "Bill Little" wrote:
>> >> >> >>
>> >> >> >> > I got the error msg below while rebuilding index for a large table. I have
>> >> >> >> > allocated plenty of space for the primary filegroup and "auto" grow by 10% is
>> >> >> >> > set. There are plenty of space in the drive. Please help me to resolve this
>> >> >> >> > isse.
>> >> >> >> >
>> >> >> >> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC SQL
>> >> >> >> > Server Driver][SQL Server]Could not allocate space for object '(SYSTEM table
>> >> >> >> > id: -739371491)' in database 'FDIT' because the 'PRIMARY' filegroup is full.
>> >> >> >> > [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
>> >> >> >> > terminated.
>> >> >> >> >
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>|||Please disregard. I posted to the wrong trend.
"Tibor Karaszi" wrote:
> > We have having the same exact problem.
> Same as which problem? The thread has several issues discussed. Anyhow, you don't seem to get the
> "MSDE" error message, as MSDE has max size of 2GB for data storage per database.
> Are you getting error messages or are you only worried that EM say 0MB free? Remember that EM
> reports free space *inside* the database files, without regard to free space on disk SQL Server
> pre-allocates storage.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Dale" <Dale@.discussions.microsoft.com> wrote in message
> news:922A620C-5AEE-4DD6-93BD-36645F72B881@.microsoft.com...
> > We have having the same exact problem. There is 30+ GB Free on the Drive
> > (Dynamic Disk, by the way). Database is set to unrestricted file growth.
> > Everything looks fine with the configuration of the sql database...however,
> > the database thinks there is no free space on the drive. Very wierd!!
> > Database is just shy of 5gb, but General Tab says 0.00Mb Free. Looks like
> > ill be calling MS soon!!
> >
> > Dale
> > txskibum@.hotmail.com
> >
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> You wouldn't get this error if you are on standard edition. Are you sure you work against the
> >> right
> >> instance of SQL Server. Can you check the licensing?
> >> SELECT @.@.VERSION
> >>
> >> Also, INDEXDEFRAG work just fine on nc indexes...
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> http://www.sqlug.se/
> >>
> >>
> >> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
> >> news:DFF76B92-66DD-47F9-9D63-EADEFFFFE9B2@.microsoft.com...
> >> > Thank you very much. We are using standard edition. So we cannot rebuild
> >> > index with this lisence limit condition, right? Any workaround or
> >> > suggestions. We have clustered indexes so I still want to use rebuild index
> >> > function.
> >> >
> >> > Regards
> >> >
> >> > Bill
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> Seems you are running MSDE, which has a max database size of 2GB data per database. You might
> >> >> want
> >> >> to use DBCC INDEXDEFRAG in stead of DBCC DBREINDEX:
> >> >> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >> http://www.sqlug.se/
> >> >>
> >> >>
> >> >> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
> >> >> news:9CC68E6E-1380-47A8-969C-DF9AE204C3F3@.microsoft.com...
> >> >> > Thanks. I tried to add more space to the database but I got the error msg
> >> >> > below. What shall we do to resolve the issue?
> >> >> >
> >> >> > Server: Msg 1827, Level 16, State 2, Line 1
> >> >> > CREATE/ALTER DATABASE failed because the resulting cumulative database size
> >> >> > would exceed your licensed limit of 2048 MB per database.
> >> >> > Extending database by 1024.00 MB on disk 'Primary'.
> >> >> >
> >> >> >
> >> >> > "Tibor Karaszi" wrote:
> >> >> >
> >> >> >> > I have set the space for the primary filegroup the same
> >> >> >> > size as the size of the whole database.
> >> >> >>
> >> >> >> Whether that is enough or not is impossible to answer without more information. If the
> >> >> >> table
> >> >> >> has
> >> >> >> a
> >> >> >> clustered index, rebuilding is essentially copying the table to a new location and after
> >> >> >> the
> >> >> >> copy
> >> >> >> removing the old table. I.e., you need as much free space as the size of the table (plus
> >> >> >> some
> >> >> >> ore)
> >> >> >> on the file group where the table resides.
> >> >> >>
> >> >> >>
> >> >> >> > Do I still need to increase the size
> >> >> >> > or the size of the log file?
> >> >> >>
> >> >> >> Rebuilding is a type of modification. If you are in FULL recovery mode, the amount of data
> >> >> >> moves
> >> >> >> (as
> >> >> >> explained above) will be logged to the transaction log.
> >> >> >> --
> >> >> >> Tibor Karaszi, SQL Server MVP
> >> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> >> http://www.solidqualitylearning.com/
> >> >> >> http://www.sqlug.se/
> >> >> >>
> >> >> >>
> >> >> >> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
> >> >> >> news:71BB84AB-C440-480F-BD09-090A7E18C13B@.microsoft.com...
> >> >> >> > Thank you very much. I have set the space for the primary filegroup the same
> >> >> >> > size as the size of the whole database. Do I still need to increase the size
> >> >> >> > or the size of the log file?
> >> >> >> >
> >> >> >> > Regards
> >> >> >> >
> >> >> >> > Bill
> >> >> >> >
> >> >> >> > "Mike Epprecht (SQL MVP)" wrote:
> >> >> >> >
> >> >> >> >> Hi
> >> >> >> >>
> >> >> >> >> Rather grow the DB to the correct size before you do such an operation.
> >> >> >> >>
> >> >> >> >> Yes, autogrow may be on, but autogrowing a e.g. 20Gb DB by 10% does take a
> >> >> >> >> while. During this time, the DBCC command will find the DB full and abort.
> >> >> >> >> The DBCC command does not know that more space will be available shortly.
> >> >> >> >>
> >> >> >> >> Regards
> >> >> >> >> Mike
> >> >> >> >>
> >> >> >> >> "Bill Little" wrote:
> >> >> >> >>
> >> >> >> >> > I got the error msg below while rebuilding index for a large table. I have
> >> >> >> >> > allocated plenty of space for the primary filegroup and "auto" grow by 10% is
> >> >> >> >> > set. There are plenty of space in the drive. Please help me to resolve this
> >> >> >> >> > isse.
> >> >> >> >> >
> >> >> >> >> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC SQL
> >> >> >> >> > Server Driver][SQL Server]Could not allocate space for object '(SYSTEM table
> >> >> >> >> > id: -739371491)' in database 'FDIT' because the 'PRIMARY' filegroup is full.
> >> >> >> >> > [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> >> >> >> >> > terminated.
> >> >> >> >> >
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
allocated plenty of space for the primary filegroup and "auto" grow by 10% is
set. There are plenty of space in the drive. Please help me to resolve this
isse.
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC SQL
Server Driver][SQL Server]Could not allocate space for object '(SYSTEM table
id: -739371491)' in database 'FDIT' because the 'PRIMARY' filegroup is full.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
terminated.Hi
Rather grow the DB to the correct size before you do such an operation.
Yes, autogrow may be on, but autogrowing a e.g. 20Gb DB by 10% does take a
while. During this time, the DBCC command will find the DB full and abort.
The DBCC command does not know that more space will be available shortly.
Regards
Mike
"Bill Little" wrote:
> I got the error msg below while rebuilding index for a large table. I have
> allocated plenty of space for the primary filegroup and "auto" grow by 10% is
> set. There are plenty of space in the drive. Please help me to resolve this
> isse.
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC SQL
> Server Driver][SQL Server]Could not allocate space for object '(SYSTEM table
> id: -739371491)' in database 'FDIT' because the 'PRIMARY' filegroup is full.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> terminated.
>|||Thank you very much. I have set the space for the primary filegroup the same
size as the size of the whole database. Do I still need to increase the size
or the size of the log file?
Regards
Bill
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Rather grow the DB to the correct size before you do such an operation.
> Yes, autogrow may be on, but autogrowing a e.g. 20Gb DB by 10% does take a
> while. During this time, the DBCC command will find the DB full and abort.
> The DBCC command does not know that more space will be available shortly.
> Regards
> Mike
> "Bill Little" wrote:
> > I got the error msg below while rebuilding index for a large table. I have
> > allocated plenty of space for the primary filegroup and "auto" grow by 10% is
> > set. There are plenty of space in the drive. Please help me to resolve this
> > isse.
> >
> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC SQL
> > Server Driver][SQL Server]Could not allocate space for object '(SYSTEM table
> > id: -739371491)' in database 'FDIT' because the 'PRIMARY' filegroup is full.
> > [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> > terminated.
> >|||> I have set the space for the primary filegroup the same
> size as the size of the whole database.
Whether that is enough or not is impossible to answer without more information. If the table has a
clustered index, rebuilding is essentially copying the table to a new location and after the copy
removing the old table. I.e., you need as much free space as the size of the table (plus some ore)
on the file group where the table resides.
> Do I still need to increase the size
> or the size of the log file?
Rebuilding is a type of modification. If you are in FULL recovery mode, the amount of data moves (as
explained above) will be logged to the transaction log.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
news:71BB84AB-C440-480F-BD09-090A7E18C13B@.microsoft.com...
> Thank you very much. I have set the space for the primary filegroup the same
> size as the size of the whole database. Do I still need to increase the size
> or the size of the log file?
> Regards
> Bill
> "Mike Epprecht (SQL MVP)" wrote:
>> Hi
>> Rather grow the DB to the correct size before you do such an operation.
>> Yes, autogrow may be on, but autogrowing a e.g. 20Gb DB by 10% does take a
>> while. During this time, the DBCC command will find the DB full and abort.
>> The DBCC command does not know that more space will be available shortly.
>> Regards
>> Mike
>> "Bill Little" wrote:
>> > I got the error msg below while rebuilding index for a large table. I have
>> > allocated plenty of space for the primary filegroup and "auto" grow by 10% is
>> > set. There are plenty of space in the drive. Please help me to resolve this
>> > isse.
>> >
>> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC SQL
>> > Server Driver][SQL Server]Could not allocate space for object '(SYSTEM table
>> > id: -739371491)' in database 'FDIT' because the 'PRIMARY' filegroup is full.
>> > [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
>> > terminated.
>> >|||Thanks. I tried to add more space to the database but I got the error msg
below. What shall we do to resolve the issue?
Server: Msg 1827, Level 16, State 2, Line 1
CREATE/ALTER DATABASE failed because the resulting cumulative database size
would exceed your licensed limit of 2048 MB per database.
Extending database by 1024.00 MB on disk 'Primary'.
"Tibor Karaszi" wrote:
> > I have set the space for the primary filegroup the same
> > size as the size of the whole database.
> Whether that is enough or not is impossible to answer without more information. If the table has a
> clustered index, rebuilding is essentially copying the table to a new location and after the copy
> removing the old table. I.e., you need as much free space as the size of the table (plus some ore)
> on the file group where the table resides.
>
> > Do I still need to increase the size
> > or the size of the log file?
> Rebuilding is a type of modification. If you are in FULL recovery mode, the amount of data moves (as
> explained above) will be logged to the transaction log.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
> news:71BB84AB-C440-480F-BD09-090A7E18C13B@.microsoft.com...
> > Thank you very much. I have set the space for the primary filegroup the same
> > size as the size of the whole database. Do I still need to increase the size
> > or the size of the log file?
> >
> > Regards
> >
> > Bill
> >
> > "Mike Epprecht (SQL MVP)" wrote:
> >
> >> Hi
> >>
> >> Rather grow the DB to the correct size before you do such an operation.
> >>
> >> Yes, autogrow may be on, but autogrowing a e.g. 20Gb DB by 10% does take a
> >> while. During this time, the DBCC command will find the DB full and abort.
> >> The DBCC command does not know that more space will be available shortly.
> >>
> >> Regards
> >> Mike
> >>
> >> "Bill Little" wrote:
> >>
> >> > I got the error msg below while rebuilding index for a large table. I have
> >> > allocated plenty of space for the primary filegroup and "auto" grow by 10% is
> >> > set. There are plenty of space in the drive. Please help me to resolve this
> >> > isse.
> >> >
> >> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC SQL
> >> > Server Driver][SQL Server]Could not allocate space for object '(SYSTEM table
> >> > id: -739371491)' in database 'FDIT' because the 'PRIMARY' filegroup is full.
> >> > [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> >> > terminated.
> >> >
>
>|||Seems you are running MSDE, which has a max database size of 2GB data per database. You might want
to use DBCC INDEXDEFRAG in stead of DBCC DBREINDEX:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
news:9CC68E6E-1380-47A8-969C-DF9AE204C3F3@.microsoft.com...
> Thanks. I tried to add more space to the database but I got the error msg
> below. What shall we do to resolve the issue?
> Server: Msg 1827, Level 16, State 2, Line 1
> CREATE/ALTER DATABASE failed because the resulting cumulative database size
> would exceed your licensed limit of 2048 MB per database.
> Extending database by 1024.00 MB on disk 'Primary'.
>
> "Tibor Karaszi" wrote:
>> > I have set the space for the primary filegroup the same
>> > size as the size of the whole database.
>> Whether that is enough or not is impossible to answer without more information. If the table has
>> a
>> clustered index, rebuilding is essentially copying the table to a new location and after the copy
>> removing the old table. I.e., you need as much free space as the size of the table (plus some
>> ore)
>> on the file group where the table resides.
>>
>> > Do I still need to increase the size
>> > or the size of the log file?
>> Rebuilding is a type of modification. If you are in FULL recovery mode, the amount of data moves
>> (as
>> explained above) will be logged to the transaction log.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
>> news:71BB84AB-C440-480F-BD09-090A7E18C13B@.microsoft.com...
>> > Thank you very much. I have set the space for the primary filegroup the same
>> > size as the size of the whole database. Do I still need to increase the size
>> > or the size of the log file?
>> >
>> > Regards
>> >
>> > Bill
>> >
>> > "Mike Epprecht (SQL MVP)" wrote:
>> >
>> >> Hi
>> >>
>> >> Rather grow the DB to the correct size before you do such an operation.
>> >>
>> >> Yes, autogrow may be on, but autogrowing a e.g. 20Gb DB by 10% does take a
>> >> while. During this time, the DBCC command will find the DB full and abort.
>> >> The DBCC command does not know that more space will be available shortly.
>> >>
>> >> Regards
>> >> Mike
>> >>
>> >> "Bill Little" wrote:
>> >>
>> >> > I got the error msg below while rebuilding index for a large table. I have
>> >> > allocated plenty of space for the primary filegroup and "auto" grow by 10% is
>> >> > set. There are plenty of space in the drive. Please help me to resolve this
>> >> > isse.
>> >> >
>> >> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC SQL
>> >> > Server Driver][SQL Server]Could not allocate space for object '(SYSTEM table
>> >> > id: -739371491)' in database 'FDIT' because the 'PRIMARY' filegroup is full.
>> >> > [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
>> >> > terminated.
>> >> >
>>|||Thank you very much. We are using standard edition. So we cannot rebuild
index with this lisence limit condition, right? Any workaround or
suggestions. We have clustered indexes so I still want to use rebuild index
function.
Regards
Bill
"Tibor Karaszi" wrote:
> Seems you are running MSDE, which has a max database size of 2GB data per database. You might want
> to use DBCC INDEXDEFRAG in stead of DBCC DBREINDEX:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
> news:9CC68E6E-1380-47A8-969C-DF9AE204C3F3@.microsoft.com...
> > Thanks. I tried to add more space to the database but I got the error msg
> > below. What shall we do to resolve the issue?
> >
> > Server: Msg 1827, Level 16, State 2, Line 1
> > CREATE/ALTER DATABASE failed because the resulting cumulative database size
> > would exceed your licensed limit of 2048 MB per database.
> > Extending database by 1024.00 MB on disk 'Primary'.
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> > I have set the space for the primary filegroup the same
> >> > size as the size of the whole database.
> >>
> >> Whether that is enough or not is impossible to answer without more information. If the table has
> >> a
> >> clustered index, rebuilding is essentially copying the table to a new location and after the copy
> >> removing the old table. I.e., you need as much free space as the size of the table (plus some
> >> ore)
> >> on the file group where the table resides.
> >>
> >>
> >> > Do I still need to increase the size
> >> > or the size of the log file?
> >>
> >> Rebuilding is a type of modification. If you are in FULL recovery mode, the amount of data moves
> >> (as
> >> explained above) will be logged to the transaction log.
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> http://www.sqlug.se/
> >>
> >>
> >> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
> >> news:71BB84AB-C440-480F-BD09-090A7E18C13B@.microsoft.com...
> >> > Thank you very much. I have set the space for the primary filegroup the same
> >> > size as the size of the whole database. Do I still need to increase the size
> >> > or the size of the log file?
> >> >
> >> > Regards
> >> >
> >> > Bill
> >> >
> >> > "Mike Epprecht (SQL MVP)" wrote:
> >> >
> >> >> Hi
> >> >>
> >> >> Rather grow the DB to the correct size before you do such an operation.
> >> >>
> >> >> Yes, autogrow may be on, but autogrowing a e.g. 20Gb DB by 10% does take a
> >> >> while. During this time, the DBCC command will find the DB full and abort.
> >> >> The DBCC command does not know that more space will be available shortly.
> >> >>
> >> >> Regards
> >> >> Mike
> >> >>
> >> >> "Bill Little" wrote:
> >> >>
> >> >> > I got the error msg below while rebuilding index for a large table. I have
> >> >> > allocated plenty of space for the primary filegroup and "auto" grow by 10% is
> >> >> > set. There are plenty of space in the drive. Please help me to resolve this
> >> >> > isse.
> >> >> >
> >> >> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC SQL
> >> >> > Server Driver][SQL Server]Could not allocate space for object '(SYSTEM table
> >> >> > id: -739371491)' in database 'FDIT' because the 'PRIMARY' filegroup is full.
> >> >> > [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> >> >> > terminated.
> >> >> >
> >>
> >>
> >>
>
>|||You wouldn't get this error if you are on standard edition. Are you sure you work against the right
instance of SQL Server. Can you check the licensing?
SELECT @.@.VERSION
Also, INDEXDEFRAG work just fine on nc indexes...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
news:DFF76B92-66DD-47F9-9D63-EADEFFFFE9B2@.microsoft.com...
> Thank you very much. We are using standard edition. So we cannot rebuild
> index with this lisence limit condition, right? Any workaround or
> suggestions. We have clustered indexes so I still want to use rebuild index
> function.
> Regards
> Bill
> "Tibor Karaszi" wrote:
>> Seems you are running MSDE, which has a max database size of 2GB data per database. You might
>> want
>> to use DBCC INDEXDEFRAG in stead of DBCC DBREINDEX:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
>> news:9CC68E6E-1380-47A8-969C-DF9AE204C3F3@.microsoft.com...
>> > Thanks. I tried to add more space to the database but I got the error msg
>> > below. What shall we do to resolve the issue?
>> >
>> > Server: Msg 1827, Level 16, State 2, Line 1
>> > CREATE/ALTER DATABASE failed because the resulting cumulative database size
>> > would exceed your licensed limit of 2048 MB per database.
>> > Extending database by 1024.00 MB on disk 'Primary'.
>> >
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> > I have set the space for the primary filegroup the same
>> >> > size as the size of the whole database.
>> >>
>> >> Whether that is enough or not is impossible to answer without more information. If the table
>> >> has
>> >> a
>> >> clustered index, rebuilding is essentially copying the table to a new location and after the
>> >> copy
>> >> removing the old table. I.e., you need as much free space as the size of the table (plus some
>> >> ore)
>> >> on the file group where the table resides.
>> >>
>> >>
>> >> > Do I still need to increase the size
>> >> > or the size of the log file?
>> >>
>> >> Rebuilding is a type of modification. If you are in FULL recovery mode, the amount of data
>> >> moves
>> >> (as
>> >> explained above) will be logged to the transaction log.
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >> http://www.sqlug.se/
>> >>
>> >>
>> >> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
>> >> news:71BB84AB-C440-480F-BD09-090A7E18C13B@.microsoft.com...
>> >> > Thank you very much. I have set the space for the primary filegroup the same
>> >> > size as the size of the whole database. Do I still need to increase the size
>> >> > or the size of the log file?
>> >> >
>> >> > Regards
>> >> >
>> >> > Bill
>> >> >
>> >> > "Mike Epprecht (SQL MVP)" wrote:
>> >> >
>> >> >> Hi
>> >> >>
>> >> >> Rather grow the DB to the correct size before you do such an operation.
>> >> >>
>> >> >> Yes, autogrow may be on, but autogrowing a e.g. 20Gb DB by 10% does take a
>> >> >> while. During this time, the DBCC command will find the DB full and abort.
>> >> >> The DBCC command does not know that more space will be available shortly.
>> >> >>
>> >> >> Regards
>> >> >> Mike
>> >> >>
>> >> >> "Bill Little" wrote:
>> >> >>
>> >> >> > I got the error msg below while rebuilding index for a large table. I have
>> >> >> > allocated plenty of space for the primary filegroup and "auto" grow by 10% is
>> >> >> > set. There are plenty of space in the drive. Please help me to resolve this
>> >> >> > isse.
>> >> >> >
>> >> >> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC SQL
>> >> >> > Server Driver][SQL Server]Could not allocate space for object '(SYSTEM table
>> >> >> > id: -739371491)' in database 'FDIT' because the 'PRIMARY' filegroup is full.
>> >> >> > [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
>> >> >> > terminated.
>> >> >> >
>> >>
>> >>
>> >>
>>|||We have having the same exact problem. There is 30+ GB Free on the Drive
(Dynamic Disk, by the way). Database is set to unrestricted file growth.
Everything looks fine with the configuration of the sql database...however,
the database thinks there is no free space on the drive. Very wierd!!
Database is just shy of 5gb, but General Tab says 0.00Mb Free. Looks like
ill be calling MS soon!!
Dale
txskibum@.hotmail.com
"Tibor Karaszi" wrote:
> You wouldn't get this error if you are on standard edition. Are you sure you work against the right
> instance of SQL Server. Can you check the licensing?
> SELECT @.@.VERSION
> Also, INDEXDEFRAG work just fine on nc indexes...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
> news:DFF76B92-66DD-47F9-9D63-EADEFFFFE9B2@.microsoft.com...
> > Thank you very much. We are using standard edition. So we cannot rebuild
> > index with this lisence limit condition, right? Any workaround or
> > suggestions. We have clustered indexes so I still want to use rebuild index
> > function.
> >
> > Regards
> >
> > Bill
> >
> > "Tibor Karaszi" wrote:
> >
> >> Seems you are running MSDE, which has a max database size of 2GB data per database. You might
> >> want
> >> to use DBCC INDEXDEFRAG in stead of DBCC DBREINDEX:
> >> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> http://www.sqlug.se/
> >>
> >>
> >> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
> >> news:9CC68E6E-1380-47A8-969C-DF9AE204C3F3@.microsoft.com...
> >> > Thanks. I tried to add more space to the database but I got the error msg
> >> > below. What shall we do to resolve the issue?
> >> >
> >> > Server: Msg 1827, Level 16, State 2, Line 1
> >> > CREATE/ALTER DATABASE failed because the resulting cumulative database size
> >> > would exceed your licensed limit of 2048 MB per database.
> >> > Extending database by 1024.00 MB on disk 'Primary'.
> >> >
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> > I have set the space for the primary filegroup the same
> >> >> > size as the size of the whole database.
> >> >>
> >> >> Whether that is enough or not is impossible to answer without more information. If the table
> >> >> has
> >> >> a
> >> >> clustered index, rebuilding is essentially copying the table to a new location and after the
> >> >> copy
> >> >> removing the old table. I.e., you need as much free space as the size of the table (plus some
> >> >> ore)
> >> >> on the file group where the table resides.
> >> >>
> >> >>
> >> >> > Do I still need to increase the size
> >> >> > or the size of the log file?
> >> >>
> >> >> Rebuilding is a type of modification. If you are in FULL recovery mode, the amount of data
> >> >> moves
> >> >> (as
> >> >> explained above) will be logged to the transaction log.
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >> http://www.sqlug.se/
> >> >>
> >> >>
> >> >> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
> >> >> news:71BB84AB-C440-480F-BD09-090A7E18C13B@.microsoft.com...
> >> >> > Thank you very much. I have set the space for the primary filegroup the same
> >> >> > size as the size of the whole database. Do I still need to increase the size
> >> >> > or the size of the log file?
> >> >> >
> >> >> > Regards
> >> >> >
> >> >> > Bill
> >> >> >
> >> >> > "Mike Epprecht (SQL MVP)" wrote:
> >> >> >
> >> >> >> Hi
> >> >> >>
> >> >> >> Rather grow the DB to the correct size before you do such an operation.
> >> >> >>
> >> >> >> Yes, autogrow may be on, but autogrowing a e.g. 20Gb DB by 10% does take a
> >> >> >> while. During this time, the DBCC command will find the DB full and abort.
> >> >> >> The DBCC command does not know that more space will be available shortly.
> >> >> >>
> >> >> >> Regards
> >> >> >> Mike
> >> >> >>
> >> >> >> "Bill Little" wrote:
> >> >> >>
> >> >> >> > I got the error msg below while rebuilding index for a large table. I have
> >> >> >> > allocated plenty of space for the primary filegroup and "auto" grow by 10% is
> >> >> >> > set. There are plenty of space in the drive. Please help me to resolve this
> >> >> >> > isse.
> >> >> >> >
> >> >> >> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC SQL
> >> >> >> > Server Driver][SQL Server]Could not allocate space for object '(SYSTEM table
> >> >> >> > id: -739371491)' in database 'FDIT' because the 'PRIMARY' filegroup is full.
> >> >> >> > [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> >> >> >> > terminated.
> >> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||> We have having the same exact problem.
Same as which problem? The thread has several issues discussed. Anyhow, you don't seem to get the
"MSDE" error message, as MSDE has max size of 2GB for data storage per database.
Are you getting error messages or are you only worried that EM say 0MB free? Remember that EM
reports free space *inside* the database files, without regard to free space on disk SQL Server
pre-allocates storage.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Dale" <Dale@.discussions.microsoft.com> wrote in message
news:922A620C-5AEE-4DD6-93BD-36645F72B881@.microsoft.com...
> We have having the same exact problem. There is 30+ GB Free on the Drive
> (Dynamic Disk, by the way). Database is set to unrestricted file growth.
> Everything looks fine with the configuration of the sql database...however,
> the database thinks there is no free space on the drive. Very wierd!!
> Database is just shy of 5gb, but General Tab says 0.00Mb Free. Looks like
> ill be calling MS soon!!
> Dale
> txskibum@.hotmail.com
>
> "Tibor Karaszi" wrote:
>> You wouldn't get this error if you are on standard edition. Are you sure you work against the
>> right
>> instance of SQL Server. Can you check the licensing?
>> SELECT @.@.VERSION
>> Also, INDEXDEFRAG work just fine on nc indexes...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
>> news:DFF76B92-66DD-47F9-9D63-EADEFFFFE9B2@.microsoft.com...
>> > Thank you very much. We are using standard edition. So we cannot rebuild
>> > index with this lisence limit condition, right? Any workaround or
>> > suggestions. We have clustered indexes so I still want to use rebuild index
>> > function.
>> >
>> > Regards
>> >
>> > Bill
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> Seems you are running MSDE, which has a max database size of 2GB data per database. You might
>> >> want
>> >> to use DBCC INDEXDEFRAG in stead of DBCC DBREINDEX:
>> >> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >> http://www.sqlug.se/
>> >>
>> >>
>> >> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
>> >> news:9CC68E6E-1380-47A8-969C-DF9AE204C3F3@.microsoft.com...
>> >> > Thanks. I tried to add more space to the database but I got the error msg
>> >> > below. What shall we do to resolve the issue?
>> >> >
>> >> > Server: Msg 1827, Level 16, State 2, Line 1
>> >> > CREATE/ALTER DATABASE failed because the resulting cumulative database size
>> >> > would exceed your licensed limit of 2048 MB per database.
>> >> > Extending database by 1024.00 MB on disk 'Primary'.
>> >> >
>> >> >
>> >> > "Tibor Karaszi" wrote:
>> >> >
>> >> >> > I have set the space for the primary filegroup the same
>> >> >> > size as the size of the whole database.
>> >> >>
>> >> >> Whether that is enough or not is impossible to answer without more information. If the
>> >> >> table
>> >> >> has
>> >> >> a
>> >> >> clustered index, rebuilding is essentially copying the table to a new location and after
>> >> >> the
>> >> >> copy
>> >> >> removing the old table. I.e., you need as much free space as the size of the table (plus
>> >> >> some
>> >> >> ore)
>> >> >> on the file group where the table resides.
>> >> >>
>> >> >>
>> >> >> > Do I still need to increase the size
>> >> >> > or the size of the log file?
>> >> >>
>> >> >> Rebuilding is a type of modification. If you are in FULL recovery mode, the amount of data
>> >> >> moves
>> >> >> (as
>> >> >> explained above) will be logged to the transaction log.
>> >> >> --
>> >> >> Tibor Karaszi, SQL Server MVP
>> >> >> http://www.karaszi.com/sqlserver/default.asp
>> >> >> http://www.solidqualitylearning.com/
>> >> >> http://www.sqlug.se/
>> >> >>
>> >> >>
>> >> >> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
>> >> >> news:71BB84AB-C440-480F-BD09-090A7E18C13B@.microsoft.com...
>> >> >> > Thank you very much. I have set the space for the primary filegroup the same
>> >> >> > size as the size of the whole database. Do I still need to increase the size
>> >> >> > or the size of the log file?
>> >> >> >
>> >> >> > Regards
>> >> >> >
>> >> >> > Bill
>> >> >> >
>> >> >> > "Mike Epprecht (SQL MVP)" wrote:
>> >> >> >
>> >> >> >> Hi
>> >> >> >>
>> >> >> >> Rather grow the DB to the correct size before you do such an operation.
>> >> >> >>
>> >> >> >> Yes, autogrow may be on, but autogrowing a e.g. 20Gb DB by 10% does take a
>> >> >> >> while. During this time, the DBCC command will find the DB full and abort.
>> >> >> >> The DBCC command does not know that more space will be available shortly.
>> >> >> >>
>> >> >> >> Regards
>> >> >> >> Mike
>> >> >> >>
>> >> >> >> "Bill Little" wrote:
>> >> >> >>
>> >> >> >> > I got the error msg below while rebuilding index for a large table. I have
>> >> >> >> > allocated plenty of space for the primary filegroup and "auto" grow by 10% is
>> >> >> >> > set. There are plenty of space in the drive. Please help me to resolve this
>> >> >> >> > isse.
>> >> >> >> >
>> >> >> >> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC SQL
>> >> >> >> > Server Driver][SQL Server]Could not allocate space for object '(SYSTEM table
>> >> >> >> > id: -739371491)' in database 'FDIT' because the 'PRIMARY' filegroup is full.
>> >> >> >> > [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
>> >> >> >> > terminated.
>> >> >> >> >
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>|||Please disregard. I posted to the wrong trend.
"Tibor Karaszi" wrote:
> > We have having the same exact problem.
> Same as which problem? The thread has several issues discussed. Anyhow, you don't seem to get the
> "MSDE" error message, as MSDE has max size of 2GB for data storage per database.
> Are you getting error messages or are you only worried that EM say 0MB free? Remember that EM
> reports free space *inside* the database files, without regard to free space on disk SQL Server
> pre-allocates storage.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Dale" <Dale@.discussions.microsoft.com> wrote in message
> news:922A620C-5AEE-4DD6-93BD-36645F72B881@.microsoft.com...
> > We have having the same exact problem. There is 30+ GB Free on the Drive
> > (Dynamic Disk, by the way). Database is set to unrestricted file growth.
> > Everything looks fine with the configuration of the sql database...however,
> > the database thinks there is no free space on the drive. Very wierd!!
> > Database is just shy of 5gb, but General Tab says 0.00Mb Free. Looks like
> > ill be calling MS soon!!
> >
> > Dale
> > txskibum@.hotmail.com
> >
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> You wouldn't get this error if you are on standard edition. Are you sure you work against the
> >> right
> >> instance of SQL Server. Can you check the licensing?
> >> SELECT @.@.VERSION
> >>
> >> Also, INDEXDEFRAG work just fine on nc indexes...
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> http://www.sqlug.se/
> >>
> >>
> >> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
> >> news:DFF76B92-66DD-47F9-9D63-EADEFFFFE9B2@.microsoft.com...
> >> > Thank you very much. We are using standard edition. So we cannot rebuild
> >> > index with this lisence limit condition, right? Any workaround or
> >> > suggestions. We have clustered indexes so I still want to use rebuild index
> >> > function.
> >> >
> >> > Regards
> >> >
> >> > Bill
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> Seems you are running MSDE, which has a max database size of 2GB data per database. You might
> >> >> want
> >> >> to use DBCC INDEXDEFRAG in stead of DBCC DBREINDEX:
> >> >> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >> http://www.sqlug.se/
> >> >>
> >> >>
> >> >> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
> >> >> news:9CC68E6E-1380-47A8-969C-DF9AE204C3F3@.microsoft.com...
> >> >> > Thanks. I tried to add more space to the database but I got the error msg
> >> >> > below. What shall we do to resolve the issue?
> >> >> >
> >> >> > Server: Msg 1827, Level 16, State 2, Line 1
> >> >> > CREATE/ALTER DATABASE failed because the resulting cumulative database size
> >> >> > would exceed your licensed limit of 2048 MB per database.
> >> >> > Extending database by 1024.00 MB on disk 'Primary'.
> >> >> >
> >> >> >
> >> >> > "Tibor Karaszi" wrote:
> >> >> >
> >> >> >> > I have set the space for the primary filegroup the same
> >> >> >> > size as the size of the whole database.
> >> >> >>
> >> >> >> Whether that is enough or not is impossible to answer without more information. If the
> >> >> >> table
> >> >> >> has
> >> >> >> a
> >> >> >> clustered index, rebuilding is essentially copying the table to a new location and after
> >> >> >> the
> >> >> >> copy
> >> >> >> removing the old table. I.e., you need as much free space as the size of the table (plus
> >> >> >> some
> >> >> >> ore)
> >> >> >> on the file group where the table resides.
> >> >> >>
> >> >> >>
> >> >> >> > Do I still need to increase the size
> >> >> >> > or the size of the log file?
> >> >> >>
> >> >> >> Rebuilding is a type of modification. If you are in FULL recovery mode, the amount of data
> >> >> >> moves
> >> >> >> (as
> >> >> >> explained above) will be logged to the transaction log.
> >> >> >> --
> >> >> >> Tibor Karaszi, SQL Server MVP
> >> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> >> http://www.solidqualitylearning.com/
> >> >> >> http://www.sqlug.se/
> >> >> >>
> >> >> >>
> >> >> >> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
> >> >> >> news:71BB84AB-C440-480F-BD09-090A7E18C13B@.microsoft.com...
> >> >> >> > Thank you very much. I have set the space for the primary filegroup the same
> >> >> >> > size as the size of the whole database. Do I still need to increase the size
> >> >> >> > or the size of the log file?
> >> >> >> >
> >> >> >> > Regards
> >> >> >> >
> >> >> >> > Bill
> >> >> >> >
> >> >> >> > "Mike Epprecht (SQL MVP)" wrote:
> >> >> >> >
> >> >> >> >> Hi
> >> >> >> >>
> >> >> >> >> Rather grow the DB to the correct size before you do such an operation.
> >> >> >> >>
> >> >> >> >> Yes, autogrow may be on, but autogrowing a e.g. 20Gb DB by 10% does take a
> >> >> >> >> while. During this time, the DBCC command will find the DB full and abort.
> >> >> >> >> The DBCC command does not know that more space will be available shortly.
> >> >> >> >>
> >> >> >> >> Regards
> >> >> >> >> Mike
> >> >> >> >>
> >> >> >> >> "Bill Little" wrote:
> >> >> >> >>
> >> >> >> >> > I got the error msg below while rebuilding index for a large table. I have
> >> >> >> >> > allocated plenty of space for the primary filegroup and "auto" grow by 10% is
> >> >> >> >> > set. There are plenty of space in the drive. Please help me to resolve this
> >> >> >> >> > isse.
> >> >> >> >> >
> >> >> >> >> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC SQL
> >> >> >> >> > Server Driver][SQL Server]Could not allocate space for object '(SYSTEM table
> >> >> >> >> > id: -739371491)' in database 'FDIT' because the 'PRIMARY' filegroup is full.
> >> >> >> >> > [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> >> >> >> >> > terminated.
> >> >> >> >> >
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
Can not allocate space for rebuilding index
I got the error msg below while rebuilding index for a large table. I have
allocated plenty of space for the primary filegroup and "auto" grow by 10% is
set. There are plenty of space in the drive. Please help me to resolve this
isse.
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC SQL
Server Driver][SQL Server]Could not allocate space for object '(SYSTEM table
id: -739371491)' in database 'FDIT' because the 'PRIMARY' filegroup is full.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
terminated.
Hi
Rather grow the DB to the correct size before you do such an operation.
Yes, autogrow may be on, but autogrowing a e.g. 20Gb DB by 10% does take a
while. During this time, the DBCC command will find the DB full and abort.
The DBCC command does not know that more space will be available shortly.
Regards
Mike
"Bill Little" wrote:
> I got the error msg below while rebuilding index for a large table. I have
> allocated plenty of space for the primary filegroup and "auto" grow by 10% is
> set. There are plenty of space in the drive. Please help me to resolve this
> isse.
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC SQL
> Server Driver][SQL Server]Could not allocate space for object '(SYSTEM table
> id: -739371491)' in database 'FDIT' because the 'PRIMARY' filegroup is full.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> terminated.
>
|||Thank you very much. I have set the space for the primary filegroup the same
size as the size of the whole database. Do I still need to increase the size
or the size of the log file?
Regards
Bill
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> Rather grow the DB to the correct size before you do such an operation.
> Yes, autogrow may be on, but autogrowing a e.g. 20Gb DB by 10% does take a
> while. During this time, the DBCC command will find the DB full and abort.
> The DBCC command does not know that more space will be available shortly.
> Regards
> Mike
> "Bill Little" wrote:
|||> I have set the space for the primary filegroup the same
> size as the size of the whole database.
Whether that is enough or not is impossible to answer without more information. If the table has a
clustered index, rebuilding is essentially copying the table to a new location and after the copy
removing the old table. I.e., you need as much free space as the size of the table (plus some ore)
on the file group where the table resides.
> Do I still need to increase the size
> or the size of the log file?
Rebuilding is a type of modification. If you are in FULL recovery mode, the amount of data moves (as
explained above) will be logged to the transaction log.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
news:71BB84AB-C440-480F-BD09-090A7E18C13B@.microsoft.com...[vbcol=seagreen]
> Thank you very much. I have set the space for the primary filegroup the same
> size as the size of the whole database. Do I still need to increase the size
> or the size of the log file?
> Regards
> Bill
> "Mike Epprecht (SQL MVP)" wrote:
|||Thanks. I tried to add more space to the database but I got the error msg
below. What shall we do to resolve the issue?
Server: Msg 1827, Level 16, State 2, Line 1
CREATE/ALTER DATABASE failed because the resulting cumulative database size
would exceed your licensed limit of 2048 MB per database.
Extending database by 1024.00 MB on disk 'Primary'.
"Tibor Karaszi" wrote:
> Whether that is enough or not is impossible to answer without more information. If the table has a
> clustered index, rebuilding is essentially copying the table to a new location and after the copy
> removing the old table. I.e., you need as much free space as the size of the table (plus some ore)
> on the file group where the table resides.
>
> Rebuilding is a type of modification. If you are in FULL recovery mode, the amount of data moves (as
> explained above) will be logged to the transaction log.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
> news:71BB84AB-C440-480F-BD09-090A7E18C13B@.microsoft.com...
>
>
|||Seems you are running MSDE, which has a max database size of 2GB data per database. You might want
to use DBCC INDEXDEFRAG in stead of DBCC DBREINDEX:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
news:9CC68E6E-1380-47A8-969C-DF9AE204C3F3@.microsoft.com...[vbcol=seagreen]
> Thanks. I tried to add more space to the database but I got the error msg
> below. What shall we do to resolve the issue?
> Server: Msg 1827, Level 16, State 2, Line 1
> CREATE/ALTER DATABASE failed because the resulting cumulative database size
> would exceed your licensed limit of 2048 MB per database.
> Extending database by 1024.00 MB on disk 'Primary'.
>
> "Tibor Karaszi" wrote:
|||Thank you very much. We are using standard edition. So we cannot rebuild
index with this lisence limit condition, right? Any workaround or
suggestions. We have clustered indexes so I still want to use rebuild index
function.
Regards
Bill
"Tibor Karaszi" wrote:
> Seems you are running MSDE, which has a max database size of 2GB data per database. You might want
> to use DBCC INDEXDEFRAG in stead of DBCC DBREINDEX:
> http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
> news:9CC68E6E-1380-47A8-969C-DF9AE204C3F3@.microsoft.com...
>
>
|||You wouldn't get this error if you are on standard edition. Are you sure you work against the right
instance of SQL Server. Can you check the licensing?
SELECT @.@.VERSION
Also, INDEXDEFRAG work just fine on nc indexes...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
news:DFF76B92-66DD-47F9-9D63-EADEFFFFE9B2@.microsoft.com...[vbcol=seagreen]
> Thank you very much. We are using standard edition. So we cannot rebuild
> index with this lisence limit condition, right? Any workaround or
> suggestions. We have clustered indexes so I still want to use rebuild index
> function.
> Regards
> Bill
> "Tibor Karaszi" wrote:
|||We have having the same exact problem. There is 30+ GB Free on the Drive
(Dynamic Disk, by the way). Database is set to unrestricted file growth.
Everything looks fine with the configuration of the sql database...however,
the database thinks there is no free space on the drive. Very wierd!!
Database is just shy of 5gb, but General Tab says 0.00Mb Free. Looks like
ill be calling MS soon!!
Dale
txskibum@.hotmail.com
"Tibor Karaszi" wrote:
> You wouldn't get this error if you are on standard edition. Are you sure you work against the right
> instance of SQL Server. Can you check the licensing?
> SELECT @.@.VERSION
> Also, INDEXDEFRAG work just fine on nc indexes...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
> news:DFF76B92-66DD-47F9-9D63-EADEFFFFE9B2@.microsoft.com...
>
>
|||> We have having the same exact problem.
Same as which problem? The thread has several issues discussed. Anyhow, you don't seem to get the
"MSDE" error message, as MSDE has max size of 2GB for data storage per database.
Are you getting error messages or are you only worried that EM say 0MB free? Remember that EM
reports free space *inside* the database files, without regard to free space on disk SQL Server
pre-allocates storage.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Dale" <Dale@.discussions.microsoft.com> wrote in message
news:922A620C-5AEE-4DD6-93BD-36645F72B881@.microsoft.com...[vbcol=seagreen]
> We have having the same exact problem. There is 30+ GB Free on the Drive
> (Dynamic Disk, by the way). Database is set to unrestricted file growth.
> Everything looks fine with the configuration of the sql database...however,
> the database thinks there is no free space on the drive. Very wierd!!
> Database is just shy of 5gb, but General Tab says 0.00Mb Free. Looks like
> ill be calling MS soon!!
> Dale
> txskibum@.hotmail.com
>
> "Tibor Karaszi" wrote:
allocated plenty of space for the primary filegroup and "auto" grow by 10% is
set. There are plenty of space in the drive. Please help me to resolve this
isse.
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC SQL
Server Driver][SQL Server]Could not allocate space for object '(SYSTEM table
id: -739371491)' in database 'FDIT' because the 'PRIMARY' filegroup is full.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
terminated.
Hi
Rather grow the DB to the correct size before you do such an operation.
Yes, autogrow may be on, but autogrowing a e.g. 20Gb DB by 10% does take a
while. During this time, the DBCC command will find the DB full and abort.
The DBCC command does not know that more space will be available shortly.
Regards
Mike
"Bill Little" wrote:
> I got the error msg below while rebuilding index for a large table. I have
> allocated plenty of space for the primary filegroup and "auto" grow by 10% is
> set. There are plenty of space in the drive. Please help me to resolve this
> isse.
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC SQL
> Server Driver][SQL Server]Could not allocate space for object '(SYSTEM table
> id: -739371491)' in database 'FDIT' because the 'PRIMARY' filegroup is full.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> terminated.
>
|||Thank you very much. I have set the space for the primary filegroup the same
size as the size of the whole database. Do I still need to increase the size
or the size of the log file?
Regards
Bill
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> Rather grow the DB to the correct size before you do such an operation.
> Yes, autogrow may be on, but autogrowing a e.g. 20Gb DB by 10% does take a
> while. During this time, the DBCC command will find the DB full and abort.
> The DBCC command does not know that more space will be available shortly.
> Regards
> Mike
> "Bill Little" wrote:
|||> I have set the space for the primary filegroup the same
> size as the size of the whole database.
Whether that is enough or not is impossible to answer without more information. If the table has a
clustered index, rebuilding is essentially copying the table to a new location and after the copy
removing the old table. I.e., you need as much free space as the size of the table (plus some ore)
on the file group where the table resides.
> Do I still need to increase the size
> or the size of the log file?
Rebuilding is a type of modification. If you are in FULL recovery mode, the amount of data moves (as
explained above) will be logged to the transaction log.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
news:71BB84AB-C440-480F-BD09-090A7E18C13B@.microsoft.com...[vbcol=seagreen]
> Thank you very much. I have set the space for the primary filegroup the same
> size as the size of the whole database. Do I still need to increase the size
> or the size of the log file?
> Regards
> Bill
> "Mike Epprecht (SQL MVP)" wrote:
|||Thanks. I tried to add more space to the database but I got the error msg
below. What shall we do to resolve the issue?
Server: Msg 1827, Level 16, State 2, Line 1
CREATE/ALTER DATABASE failed because the resulting cumulative database size
would exceed your licensed limit of 2048 MB per database.
Extending database by 1024.00 MB on disk 'Primary'.
"Tibor Karaszi" wrote:
> Whether that is enough or not is impossible to answer without more information. If the table has a
> clustered index, rebuilding is essentially copying the table to a new location and after the copy
> removing the old table. I.e., you need as much free space as the size of the table (plus some ore)
> on the file group where the table resides.
>
> Rebuilding is a type of modification. If you are in FULL recovery mode, the amount of data moves (as
> explained above) will be logged to the transaction log.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
> news:71BB84AB-C440-480F-BD09-090A7E18C13B@.microsoft.com...
>
>
|||Seems you are running MSDE, which has a max database size of 2GB data per database. You might want
to use DBCC INDEXDEFRAG in stead of DBCC DBREINDEX:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
news:9CC68E6E-1380-47A8-969C-DF9AE204C3F3@.microsoft.com...[vbcol=seagreen]
> Thanks. I tried to add more space to the database but I got the error msg
> below. What shall we do to resolve the issue?
> Server: Msg 1827, Level 16, State 2, Line 1
> CREATE/ALTER DATABASE failed because the resulting cumulative database size
> would exceed your licensed limit of 2048 MB per database.
> Extending database by 1024.00 MB on disk 'Primary'.
>
> "Tibor Karaszi" wrote:
|||Thank you very much. We are using standard edition. So we cannot rebuild
index with this lisence limit condition, right? Any workaround or
suggestions. We have clustered indexes so I still want to use rebuild index
function.
Regards
Bill
"Tibor Karaszi" wrote:
> Seems you are running MSDE, which has a max database size of 2GB data per database. You might want
> to use DBCC INDEXDEFRAG in stead of DBCC DBREINDEX:
> http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
> news:9CC68E6E-1380-47A8-969C-DF9AE204C3F3@.microsoft.com...
>
>
|||You wouldn't get this error if you are on standard edition. Are you sure you work against the right
instance of SQL Server. Can you check the licensing?
SELECT @.@.VERSION
Also, INDEXDEFRAG work just fine on nc indexes...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
news:DFF76B92-66DD-47F9-9D63-EADEFFFFE9B2@.microsoft.com...[vbcol=seagreen]
> Thank you very much. We are using standard edition. So we cannot rebuild
> index with this lisence limit condition, right? Any workaround or
> suggestions. We have clustered indexes so I still want to use rebuild index
> function.
> Regards
> Bill
> "Tibor Karaszi" wrote:
|||We have having the same exact problem. There is 30+ GB Free on the Drive
(Dynamic Disk, by the way). Database is set to unrestricted file growth.
Everything looks fine with the configuration of the sql database...however,
the database thinks there is no free space on the drive. Very wierd!!
Database is just shy of 5gb, but General Tab says 0.00Mb Free. Looks like
ill be calling MS soon!!
Dale
txskibum@.hotmail.com
"Tibor Karaszi" wrote:
> You wouldn't get this error if you are on standard edition. Are you sure you work against the right
> instance of SQL Server. Can you check the licensing?
> SELECT @.@.VERSION
> Also, INDEXDEFRAG work just fine on nc indexes...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
> news:DFF76B92-66DD-47F9-9D63-EADEFFFFE9B2@.microsoft.com...
>
>
|||> We have having the same exact problem.
Same as which problem? The thread has several issues discussed. Anyhow, you don't seem to get the
"MSDE" error message, as MSDE has max size of 2GB for data storage per database.
Are you getting error messages or are you only worried that EM say 0MB free? Remember that EM
reports free space *inside* the database files, without regard to free space on disk SQL Server
pre-allocates storage.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Dale" <Dale@.discussions.microsoft.com> wrote in message
news:922A620C-5AEE-4DD6-93BD-36645F72B881@.microsoft.com...[vbcol=seagreen]
> We have having the same exact problem. There is 30+ GB Free on the Drive
> (Dynamic Disk, by the way). Database is set to unrestricted file growth.
> Everything looks fine with the configuration of the sql database...however,
> the database thinks there is no free space on the drive. Very wierd!!
> Database is just shy of 5gb, but General Tab says 0.00Mb Free. Looks like
> ill be calling MS soon!!
> Dale
> txskibum@.hotmail.com
>
> "Tibor Karaszi" wrote:
Sunday, March 11, 2012
Can Indexes LIve on another server?
I know I can split up data and indexes onto different file groups, but can I
then move the index file group to another server? if so, what are the pros &
cons?
thanks in advance!!Yes, you can certainly have a filegroup which lives on another physical
resource. There are two very strong di
vantages to this:
(a) network latency unless you are on gig+ ethernet...
(b) reliance on multiple machines to be up for one server to be considered
"available." How do you plan to recycle machines after applying a service
pack or hotfix?
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:E7BF3CB9-6974-4BBD-AD9F-94D81D04EC4C@.microsoft.com...
>I know I can split up data and indexes onto different file groups, but can
>I
> then move the index file group to another server? if so, what are the pros
> &
> cons?
> thanks in advance!!|||what are the performance gains/issues?
I am looking at splitting up a 1 tb data file into a 300gb data file and a
700gb index file. I have a gb network, but I am worried about administrating
mutiple machines.
"Aaron Bertrand [SQL Server MVP]" wrote:
> Yes, you can certainly have a filegroup which lives on another physical
> resource. There are two very strong divantages to this:
> (a) network latency unless you are on gig+ ethernet...
> (b) reliance on multiple machines to be up for one server to be considered
> "available." How do you plan to recycle machines after applying a service
> pack or hotfix?
>
>
> "Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
> news:E7BF3CB9-6974-4BBD-AD9F-94D81D04EC4C@.microsoft.com...
>
>|||Why not just additional drive(s) to the same server?
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:29753642-4A9E-4DC3-975B-578EBFD963AC@.microsoft.com...
> what are the performance gains/issues?
> I am looking at splitting up a 1 tb data file into a 300gb data file and a
> 700gb index file. I have a gb network, but I am worried about
> administrating
> mutiple machines.
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||Incorrect. olny local drives can be used for this purpose or special SAN
that you probably don't have.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uMTRslkrFHA.1028@.TK2MSFTNGP12.phx.gbl...
> Yes, you can certainly have a filegroup which lives on another physical
> resource. There are two very strong divantages to this:
> (a) network latency unless you are on gig+ ethernet...
> (b) reliance on multiple machines to be up for one server to be considered
> "available." How do you plan to recycle machines after applying a service
> pack or hotfix?
>
>
> "Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
> news:E7BF3CB9-6974-4BBD-AD9F-94D81D04EC4C@.microsoft.com...
>|||And only enterprize edition supports VIA SANs. others will work if a SAN
drive is presented by SAN driver as local.
however another tip here is:
BOL
Note When running on Windows NT, SQL Server performance can be improved
further if the databases are created on disks formatted using NTFS and,
specifically, 64-KB extent sizes. In Windows 2000, setting the file system
cluster size to 64-KB extent size using the /A format option will also
improve performance. For more information about formatting an NTFS disk, see
the Windows NT or Windows 2000 documentation.
"Farmer" <someone@.somewhere.com> wrote in message
news:ez99%23ilrFHA.3068@.TK2MSFTNGP14.phx.gbl...
> Incorrect. olny local drives can be used for this purpose or special SAN
> that you probably don't have.
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:uMTRslkrFHA.1028@.TK2MSFTNGP12.phx.gbl...
>|||Am I reading that you have 700gb of indexing on 300gb of tables?
Consider if you are indexing on too many columns:
http://msdn.microsoft.com/library/d.../>
indexes.asp
Also, look into the extent of index fragmentation:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:29753642-4A9E-4DC3-975B-578EBFD963AC@.microsoft.com...
> what are the performance gains/issues?
> I am looking at splitting up a 1 tb data file into a 300gb data file and a
> 700gb index file. I have a gb network, but I am worried about
> administrating
> mutiple machines.
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||> Incorrect. olny local drives can be used for this purpose or special SAN
> that you probably don't have.
Uh, we have multiple Clariions. But thanks for the tip.|||And here's the full story on creating database files on network drives.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Farmer" <someone@.somewhere.com> wrote in message news:ez99%23ilrFHA.3068@.TK2MSFTNGP14.phx.
gbl...
> Incorrect. olny local drives can be used for this purpose or special SAN
> that you probably don't have.
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in messag
e
> news:uMTRslkrFHA.1028@.TK2MSFTNGP12.phx.gbl...
>|||Well, we are currently delivering on Del 2650's with a powervault loaded wit
h
16 - 15k 73gb hard drives configured into one raid-5 drive. The search times
on this 1tb table are very fast, but I am looking to scale it out. I would
like to seperate the indexes and the data onto different disks, but then
started wondering if I could store the data on one server, and say store the
indexes on 3-4 other servers that point to the data servers.
My research has found that 70-80% of query times is caught up in the indexes
looking for the right keys to use to go get the data with. I have 26 indexes
built on my table that covers 95% of the queries uses according to our log
files. I would like to spread out the work done in the index search to a fe
w
specialized servers if possible. I dont think that MS can do this, but I can
probably build something myself that could.
thanks!!
"Aaron Bertrand [SQL Server MVP]" wrote:
> Uh, we have multiple Clariions. But thanks for the tip.
>
>
then move the index file group to another server? if so, what are the pros &
cons?
thanks in advance!!Yes, you can certainly have a filegroup which lives on another physical
resource. There are two very strong di
vantages to this:(a) network latency unless you are on gig+ ethernet...
(b) reliance on multiple machines to be up for one server to be considered
"available." How do you plan to recycle machines after applying a service
pack or hotfix?
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:E7BF3CB9-6974-4BBD-AD9F-94D81D04EC4C@.microsoft.com...
>I know I can split up data and indexes onto different file groups, but can
>I
> then move the index file group to another server? if so, what are the pros
> &
> cons?
> thanks in advance!!|||what are the performance gains/issues?
I am looking at splitting up a 1 tb data file into a 300gb data file and a
700gb index file. I have a gb network, but I am worried about administrating
mutiple machines.
"Aaron Bertrand [SQL Server MVP]" wrote:
> Yes, you can certainly have a filegroup which lives on another physical
> resource. There are two very strong di
vantages to this:> (a) network latency unless you are on gig+ ethernet...
> (b) reliance on multiple machines to be up for one server to be considered
> "available." How do you plan to recycle machines after applying a service
> pack or hotfix?
>
>
> "Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
> news:E7BF3CB9-6974-4BBD-AD9F-94D81D04EC4C@.microsoft.com...
>
>|||Why not just additional drive(s) to the same server?
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:29753642-4A9E-4DC3-975B-578EBFD963AC@.microsoft.com...
> what are the performance gains/issues?
> I am looking at splitting up a 1 tb data file into a 300gb data file and a
> 700gb index file. I have a gb network, but I am worried about
> administrating
> mutiple machines.
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||Incorrect. olny local drives can be used for this purpose or special SAN
that you probably don't have.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uMTRslkrFHA.1028@.TK2MSFTNGP12.phx.gbl...
> Yes, you can certainly have a filegroup which lives on another physical
> resource. There are two very strong di
vantages to this:> (a) network latency unless you are on gig+ ethernet...
> (b) reliance on multiple machines to be up for one server to be considered
> "available." How do you plan to recycle machines after applying a service
> pack or hotfix?
>
>
> "Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
> news:E7BF3CB9-6974-4BBD-AD9F-94D81D04EC4C@.microsoft.com...
>|||And only enterprize edition supports VIA SANs. others will work if a SAN
drive is presented by SAN driver as local.
however another tip here is:
BOL
Note When running on Windows NT, SQL Server performance can be improved
further if the databases are created on disks formatted using NTFS and,
specifically, 64-KB extent sizes. In Windows 2000, setting the file system
cluster size to 64-KB extent size using the /A format option will also
improve performance. For more information about formatting an NTFS disk, see
the Windows NT or Windows 2000 documentation.
"Farmer" <someone@.somewhere.com> wrote in message
news:ez99%23ilrFHA.3068@.TK2MSFTNGP14.phx.gbl...
> Incorrect. olny local drives can be used for this purpose or special SAN
> that you probably don't have.
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:uMTRslkrFHA.1028@.TK2MSFTNGP12.phx.gbl...
>|||Am I reading that you have 700gb of indexing on 300gb of tables?
Consider if you are indexing on too many columns:
http://msdn.microsoft.com/library/d.../>
indexes.asp
Also, look into the extent of index fragmentation:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:29753642-4A9E-4DC3-975B-578EBFD963AC@.microsoft.com...
> what are the performance gains/issues?
> I am looking at splitting up a 1 tb data file into a 300gb data file and a
> 700gb index file. I have a gb network, but I am worried about
> administrating
> mutiple machines.
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||> Incorrect. olny local drives can be used for this purpose or special SAN
> that you probably don't have.
Uh, we have multiple Clariions. But thanks for the tip.|||And here's the full story on creating database files on network drives.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Farmer" <someone@.somewhere.com> wrote in message news:ez99%23ilrFHA.3068@.TK2MSFTNGP14.phx.
gbl...
> Incorrect. olny local drives can be used for this purpose or special SAN
> that you probably don't have.
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in messag
e
> news:uMTRslkrFHA.1028@.TK2MSFTNGP12.phx.gbl...
>|||Well, we are currently delivering on Del 2650's with a powervault loaded wit
h
16 - 15k 73gb hard drives configured into one raid-5 drive. The search times
on this 1tb table are very fast, but I am looking to scale it out. I would
like to seperate the indexes and the data onto different disks, but then
started wondering if I could store the data on one server, and say store the
indexes on 3-4 other servers that point to the data servers.
My research has found that 70-80% of query times is caught up in the indexes
looking for the right keys to use to go get the data with. I have 26 indexes
built on my table that covers 95% of the queries uses according to our log
files. I would like to spread out the work done in the index search to a fe
w
specialized servers if possible. I dont think that MS can do this, but I can
probably build something myself that could.
thanks!!
"Aaron Bertrand [SQL Server MVP]" wrote:
> Uh, we have multiple Clariions. But thanks for the tip.
>
>
Friday, February 24, 2012
Can I tell how often the clustered index pages are spliting?
Greetings
For performance reasons I am trying to determine how often the pages in
the index split due to inserts. Are there any records in the 'sys' tables,
etc. Thanx"Robert" <stop.spam@.boeing.com> wrote in message news:<Hsw479.Mnx@.news.boeing.com>...
> Greetings
> For performance reasons I am trying to determine how often the pages in
> the index split due to inserts. Are there any records in the 'sys' tables,
> etc. Thanx
I don't think this is stored in any system table, but you can use the
Performance Monitor counter SQLServer:AccessMethods, Page Splits/sec
to capture the information.
Simon|||Many, many thanx. I found it.
"Simon Hayes" <sql@.hayes.ch> wrote in message
news:60cd0137.0402102355.4236cb34@.posting.google.c om...
> "Robert" <stop.spam@.boeing.com> wrote in message
news:<Hsw479.Mnx@.news.boeing.com>...
> > Greetings
> > For performance reasons I am trying to determine how often the pages
in
> > the index split due to inserts. Are there any records in the 'sys'
tables,
> > etc. Thanx
> I don't think this is stored in any system table, but you can use the
> Performance Monitor counter SQLServer:AccessMethods, Page Splits/sec
> to capture the information.
> Simon
Sunday, February 12, 2012
Can I make my clustered index scan cheaper?
Hi,
I have a query here that I would like to make cheaper in terms of query
cost. Here's the query:
________________________________________
__________________________
Select distinct
vw.CustomerID,
vw.BranchID,
vw.SourceOfBusiness ,
vw.SpkCustNo,
vw.Surname,
vw.Initials,
vw.Incode,
vw.Outcode,
vw.Updated
>From tbl_Insurer i
Inner join tbl_SpkInsurer si on i.ID = si.InsurerID
Inner join tbl_Scheme s on si.ID = s.SpkInsurerID
Inner join vw_SearchCustomerPolicy vw on vw.SchemeID = s.ID
Where i.id = @.InsurerID
and convert(datetime, convert(char(11), vw.RenewalDate, 103), 103) >=
@.StartDate
and (@.PolicyRef is null or vw.PolicyRef = @.PolicyRef)
and (@.ShowSSU = 1 or vw.BranchID <> @.SSUID)
and (@.BranchID is null or vw.BranchID = @.BranchID)
and (@.SpkCustNo is null or vw.SpkCustNo = @.SpkCustNo)
and (@.Surname is null or vw.Surname like @.Surname)
and (@.Initials is null or vw.Initials = @.Initials)
and (@.Incode is null or vw.Incode = @.Incode)
and (@.Outcode is null or vw.Outcode = @.Outcode)
and (@.SpkPolNo is null or vw.SpkPolNo = @.SpkPolNo)
Order by vw.Surname, vw.Initials, vw.BranchID
________________________________________
__________________________
This takes 14 seconds to return 25 or so rows, I would like to knock
the return time down to under 5 seconds. When I display the Execution
Plan, I can see that the most expensive item is a clustered index scan
on the vw_SearchCustomerPolicy (this is an indexed view) this is 51%.
Can I know alter my query to reduce that cost?
Thanks
qhWhat is the clustered index?
A clustered index s
is faster than a scan but relies on only looking
at a portion of the index.
If the renewaldate were the clustered index that may help.
Martin
Scott wrote:
> Hi,
> I have a query here that I would like to make cheaper in terms of query
> cost. Here's the query:
> ________________________________________
__________________________
> Select distinct
> vw.CustomerID,
> vw.BranchID,
> vw.SourceOfBusiness ,
> vw.SpkCustNo,
> vw.Surname,
> vw.Initials,
> vw.Incode,
> vw.Outcode,
> vw.Updated
> Inner join tbl_SpkInsurer si on i.ID = si.InsurerID
> Inner join tbl_Scheme s on si.ID = s.SpkInsurerID
> Inner join vw_SearchCustomerPolicy vw on vw.SchemeID = s.ID
> Where i.id = @.InsurerID
> and convert(datetime, convert(char(11), vw.RenewalDate, 103), 103) >=
> @.StartDate
> and (@.PolicyRef is null or vw.PolicyRef = @.PolicyRef)
> and (@.ShowSSU = 1 or vw.BranchID <> @.SSUID)
> and (@.BranchID is null or vw.BranchID = @.BranchID)
> and (@.SpkCustNo is null or vw.SpkCustNo = @.SpkCustNo)
> and (@.Surname is null or vw.Surname like @.Surname)
> and (@.Initials is null or vw.Initials = @.Initials)
> and (@.Incode is null or vw.Incode = @.Incode)
> and (@.Outcode is null or vw.Outcode = @.Outcode)
> and (@.SpkPolNo is null or vw.SpkPolNo = @.SpkPolNo)
> Order by vw.Surname, vw.Initials, vw.BranchID
> ________________________________________
__________________________
> This takes 14 seconds to return 25 or so rows, I would like to knock
> the return time down to under 5 seconds. When I display the Execution
> Plan, I can see that the most expensive item is a clustered index scan
> on the vw_SearchCustomerPolicy (this is an indexed view) this is 51%.
>
> Can I know alter my query to reduce that cost?
>
> Thanks
> qh
>|||Martin Selway wrote:
> What is the clustered index?
The clustered index on the view is PolicyID. I have read that because
I am doing a search for null for every element in the where clause
these aren't picked up by the clustered index.
I could be wrong though...
Thanks
qh|||Don't do this...
convert(datetime, convert(char(11), vw.RenewalDate, 103), 103)
Do the convert on @.Start instead.
The convert will negate useful usage of an index (instead a full index scan
at best).
It looks like a query built from a search, i'd recommend building it up
using dynamic SQL so you don't have this...
and (@.Surname is null or vw.Surname like @.Surname)
but instead have something that can use an index properly...
and vw.Surname like @.Surname
You can then build up some nice indexes based on popular inputs from the
users.
That will significantly improve the performance of the query.
To prevent injection, use parameterisation and sp_executesql, for
instance...
declare @.nsql nvarchar(4000)
set @.nsql = 'select.... where vw.Surname like @.Surname'
exec sp_executesql @.nsql,
N'@.Surname varchar(...),
@.Surname
Hope that helps.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Scott" <quackhandle1975@.yahoo.co.uk> wrote in message
news:1129713424.015770.212820@.o13g2000cwo.googlegroups.com...
> Hi,
> I have a query here that I would like to make cheaper in terms of query
> cost. Here's the query:
> ________________________________________
__________________________
> Select distinct
> vw.CustomerID,
> vw.BranchID,
> vw.SourceOfBusiness ,
> vw.SpkCustNo,
> vw.Surname,
> vw.Initials,
> vw.Incode,
> vw.Outcode,
> vw.Updated
> Inner join tbl_SpkInsurer si on i.ID = si.InsurerID
> Inner join tbl_Scheme s on si.ID = s.SpkInsurerID
> Inner join vw_SearchCustomerPolicy vw on vw.SchemeID = s.ID
> Where i.id = @.InsurerID
> and convert(datetime, convert(char(11), vw.RenewalDate, 103), 103) >=
> @.StartDate
> and (@.PolicyRef is null or vw.PolicyRef = @.PolicyRef)
> and (@.ShowSSU = 1 or vw.BranchID <> @.SSUID)
> and (@.BranchID is null or vw.BranchID = @.BranchID)
> and (@.SpkCustNo is null or vw.SpkCustNo = @.SpkCustNo)
> and (@.Surname is null or vw.Surname like @.Surname)
> and (@.Initials is null or vw.Initials = @.Initials)
> and (@.Incode is null or vw.Incode = @.Incode)
> and (@.Outcode is null or vw.Outcode = @.Outcode)
> and (@.SpkPolNo is null or vw.SpkPolNo = @.SpkPolNo)
> Order by vw.Surname, vw.Initials, vw.BranchID
> ________________________________________
__________________________
> This takes 14 seconds to return 25 or so rows, I would like to knock
> the return time down to under 5 seconds. When I display the Execution
> Plan, I can see that the most expensive item is a clustered index scan
> on the vw_SearchCustomerPolicy (this is an indexed view) this is 51%.
>
> Can I know alter my query to reduce that cost?
>
> Thanks
> qh
>|||Scott wrote:
> Hi,
> I have a query here that I would like to make cheaper in terms of
> query cost. Here's the query:
> ________________________________________
__________________________
> Select distinct
> vw.CustomerID,
> vw.BranchID,
> vw.SourceOfBusiness ,
> vw.SpkCustNo,
> vw.Surname,
> vw.Initials,
> vw.Incode,
> vw.Outcode,
> vw.Updated
> Inner join tbl_SpkInsurer si on i.ID = si.InsurerID
> Inner join tbl_Scheme s on si.ID = s.SpkInsurerID
> Inner join vw_SearchCustomerPolicy vw on vw.SchemeID = s.ID
> Where i.id = @.InsurerID
> and convert(datetime, convert(char(11), vw.RenewalDate, 103), 103) >=
> @.StartDate
> and (@.PolicyRef is null or vw.PolicyRef = @.PolicyRef)
> and (@.ShowSSU = 1 or vw.BranchID <> @.SSUID)
> and (@.BranchID is null or vw.BranchID = @.BranchID)
> and (@.SpkCustNo is null or vw.SpkCustNo = @.SpkCustNo)
> and (@.Surname is null or vw.Surname like @.Surname)
> and (@.Initials is null or vw.Initials = @.Initials)
> and (@.Incode is null or vw.Incode = @.Incode)
> and (@.Outcode is null or vw.Outcode = @.Outcode)
> and (@.SpkPolNo is null or vw.SpkPolNo = @.SpkPolNo)
> Order by vw.Surname, vw.Initials, vw.BranchID
> ________________________________________
__________________________
> This takes 14 seconds to return 25 or so rows, I would like to knock
> the return time down to under 5 seconds. When I display the Execution
> Plan, I can see that the most expensive item is a clustered index scan
> on the vw_SearchCustomerPolicy (this is an indexed view) this is 51%.
You can create a text version of the plan with QA so we can have a look.
> Can I know alter my query to reduce that cost?
What indexes are in place? It seems all your criteria are on the view so
the view definition and the other DDL would be interesting to look at.
Some other remarks:
- you use like, that's usually slow especially if used with a leading
wildcard
- you convert vw.RenewalDate back and forth and then compare it. Try to
find a solution where @.StartDate
is converted instead or where @.StartDate is adjusted so it can be used
directly.
- do you have proper indexes on the join columns in place?
Kind regards
robert|||Never use functions on columns. That leads to table scan/index scan and inde
x
s is faster.
--
Regards
R.D
--Knowledge gets doubled when shared
"Tony Rogerson" wrote:
> Don't do this...
> convert(datetime, convert(char(11), vw.RenewalDate, 103), 103)
> Do the convert on @.Start instead.
> The convert will negate useful usage of an index (instead a full index sca
n
> at best).
> It looks like a query built from a search, i'd recommend building it up
> using dynamic SQL so you don't have this...
> and (@.Surname is null or vw.Surname like @.Surname)
> but instead have something that can use an index properly...
> and vw.Surname like @.Surname
> You can then build up some nice indexes based on popular inputs from the
> users.
> That will significantly improve the performance of the query.
> To prevent injection, use parameterisation and sp_executesql, for
> instance...
> declare @.nsql nvarchar(4000)
> set @.nsql = 'select.... where vw.Surname like @.Surname'
> exec sp_executesql @.nsql,
> N'@.Surname varchar(...),
> @.Surname
> Hope that helps.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Scott" <quackhandle1975@.yahoo.co.uk> wrote in message
> news:1129713424.015770.212820@.o13g2000cwo.googlegroups.com...
>
>|||Cheers for all the replies. Tony R. managed to spot that fact that this
query used to be built using dynamic sql however the powers that be
came over all security conscious and said it had to be built this way.
Thanks Again
Scott|||That's not strictly true.
You should think carefully about using a function on a column that you've
indexed (the access path), you can use a function on the value column...
Index on RenewalDate...
where RenewalDate = datediff( day, -1, myothercolumn )
The above is fine, and the index will be used efficiently.
You can also use functions to good effect in the SELECT clause and ORDER
clause.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"R.D" <RD@.discussions.microsoft.com> wrote in message
news:6C2CF2D1-4BDB-4634-A8E5-8F477FC14378@.microsoft.com...
> Never use functions on columns. That leads to table scan/index scan and
> index
> s is faster.
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "Tony Rogerson" wrote:
>|||Bizzarre,
They haven't really understood how injection happens then which is even more
scary!
I hope they are using the command object in the app code rather than
concatenating a string together and executing it.
I'd go back to the powers at be and tell them in order to get good
performance and scalability you need to either write out a ton of IF ELSE
statements or use safe, parameterised dynamic SQL.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Quackhandle" <quackhandle1975@.yahoo.co.uk> wrote in message
news:1129718265.957237.248090@.g47g2000cwa.googlegroups.com...
> Cheers for all the replies. Tony R. managed to spot that fact that this
> query used to be built using dynamic sql however the powers that be
> came over all security conscious and said it had to be built this way.
> Thanks Again
> Scott
>|||This is the view:
CREATE VIEW dbo.vw_SearchCustomerPolicy WITH SCHEMABINDING
AS
SELECT
c.ID AS CustomerID,
c.BranchID,
c.SourceOfBusiness,
c.SpkCustNo,
c.Surname,
c.Initials,
c.Incode,
c.Outcode,
c.Updated,
p.ID AS PolicyID,
p.PolicyRef,
p.SpkPolNo,
p.RenewalDate,
p.SchemeID
FROM dbo.tbl_CA_Customer c
INNER JOIN dbo.tbl_CA_Policy p ON p.CA_CustomerID = c.ID
And this is the text version of the execution plan.
Regards,
qh
I have a query here that I would like to make cheaper in terms of query
cost. Here's the query:
________________________________________
__________________________
Select distinct
vw.CustomerID,
vw.BranchID,
vw.SourceOfBusiness ,
vw.SpkCustNo,
vw.Surname,
vw.Initials,
vw.Incode,
vw.Outcode,
vw.Updated
>From tbl_Insurer i
Inner join tbl_SpkInsurer si on i.ID = si.InsurerID
Inner join tbl_Scheme s on si.ID = s.SpkInsurerID
Inner join vw_SearchCustomerPolicy vw on vw.SchemeID = s.ID
Where i.id = @.InsurerID
and convert(datetime, convert(char(11), vw.RenewalDate, 103), 103) >=
@.StartDate
and (@.PolicyRef is null or vw.PolicyRef = @.PolicyRef)
and (@.ShowSSU = 1 or vw.BranchID <> @.SSUID)
and (@.BranchID is null or vw.BranchID = @.BranchID)
and (@.SpkCustNo is null or vw.SpkCustNo = @.SpkCustNo)
and (@.Surname is null or vw.Surname like @.Surname)
and (@.Initials is null or vw.Initials = @.Initials)
and (@.Incode is null or vw.Incode = @.Incode)
and (@.Outcode is null or vw.Outcode = @.Outcode)
and (@.SpkPolNo is null or vw.SpkPolNo = @.SpkPolNo)
Order by vw.Surname, vw.Initials, vw.BranchID
________________________________________
__________________________
This takes 14 seconds to return 25 or so rows, I would like to knock
the return time down to under 5 seconds. When I display the Execution
Plan, I can see that the most expensive item is a clustered index scan
on the vw_SearchCustomerPolicy (this is an indexed view) this is 51%.
Can I know alter my query to reduce that cost?
Thanks
qhWhat is the clustered index?
A clustered index s
is faster than a scan but relies on only lookingat a portion of the index.
If the renewaldate were the clustered index that may help.
Martin
Scott wrote:
> Hi,
> I have a query here that I would like to make cheaper in terms of query
> cost. Here's the query:
> ________________________________________
__________________________
> Select distinct
> vw.CustomerID,
> vw.BranchID,
> vw.SourceOfBusiness ,
> vw.SpkCustNo,
> vw.Surname,
> vw.Initials,
> vw.Incode,
> vw.Outcode,
> vw.Updated
> Inner join tbl_SpkInsurer si on i.ID = si.InsurerID
> Inner join tbl_Scheme s on si.ID = s.SpkInsurerID
> Inner join vw_SearchCustomerPolicy vw on vw.SchemeID = s.ID
> Where i.id = @.InsurerID
> and convert(datetime, convert(char(11), vw.RenewalDate, 103), 103) >=
> @.StartDate
> and (@.PolicyRef is null or vw.PolicyRef = @.PolicyRef)
> and (@.ShowSSU = 1 or vw.BranchID <> @.SSUID)
> and (@.BranchID is null or vw.BranchID = @.BranchID)
> and (@.SpkCustNo is null or vw.SpkCustNo = @.SpkCustNo)
> and (@.Surname is null or vw.Surname like @.Surname)
> and (@.Initials is null or vw.Initials = @.Initials)
> and (@.Incode is null or vw.Incode = @.Incode)
> and (@.Outcode is null or vw.Outcode = @.Outcode)
> and (@.SpkPolNo is null or vw.SpkPolNo = @.SpkPolNo)
> Order by vw.Surname, vw.Initials, vw.BranchID
> ________________________________________
__________________________
> This takes 14 seconds to return 25 or so rows, I would like to knock
> the return time down to under 5 seconds. When I display the Execution
> Plan, I can see that the most expensive item is a clustered index scan
> on the vw_SearchCustomerPolicy (this is an indexed view) this is 51%.
>
> Can I know alter my query to reduce that cost?
>
> Thanks
> qh
>|||Martin Selway wrote:
> What is the clustered index?
The clustered index on the view is PolicyID. I have read that because
I am doing a search for null for every element in the where clause
these aren't picked up by the clustered index.
I could be wrong though...
Thanks
qh|||Don't do this...
convert(datetime, convert(char(11), vw.RenewalDate, 103), 103)
Do the convert on @.Start instead.
The convert will negate useful usage of an index (instead a full index scan
at best).
It looks like a query built from a search, i'd recommend building it up
using dynamic SQL so you don't have this...
and (@.Surname is null or vw.Surname like @.Surname)
but instead have something that can use an index properly...
and vw.Surname like @.Surname
You can then build up some nice indexes based on popular inputs from the
users.
That will significantly improve the performance of the query.
To prevent injection, use parameterisation and sp_executesql, for
instance...
declare @.nsql nvarchar(4000)
set @.nsql = 'select.... where vw.Surname like @.Surname'
exec sp_executesql @.nsql,
N'@.Surname varchar(...),
@.Surname
Hope that helps.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Scott" <quackhandle1975@.yahoo.co.uk> wrote in message
news:1129713424.015770.212820@.o13g2000cwo.googlegroups.com...
> Hi,
> I have a query here that I would like to make cheaper in terms of query
> cost. Here's the query:
> ________________________________________
__________________________
> Select distinct
> vw.CustomerID,
> vw.BranchID,
> vw.SourceOfBusiness ,
> vw.SpkCustNo,
> vw.Surname,
> vw.Initials,
> vw.Incode,
> vw.Outcode,
> vw.Updated
> Inner join tbl_SpkInsurer si on i.ID = si.InsurerID
> Inner join tbl_Scheme s on si.ID = s.SpkInsurerID
> Inner join vw_SearchCustomerPolicy vw on vw.SchemeID = s.ID
> Where i.id = @.InsurerID
> and convert(datetime, convert(char(11), vw.RenewalDate, 103), 103) >=
> @.StartDate
> and (@.PolicyRef is null or vw.PolicyRef = @.PolicyRef)
> and (@.ShowSSU = 1 or vw.BranchID <> @.SSUID)
> and (@.BranchID is null or vw.BranchID = @.BranchID)
> and (@.SpkCustNo is null or vw.SpkCustNo = @.SpkCustNo)
> and (@.Surname is null or vw.Surname like @.Surname)
> and (@.Initials is null or vw.Initials = @.Initials)
> and (@.Incode is null or vw.Incode = @.Incode)
> and (@.Outcode is null or vw.Outcode = @.Outcode)
> and (@.SpkPolNo is null or vw.SpkPolNo = @.SpkPolNo)
> Order by vw.Surname, vw.Initials, vw.BranchID
> ________________________________________
__________________________
> This takes 14 seconds to return 25 or so rows, I would like to knock
> the return time down to under 5 seconds. When I display the Execution
> Plan, I can see that the most expensive item is a clustered index scan
> on the vw_SearchCustomerPolicy (this is an indexed view) this is 51%.
>
> Can I know alter my query to reduce that cost?
>
> Thanks
> qh
>|||Scott wrote:
> Hi,
> I have a query here that I would like to make cheaper in terms of
> query cost. Here's the query:
> ________________________________________
__________________________
> Select distinct
> vw.CustomerID,
> vw.BranchID,
> vw.SourceOfBusiness ,
> vw.SpkCustNo,
> vw.Surname,
> vw.Initials,
> vw.Incode,
> vw.Outcode,
> vw.Updated
> Inner join tbl_SpkInsurer si on i.ID = si.InsurerID
> Inner join tbl_Scheme s on si.ID = s.SpkInsurerID
> Inner join vw_SearchCustomerPolicy vw on vw.SchemeID = s.ID
> Where i.id = @.InsurerID
> and convert(datetime, convert(char(11), vw.RenewalDate, 103), 103) >=
> @.StartDate
> and (@.PolicyRef is null or vw.PolicyRef = @.PolicyRef)
> and (@.ShowSSU = 1 or vw.BranchID <> @.SSUID)
> and (@.BranchID is null or vw.BranchID = @.BranchID)
> and (@.SpkCustNo is null or vw.SpkCustNo = @.SpkCustNo)
> and (@.Surname is null or vw.Surname like @.Surname)
> and (@.Initials is null or vw.Initials = @.Initials)
> and (@.Incode is null or vw.Incode = @.Incode)
> and (@.Outcode is null or vw.Outcode = @.Outcode)
> and (@.SpkPolNo is null or vw.SpkPolNo = @.SpkPolNo)
> Order by vw.Surname, vw.Initials, vw.BranchID
> ________________________________________
__________________________
> This takes 14 seconds to return 25 or so rows, I would like to knock
> the return time down to under 5 seconds. When I display the Execution
> Plan, I can see that the most expensive item is a clustered index scan
> on the vw_SearchCustomerPolicy (this is an indexed view) this is 51%.
You can create a text version of the plan with QA so we can have a look.
> Can I know alter my query to reduce that cost?
What indexes are in place? It seems all your criteria are on the view so
the view definition and the other DDL would be interesting to look at.
Some other remarks:
- you use like, that's usually slow especially if used with a leading
wildcard
- you convert vw.RenewalDate back and forth and then compare it. Try to
find a solution where @.StartDate
is converted instead or where @.StartDate is adjusted so it can be used
directly.
- do you have proper indexes on the join columns in place?
Kind regards
robert|||Never use functions on columns. That leads to table scan/index scan and inde
x
s
is faster.--
Regards
R.D
--Knowledge gets doubled when shared
"Tony Rogerson" wrote:
> Don't do this...
> convert(datetime, convert(char(11), vw.RenewalDate, 103), 103)
> Do the convert on @.Start instead.
> The convert will negate useful usage of an index (instead a full index sca
n
> at best).
> It looks like a query built from a search, i'd recommend building it up
> using dynamic SQL so you don't have this...
> and (@.Surname is null or vw.Surname like @.Surname)
> but instead have something that can use an index properly...
> and vw.Surname like @.Surname
> You can then build up some nice indexes based on popular inputs from the
> users.
> That will significantly improve the performance of the query.
> To prevent injection, use parameterisation and sp_executesql, for
> instance...
> declare @.nsql nvarchar(4000)
> set @.nsql = 'select.... where vw.Surname like @.Surname'
> exec sp_executesql @.nsql,
> N'@.Surname varchar(...),
> @.Surname
> Hope that helps.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Scott" <quackhandle1975@.yahoo.co.uk> wrote in message
> news:1129713424.015770.212820@.o13g2000cwo.googlegroups.com...
>
>|||Cheers for all the replies. Tony R. managed to spot that fact that this
query used to be built using dynamic sql however the powers that be
came over all security conscious and said it had to be built this way.
Thanks Again
Scott|||That's not strictly true.
You should think carefully about using a function on a column that you've
indexed (the access path), you can use a function on the value column...
Index on RenewalDate...
where RenewalDate = datediff( day, -1, myothercolumn )
The above is fine, and the index will be used efficiently.
You can also use functions to good effect in the SELECT clause and ORDER
clause.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"R.D" <RD@.discussions.microsoft.com> wrote in message
news:6C2CF2D1-4BDB-4634-A8E5-8F477FC14378@.microsoft.com...
> Never use functions on columns. That leads to table scan/index scan and
> index
> s
is faster.> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "Tony Rogerson" wrote:
>|||Bizzarre,
They haven't really understood how injection happens then which is even more
scary!
I hope they are using the command object in the app code rather than
concatenating a string together and executing it.
I'd go back to the powers at be and tell them in order to get good
performance and scalability you need to either write out a ton of IF ELSE
statements or use safe, parameterised dynamic SQL.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Quackhandle" <quackhandle1975@.yahoo.co.uk> wrote in message
news:1129718265.957237.248090@.g47g2000cwa.googlegroups.com...
> Cheers for all the replies. Tony R. managed to spot that fact that this
> query used to be built using dynamic sql however the powers that be
> came over all security conscious and said it had to be built this way.
> Thanks Again
> Scott
>|||This is the view:
CREATE VIEW dbo.vw_SearchCustomerPolicy WITH SCHEMABINDING
AS
SELECT
c.ID AS CustomerID,
c.BranchID,
c.SourceOfBusiness,
c.SpkCustNo,
c.Surname,
c.Initials,
c.Incode,
c.Outcode,
c.Updated,
p.ID AS PolicyID,
p.PolicyRef,
p.SpkPolNo,
p.RenewalDate,
p.SchemeID
FROM dbo.tbl_CA_Customer c
INNER JOIN dbo.tbl_CA_Policy p ON p.CA_CustomerID = c.ID
And this is the text version of the execution plan.
Regards,
qh
Subscribe to:
Posts (Atom)