Sql-server – Appropriate tempdb size

size;sql servertempdb

DBCC CHECKDB is failing on a particular database because of

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

Tempdb is currently 5GB. The database in question is 56GB. The biggest table in that db is 15GB.

How big shall I make tempdb?

Thanks

Best Answer

You can use the "WITH ESTIMATEONLY" with your dbcc command.

DBCC CHECKDB WITH ESTIMATEONLY

This should give you an aprox. space in tempdb to run it. .

Related Solutions

Sql-server – sql server – could not allocate space for object

This error is appearing because your query requires SQL Server to sort a big amount of data.

Are you sure that there is enough disk space for all data files of temp db and that the data files have no size restriction (SQL Server has an option to restrict a file's size)? These are the only problems I can imagine.

Sql-server – Can’t create indexes on really large table!

One thing you can try is to temporarily set the recovery model to Simple.

Alternatively, you can tell the create index command to use tempdb. Since tempdb is typically in the Simple recovery mode.

CREATE INDEX ...
WITH (SORT_IN_TEMPDB = ON);

Best Answer

Related Solutions

Sql-server – sql server – could not allocate space for object

Sql-server – Can’t create indexes on really large table!

Related Question