Allocate space for object 'dbo.SORT temporary run storage

by shamim   Last Updated August 13, 2017 05:06 AM

DBCC CHECKDB returns:

Could not allocate space for object 'dbo.SORT temporary run storage: 140737951236096' 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.

Msg 9002, Level 17, State 4, Line 1



Answers 3


This is pretty self explanatory - your tempdb is full, and can't be grown to perform the operation it needs tempdb for.

As indicated, either the disk where it is located is full, or you haven't got suitable autogrowth settings (see this earlier post for information about that), if it's not autogrowth, you'll have to either delete other files from the disk that your tempdb is on, or locate your tempdb on a bigger disk.

This sqlservercentral post is similar to yours and might take be worth a look. This video by Brent Ozar might be worth a look too, covering tempdb performance and config.

Ian_H
Ian_H
January 18, 2017 14:54 PM

Set smaller auto growth sizes. The file growth of 1000MB is quite large. Here is what happens inside of sqlservr.exe. Transaction 1 causes the log/data file to run out of space, so auto growth starts. Remember that the disk is the slowest part of a server and runs in Milliseconds. The CPU runs in Nanoseconds and is an operating system that is multi-threaded. Now transaction 2 comes along and the file is in an autogrowth state, this now throws an error saying the file is out of space while you have TBs of free space.

Duane Lawrence
Duane Lawrence
January 18, 2017 17:35 PM

run this command to get estimated time for your database DBCC CHECKDB ('databasename') WITH ESTIMATEONLY

Sathishchandra BhanuMurthy
Sathishchandra BhanuMurthy
August 13, 2017 04:12 AM

Related Questions




DBCC CHECKDB consistency-based I/O error

Updated May 16, 2017 10:06 AM