A SQL Server 2008 R2 database view unions several transaction table. The query has been slow over the couple of months and the dev team is planning to recreate the query. I need to ETL the results into a flat file and purge them from the DB. When I tried to query the view (of 5M records) this error showed up:

Msg 1105, Level 17, State 2, Line 1
Could not allocate space for object 'dbo.SORT temporary run storage: 140744470495232' in database 'tempdb' 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.

What is the recommended file growth I should set, how is this error managed or solved? Thanks in advance.


3 Answers 3


The quickest fix would be to restart the SQL Server and Tempdb will be recreated with default size and empty files.

But if it's a production server you can't really restart it when you want. A real fix would be to add a new file on a different drive and run your queries.

An example would be (new file of starting size 1 MB, increase 100 MB, limit 500 MB):

USE [master]
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'newtempfile', FILENAME = N'e:\newtempfile.ndf' , SIZE = 100MB, MAXSIZE = 500MB , FILEGROWTH = 100MB )

And then, when you have time, check what's using Tempdb so much. But likely you'll still need space for TempDB, so you'd better plan space for this and assign enough space for this db, as it's very important for a healthy system (you can simply look at it as a RAM part).

PS1: check if you don't have a Cartesian product anywhere in your select statements, because that number seems a bit high.

PS2: If you have enough free space on the TempDB drive, check if the files didn't get to their limit and autogrowth is disabled. If yes, enable autogrowth (not by percent, but by some specific size you feel comfortable with).

PS3: a good solution would be to break that ETL process into smaller transactions. Instead of removing 1 Bil records at once, do it by 1000 mill .. or play with batch sizes until you feel safe with the length of the process and the size of the files. You'll likely obtain the result faster and not have huge increases in space at once.


The error most likely indicates that the hard drive that your tempdb is located on is full. The easiest solution would be plugging in a new drive, and moving the tempdb on there.

First, find out the name of the files that make up the tempdb, by running the following query in the context of the tempdb:

EXEC sp_helpfile

Alter the following script using the file names from the above script:

USE master
  (NAME = tempdev, FILENAME = 'e\:<tempdb datafile>.mdf')
  (NAME = templog, FILENAME = 'e:\<tempdb log>.ldf')

Obviously, replace E:\ with the new drive letter.

When done, restart SQL Server to actually move the tempdb, and then try and see if that solved the issue.


Clean up your disk space and you will be fine. SQL temporary files persist on your disk even if you delete them from SSMS. You will have to delete manually these files or create more space on your disk to allow more data to run.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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