Solved

Reindexing table

Posted on 1998-11-05
5
478 Views
Last Modified: 2008-03-10
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?
0
Comment
Question by:DARRAH
  • 2
  • 2
5 Comments
 
LVL 4

Expert Comment

by:dwwang
ID: 1091057
Have you tried expanding the size of tempdb database?
0
 

Author Comment

by:DARRAH
ID: 1091058
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?
0
 
LVL 2

Accepted Solution

by:
formula earned 100 total points
ID: 1091059
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!
0
 

Author Comment

by:DARRAH
ID: 1091060
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!
0
 
LVL 2

Expert Comment

by:formula
ID: 1091061
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.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now