Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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
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.
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.
I ran the below query and it released the unused space:
USE [tempdb]
GO
DBCC SHRINKFILE (N'tempdev' , 0, TRUNCATEONLY)
GO
I also got this error message. It turns out that the physical disk was full. Our SQL Server admin deleted some history backups and changed backup mode from full to simple at some databases which had large transaction log and their purpose was for reporting and not for transaction's (application).
