Tempdb “Monster” in SQL Server 2008 r2 swallows free hard disk drive space

In my quest to transfer excel data to SQL Server today, I was stumped by an error message. This error sprung up as soon as I had executed a query in SQL Server 2008 r2 that joined two tables. The error message was “Could not allocate space for object ‘dbo.SORT temporary run storage:  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.

You will see that the primary partition of your HDD has turned red in colour and that Windows OS is prompting you to clean up HDD space.

Why this error message comes up?

Answer is that when you use SQL Server to execute any operation on the database be it execute a query or sort a table it will process and store the query in a temporary location called as “registers” technically. In SQL Server this is called as tempdb.

Solution:

First, check what is the size of the tempdb folder. Its location is shown below. Its generally located at

tempdb

C:\Program Files\Microsoft SQL Server\MSSQL10_50.SAMSQLSERVER\MSSQL\DATA\

In the above path, notice the text that is bold. This will be user specific instance of your SQL Server.

The easiest solution is to close SQL Server application and restart it again. If this doesnt solve the problem Then navigate to Services.msc and stop the SQL Server service & SQL Server Agent service. Once these two services are stopped, navigate to the tempdb location again and you will see that it has released the space that it was occupying earlier.

Optionally you may set the recovery model of tempdb to SIMPLE

References
Optimizing tempdb Performance in SQL Server 2008 R2
To view or change the recovery model of a database

Advertisements