DBCC DBREINDEX failure

[Rob]

When I run the following DBCC command to reindex all indexes (in most cases
only one: clustered) on a table...

DBCC DBREINDEX (<table_name>, '', 10)

It reports the following error:

Could not allocate space for object ... in database ... because the
'PRIMARY' filegroup is full.

This is strange as the primary filegroup, which consists of three physical
files spread across three physical drives, have ample space for the database
to grow. This had been verified by updating the stats and running
sp_spacesused.

The reindexing had originally been done through the once weekly DB
optimization job, but after repeated failures due to the log file being full,
I decided to separate the reindexing of all tables into specific groups, by
running them in batches, once a day over the week, with no batch repeated
more than once a week.

That seems to have resolved the log file full issue, but I still see the
above error when the reindexing occurs against larger tables.

The database has grown to twice its size since I've replace the Opt. job
with manual DBCC DBREINDEX.

The only DB options set on the database are:

trunc. log on chkpt. (SIMPLE recovery mode),
torn page detection,
auto create statistics, and
auto update statistics

Any ideas what I could be doing wrong here?

TIA.

[Rob]

Repeat post. My apologies... it seemed that my original post had gone into
limbo (timeout issue) so I had reposted.

Post by Rob
When I run the following DBCC command to reindex all indexes (in most cases
only one: clustered) on a table...
DBCC DBREINDEX (<table_name>, '', 10)
Could not allocate space for object ... in database ... because the
'PRIMARY' filegroup is full.
This is strange as the primary filegroup, which consists of three physical
files spread across three physical drives, have ample space for the database
to grow. This had been verified by updating the stats and running
sp_spacesused.
The reindexing had originally been done through the once weekly DB
optimization job, but after repeated failures due to the log file being full,
I decided to separate the reindexing of all tables into specific groups, by
running them in batches, once a day over the week, with no batch repeated
more than once a week.
That seems to have resolved the log file full issue, but I still see the
above error when the reindexing occurs against larger tables.
The database has grown to twice its size since I've replace the Opt. job
with manual DBCC DBREINDEX.
trunc. log on chkpt. (SIMPLE recovery mode),
torn page detection,
auto create statistics, and
auto update statistics
Any ideas what I could be doing wrong here?
TIA.

[AlterEgo]

Rob,

The reindex function will need data + (2 x Index space) to create an index.
You might want to examine the SORT_IN_TEMPDB option of the CREATE INDEX
statement, and look at dropping and recreating the indexes instead of the
DBCC DBREINDEX statement.

-- Bill

Post by Rob
Repeat post. My apologies... it seemed that my original post had gone into
limbo (timeout issue) so I had reposted.

Post by Rob
When I run the following DBCC command to reindex all indexes (in most cases
only one: clustered) on a table...
DBCC DBREINDEX (<table_name>, '', 10)
Could not allocate space for object ... in database ... because the
'PRIMARY' filegroup is full.
This is strange as the primary filegroup, which consists of three physical
files spread across three physical drives, have ample space for the database
to grow. This had been verified by updating the stats and running
sp_spacesused.
The reindexing had originally been done through the once weekly DB
optimization job, but after repeated failures due to the log file being full,
I decided to separate the reindexing of all tables into specific groups, by
running them in batches, once a day over the week, with no batch repeated
more than once a week.
That seems to have resolved the log file full issue, but I still see the
above error when the reindexing occurs against larger tables.
The database has grown to twice its size since I've replace the Opt. job
with manual DBCC DBREINDEX.
trunc. log on chkpt. (SIMPLE recovery mode),
torn page detection,
auto create statistics, and
auto update statistics
Any ideas what I could be doing wrong here?
TIA.

[Rob]

Thanks Bill.

I've been wondering if reindexing would do any good at all given that most
of my tables only have one clustered index. If it's clustered then there
shouldn't be any reason to drop and recreate the index, right... as data and
index pages are always kept in a sorted order.

