6

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

4

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.

  • My disk space is 10TB free out of 16TB .in same folder i do have other .mdf and .ldf files . Autogrowth for data file is 1000MB ,unlimited and log file 100MB limited to 2097152Mb .do you think i have to change autogrowth and maxsize ?@Ian_H – shamim Jan 18 '17 at 15:53
3

Run this command to get the estimated tempdb space required for the check:

DBCC CHECKDB ('databasename') WITH ESTIMATEONLY

See the DBCC CHECKDB documentation

ESTIMATEONLY
Displays the estimated amount of tempdb space that is required to run DBCC CHECKDB with all the other specified options. The actual database check is not performed.

0

I did ran the below query and it released the unused space.

USE [tempdb]
GO
DBCC SHRINKFILE (N'tempdev' , 0, TRUNCATEONLY)
GO

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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