Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
9  
Are you on Express edition and hitting the 4/10GB per database limit? –  Martin Smith Jul 24 '12 at 6:13
    
Why yes I am. Could you explain the 4/10 per database limit? –  Abe Miessler Jul 24 '12 at 13:08
1  
On Express, the maximum database size is 10 GB on 2008 R2; memory says it's only 4 GB on 2008 (non-R2). –  Jon Seigel Jul 24 '12 at 13:50
    
I see. @MartinSmith, if you want to put that as an answer I will accept? –  Abe Miessler Jul 24 '12 at 14:06
add comment

2 Answers 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?

share|improve this answer
add comment

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);
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.