Sql-server – Can’t create indexes on really large table!

sql serversql-server-2008

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?

Best Answer

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);
Related Question