Error on SELECT - Primary filegroup is full

Question

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.

Answer 1

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
GO

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

USE master
GO
ALTER DATABASE TempDB MODIFY FILE
  (NAME = tempdev, FILENAME = 'e\:<tempdb datafile>.mdf')
GO
ALTER DATABASE TempDB MODIFY FILE
  (NAME = templog, FILENAME = 'e:\<tempdb log>.ldf')
GO

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.

Answer 2

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]
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'newtempfile', FILENAME = N'e:\newtempfile.ndf' , SIZE = 100MB, MAXSIZE = 500MB , FILEGROWTH = 100MB )
GO

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.