0

I was running a dbcc command using ola hallengren checkdb on a large database (775Gb) when half way through the process the tempdb ran out of space. I have run this in other environment on different databases in the past and no problems. This time though the database went from 775 GB to 1.6 TB, doubling in size. I have never had this issue in the past and anything I do increase the size by 100 GB. What can be causing this issue like that? SQL server 2016.

4
  • This is strange, so data file size increased or log file. I would also check to see if the snapshot files created by checkdb are removed or not, due to the unexpected failure it seems like the snapshot files were not removed. They can be of type DatabaseName.mdf_MSSQL_DBCCXX where XX is number
    – Shanky
    Sep 2 '20 at 11:54
  • Not finding any DatabaseName.mdf_MSSQL_DBCCXX in any of the folder or drives. but it appears that the indexes have exploded in size. Might need to look at dropping them and then re-adding some. Nothing makes sense as to why it did this.
    – tbear58203
    Sep 2 '20 at 12:32
  • Did you had index rebuild and checkdb jobs combined ?.
    – Shanky
    Sep 2 '20 at 12:54
  • Yes, I think the issue is the indexes, I ran a quick top ten and they are huge fragmentation and need to have rebuild done.
    – tbear58203
    Sep 2 '20 at 13:18
0

CHECKDB by design will use tempdb space for temporary storage and it will fail if you run out of space. Make sure you presize your tempdb. You can check how much space it needs by issuing WITH ESTIMATEONLY command:

DBCC CHECKDB ([WideWorldImporters]) WITH ESTIMATEONLY;

You will be able to get an estimate space requirement for tempdb:

DBCC results for 'WideWorldImporters'.
Estimated TEMPDB space (in KB) needed for CHECKDB on database WideWorldImporters = 1.
Estimated TEMPDB space (in KB) needed for CHECKDB on database WideWorldImporters = 261556.
Object ID 1179151246 (object 'Warehouse.ColdRoomTemperatures'): The operation is not supported with memory optimized tables. This object has been skipped and will not be processed.
Object ID 1211151360 (object 'sys.memory_optimized_history_table_1179151246'): The operation is not supported with memory optimized tables. This object has been skipped and will not be processed.
Object ID 1243151474 (object 'Warehouse.VehicleTemperatures'): The operation is not supported with memory optimized tables. This object has been skipped and will not be processed.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Since you have close to 800GB, what you can do is breakdown the CHECKDB into multiple DBCC commands. You can follow the recommendation here.

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.