Sql-server – MSSQL: “Could not allocate space for object ‘dbo.SORT temporary run storage in database because ‘PRIMARY’ filegroup is full

sql serversql-server-2008

I have been around the block with this issue several times today checking blog posts everywhere and nothing has helped me to fix and resolve this issue.

I'm trying to add a non-clustered index to a table with around 7 million rows. The database is about 4GB on a 1TB hard drive. I have TempDB on the same drive, set to unrestricted auto growth by 10%. There is aproximately 950GB left on the drive. I keep getting this error.

This is SQL Server 2008 Standard.

I get the same error when I try to 'Rebuild INdexes'.

Best Answer

Make sure you're using the SORT_IN_TEMPDB option; it doesn't sound like you are based on your description of the tempdb config. Sounds like it's doing the sort within the database and the destination filegroup is getting filled.

The SORT_IN_TEMPDB option is not used by default when creating an index via SQL Server Management Studio's New Index UI. You can enable it on the Options pane of the New Index screen.

In T-SQL it's something like this (for a non-clustered index):

CREATE NONCLUSTERED INDEX [IX_MyNewIndex] ON [MyTable]
(
   COL1 ASC,
   COL2 ASC,
   ...
)
WITH (
    ....,
    SORT_IN_TEMPDB = ON,
    ....
)
Related Topic