Post by AlterEgo
Rob,
The reindex function will need data + (2 x Index space) to create an index.
You might want to examine the SORT_IN_TEMPDB option of the CREATE INDEX
statement, and look at dropping and recreating the indexes instead of the
DBCC DBREINDEX statement.
-- Bill

Post by Rob
Repeat post. My apologies... it seemed that my original post had gone into
limbo (timeout issue) so I had reposted.

Post by Rob
When I run the following DBCC command to reindex all indexes (in most cases
only one: clustered) on a table...
DBCC DBREINDEX (<table_name>, '', 10)
Could not allocate space for object ... in database ... because the
'PRIMARY' filegroup is full.
This is strange as the primary filegroup, which consists of three physical
files spread across three physical drives, have ample space for the database
to grow. This had been verified by updating the stats and running
sp_spacesused.
The reindexing had originally been done through the once weekly DB
optimization job, but after repeated failures due to the log file being full,
I decided to separate the reindexing of all tables into specific groups, by
running them in batches, once a day over the week, with no batch repeated
more than once a week.
That seems to have resolved the log file full issue, but I still see the
above error when the reindexing occurs against larger tables.
The database has grown to twice its size since I've replace the Opt. job
with manual DBCC DBREINDEX.
trunc. log on chkpt. (SIMPLE recovery mode),
torn page detection,
auto create statistics, and
auto update statistics
Any ideas what I could be doing wrong here?
TIA.

[Tibor Karaszi]

Post by Rob
as data and
index pages are always kept in a sorted order.

Logically, yes, the linked list is logically sorted. But as you follow the pointers in the linked
list, you can jump back and forth ion the disk. On other words, a clustered index can definitely be
fragmented. Whether or not you suffer from the fragmentation, and how much is another question:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Post by Rob
Thanks Bill.
I've been wondering if reindexing would do any good at all given that most
of my tables only have one clustered index. If it's clustered then there
shouldn't be any reason to drop and recreate the index, right... as data and
index pages are always kept in a sorted order.

Post by AlterEgo
Rob,
The reindex function will need data + (2 x Index space) to create an index.
You might want to examine the SORT_IN_TEMPDB option of the CREATE INDEX
statement, and look at dropping and recreating the indexes instead of the
DBCC DBREINDEX statement.
-- Bill

Post by Rob
Repeat post. My apologies... it seemed that my original post had gone into
limbo (timeout issue) so I had reposted.

Post by Rob
When I run the following DBCC command to reindex all indexes (in most cases
only one: clustered) on a table...
DBCC DBREINDEX (<table_name>, '', 10)
Could not allocate space for object ... in database ... because the
'PRIMARY' filegroup is full.
This is strange as the primary filegroup, which consists of three physical
files spread across three physical drives, have ample space for the database
to grow. This had been verified by updating the stats and running
sp_spacesused.
The reindexing had originally been done through the once weekly DB
optimization job, but after repeated failures due to the log file being full,
I decided to separate the reindexing of all tables into specific groups, by
running them in batches, once a day over the week, with no batch repeated
more than once a week.
That seems to have resolved the log file full issue, but I still see the
above error when the reindexing occurs against larger tables.
The database has grown to twice its size since I've replace the Opt. job
with manual DBCC DBREINDEX.
trunc. log on chkpt. (SIMPLE recovery mode),
torn page detection,
auto create statistics, and
auto update statistics
Any ideas what I could be doing wrong here?
TIA.

[Rob]

Thanks Tibor.

Assumming that we choose not to run reindexing, is there a way to reclaim
the space chewed up by the indexes, without dropping the indexes?

Post by Tibor Karaszi

Post by Rob
as data and
index pages are always kept in a sorted order.

Logically, yes, the linked list is logically sorted. But as you follow the pointers in the linked
list, you can jump back and forth ion the disk. On other words, a clustered index can definitely be
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Post by Rob
Thanks Bill.
I've been wondering if reindexing would do any good at all given that most
of my tables only have one clustered index. If it's clustered then there
shouldn't be any reason to drop and recreate the index, right... as data and
index pages are always kept in a sorted order.

Post by AlterEgo
Rob,
The reindex function will need data + (2 x Index space) to create an index.
You might want to examine the SORT_IN_TEMPDB option of the CREATE INDEX
statement, and look at dropping and recreating the indexes instead of the
DBCC DBREINDEX statement.
-- Bill

Post by Rob
Repeat post. My apologies... it seemed that my original post had gone into
limbo (timeout issue) so I had reposted.

Post by Rob
When I run the following DBCC command to reindex all indexes (in most cases
only one: clustered) on a table...
DBCC DBREINDEX (<table_name>, '', 10)
Could not allocate space for object ... in database ... because the
'PRIMARY' filegroup is full.
This is strange as the primary filegroup, which consists of three physical
files spread across three physical drives, have ample space for the database
to grow. This had been verified by updating the stats and running
sp_spacesused.
The reindexing had originally been done through the once weekly DB
optimization job, but after repeated failures due to the log file being full,
I decided to separate the reindexing of all tables into specific groups, by
running them in batches, once a day over the week, with no batch repeated
more than once a week.
That seems to have resolved the log file full issue, but I still see the
above error when the reindexing occurs against larger tables.
The database has grown to twice its size since I've replace the Opt. job
with manual DBCC DBREINDEX.
trunc. log on chkpt. (SIMPLE recovery mode),
torn page detection,
auto create statistics, and
auto update statistics
Any ideas what I could be doing wrong here?
TIA.

[Tibor Karaszi]

I'm not sure what you mean by "space chewed up by the indexes".

Perhaps you refer to "internal fragmentation", where page splits in an index lead to less than 100%
full pages? Internal fragmentation doesn't have to be bad, because a page will have room for some
rows before the next page split. This is why we have the FILLFACTOR option. But having pages not
full lead to more pages, of course.

I recommend you read the white paper I referred to. It has good details and recommendations.

The ways to "reorganize" an index are:
DBCC DBREINDEX
DBCC INDEXDEFRAG
Export, empty table, import (preferably data in same order as index).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

Post by Rob
Thanks Tibor.
Assumming that we choose not to run reindexing, is there a way to reclaim
the space chewed up by the indexes, without dropping the indexes?

Post by Tibor Karaszi

Post by Rob
as data and
index pages are always kept in a sorted order.

Logically, yes, the linked list is logically sorted. But as you follow the pointers in the linked
list, you can jump back and forth ion the disk. On other words, a clustered index can definitely
be
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Post by Rob
Thanks Bill.
I've been wondering if reindexing would do any good at all given that most
of my tables only have one clustered index. If it's clustered then there
shouldn't be any reason to drop and recreate the index, right... as data and
index pages are always kept in a sorted order.

Post by AlterEgo
Rob,
The reindex function will need data + (2 x Index space) to create an index.
You might want to examine the SORT_IN_TEMPDB option of the CREATE INDEX
statement, and look at dropping and recreating the indexes instead of the
DBCC DBREINDEX statement.
-- Bill

Post by Rob
Repeat post. My apologies... it seemed that my original post had gone into
limbo (timeout issue) so I had reposted.

Post by Rob
When I run the following DBCC command to reindex all indexes (in most cases
only one: clustered) on a table...
DBCC DBREINDEX (<table_name>, '', 10)
Could not allocate space for object ... in database ... because the
'PRIMARY' filegroup is full.
This is strange as the primary filegroup, which consists of three physical
files spread across three physical drives, have ample space for the database
to grow. This had been verified by updating the stats and running
sp_spacesused.
The reindexing had originally been done through the once weekly DB
optimization job, but after repeated failures due to the log file being full,
I decided to separate the reindexing of all tables into specific groups, by
running them in batches, once a day over the week, with no batch repeated
more than once a week.
That seems to have resolved the log file full issue, but I still see the
above error when the reindexing occurs against larger tables.
The database has grown to twice its size since I've replace the Opt. job
with manual DBCC DBREINDEX.
trunc. log on chkpt. (SIMPLE recovery mode),
torn page detection,
auto create statistics, and
auto update statistics
Any ideas what I could be doing wrong here?
TIA.