Reindexing table

I'm attempting to reindex a table in my database, but when I do, I receive the following error message

Can't allocate space for object in database STS because the default segment is full. If you run out of space in Syslogs, dump the transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to expand the size of the segment.

My database is 500 MB, the log which is on a separate device is 125 MB. I don't (or at least didn't) think that the data contained within is already so much that there would not be space to reindex.

I tried reindexing on a particular index, which works, but when I do all, or the primary key index, it gives the above error. I tried dumping the transaction log, but that didn't make a difference.

My thoughts are to change the size of the database device, and then resize the database. Will that do it? I would think, as I said before, it already has enough space. Any thoughts?
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

formulaConnect With a Mentor Commented:
I suspect your primary key is a clustered index and you need at least 120 percent free space available in your database in order to recreate it.  This enables space for the table to coexist while it is being sorted. Also, recreate your clustered index before creating non-clustered ones, so that the non-clustered index does not need to be  reshuffled during the sort.

My question is why recreate the index?  You can update the ditribution page with "update statistics", so unless you change the index structure, you don't need to recreate.

By the way, tempdb is not your problem, unless your index is larger than 27mb.  But because tempdb is used for sorting and temporary tables for ALL databases, you may want to consider increasing the size of it anyway. Mine is 200 mb and works fine for my 8 GB database.  And you might have to increase the size of your STS database, too.  That is what your error is (i.e. the default segment is your data segment on STS).

Good Luck!
Have you tried expanding the size of tempdb database?
DARRAHAuthor Commented:
TempDB is currently at 27MB. Shouldn't that be large enough? If not, how do I go about expanding Tempdb. Is it part of Master? I'm asking these possibly ridiculous questions because I've not expanded it before and I want to make sure I'm doing it correctly?

If Temdb with 27MB should be large enough, what other possible avenues? Or is Tempdb the only thing that might restrict reindexing my table?
DARRAHAuthor Commented:
So I plan on resizing the database device to 600 MB and expand the database accordingly.

I thought I needed to reindex because when I ran DBCC SHOWCONTIG on that table, the scan density on the index was at 10%.

Please advise if there is another way!
There is another way. If your scan density is 10%, you can rebuild the clustered index to resolve the index fragmentation.  I did not realize you had a fragmentation problem.  Here's what to do:

1) Rebuild the index with the sorted_data option.  This will verify that the index is sorted.  This will work much faster and not copy the data, so you won't have space problems.  If the index values are not sorted though, it will fail.  Then you'll have to either rebuild the index without the sorted option or try option 2.
   Example: create clustered index XXXX with SORTED_DATA

Afterward, check your index again with DBCC SHOWCONTIG.  If it's still below say 50%, here's the other alternative:

2) Extract and reload the data from the table with BCP.

Have fun and if you need further assistance, let me know.
All Courses

From novice to tech pro — start learning today.