Can't create indexes on really large table!

Question

I have a table with over 300 Million rows in it. When I try to create an index on the table I get the error message below.

Msg 1105, Level 17, State 2, Line 3 Could not allocate space for object 'dbo.SORT temporary run storage: 422212472471552' in database 'MSD' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

When I look at the files, the one using the PRIMARY file group is set to grow by 10% and is unrestricted for max size. My hard drive still has 500 gigs of space left on it. Does anyone know why I am hitting this problem and how I can get around it?

Answer 1

One thing you can try is to temporarily set the recovery model to Simple.

Alternatively, you can tell the create index command to use tempdb. Since tempdb is typically in the Simple recovery mode.

CREATE INDEX ...
WITH (SORT_IN_TEMPDB = ON);

Answer 2

uu run out of database space. This can happen if: 1) If autogrow is off. In this case, you need to extend the database manually.(tempdb) 2) Autogrow is on, but there is not room on the disk.

if autogrow is 10% and this translates to 30GB, this means if you only have 20GB free, you get an error.

Are you specifying the SORT_IN_TEMPDB option on the CREATE INDEX?

Answer 3

If you are on Express Edition, you may be hitting the 4/10GB per database limit.- martin-smith

The limit was originally 4GB, then raised to 10GB in the 2008 R2 release. - jon-seigel