January 26, 2006 at 9:10 am
I am attempting to rebuild Indexes on a 136GB database. They are all non-clustered Indexes. All of the Indexes are in one table, which stores the majority of the db data.
When I run the following I get an error that the file group is full; however I have no more disk space to give the database so I am stuck.
How do I rebuid these Indexes? Do I have to drop and recreate them?
Statement I am using:
DBCC DBREINDEX
('dbname.dbo.tablename', indexname)
Error:
Msg 1105, Sev 17: Could not allocate space for object '(SYSTEM table id: -55978638)' in database 'dbname' because the 'PRIMARY' filegroup is full. [SQLSTATE 42000]
Msg 3621, Sev 17: The statement has been terminated. [SQLSTATE 01000]
TIA,
-Ben
January 26, 2006 at 9:41 am
You'll need to get additional disk space somehow.
Add another drive, and create a filegroup, and file on the new drive, and create your indexes on that new filegroup.
You didn't say, but if you don't have your log on another drive, you should do that too.
Reducing the # of indexes by creating covering indexes could help save some space, but I don't think a ton of space.
January 26, 2006 at 10:10 am
Thanks for the advice. I did move the transaction log to another drive for better performance, and that has made a noticable difference.
I will try to acquire a drive and create a file group. One other question...After I create the indexes in the new filegroup, do I have to copy the file group back to its original location or can I keep the file group on a different drive?
Thanks,
-Ben
January 26, 2006 at 10:43 am
No, definatelly keep it on a different drive.
This will also help performance.
Your distributing your data across drives which will help accessing the data a little quicker. (Indexes are data about the data)
January 26, 2006 at 12:07 pm
>>They are all non-clustered Indexes.
Do you have a clustered index on this 1 large table ? If not, you may have developed a highly fragmented heap.
January 26, 2006 at 1:53 pm
Yes, and I have seen several cases where the lack of a clustered index lead the optimizer to choose poor execution plans using the wrong index etc...
Put a clustered index on that table, there's really no reason not to.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
January 27, 2006 at 3:35 am
After the last transaction log backup and before the database backup set the database to Simple recovery run DBCC DBREINDEX ('dbname.dbo.tablename', indexname), on completion set the database to Full recovery before the database backup executes (this assumes that you have sufficient time after the end of daily / weekly operations to complete the reindex and your database backup).
As a rule I find that re-indexing using DBCC DBREINDEX in Full recovery will require empty space in the database equal to the size of the transaction log. This is because DBCC REINDEX is run within a transaction creating the new index before deleting the old and in many systems the size of this transaction is fare larger than any transaction that access data in normal operation.
January 31, 2006 at 8:54 am
When I've encountered situations with index rebuilds and limited space (most of the time) I've created a script that will reindex the tables individually based on the size. I've also set it up to only rebuild certain tables various times during the week to virtually eliminate the TLog problems. Getting space would really be the best solution.. especially if the tables are growing at any pace.
-ScottyMan
January 31, 2006 at 10:56 am
Yes I believe adding a clustered index is the way to go in the long run, this table is several million rows and has multiple non-clustered indexes.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply