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: