DBCC DBREINDEX failure

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.Repeat post. My apologies... it seemed that my original post had gone into
limbo (timeout issue) so I had reposted.
"Rob" wrote:

> When I run the following DBCC command to reindex all indexes (in most case
s
> 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 databa
se
> 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 fu
ll,
> I decided to separate the reindexing of all tables into specific groups, b
y
> 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,
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
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:6F079FC8-012F-4E24-8E07-645F1A197B25@.microsoft.com...[vbcol=seagreen]
> Repeat post. My apologies... it seemed that my original post had gone into
> limbo (timeout issue) so I had reposted.
> "Rob" wrote:
>|||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.
"AlterEgo" wrote:

> 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
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:6F079FC8-012F-4E24-8E07-645F1A197B25@.microsoft.com...
>
>|||> 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 p
ointers 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 i
s another question:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:6234B4EC-CF94-4DC8-9C2E-AF12AC12CB57@.microsoft.com...[vbcol=seagreen]
> 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 a
nd
> index pages are always kept in a sorted order.
> "AlterEgo" wrote:
>|||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?
"Tibor Karaszi" wrote:

> 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 clustere
d index can definitely be
> fragmented. Whether or not you suffer from the fragmentation, and how much
is another question:
> http://www.microsoft.com/technet/pr...ver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:6234B4EC-CF94-4DC8-9C2E-AF12AC12CB57@.microsoft.com...
>|||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 wi
ll 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/
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:A12B55E8-950E-4E6F-BAFE-40EF61C51C20@.microsoft.com...[vbcol=seagreen]
> 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?
> "Tibor Karaszi" wrote:
>