DBCC CHeckdb and tempdb usage

sakuraime 2,321 Reputation points
2021-02-27T04:55:36.417+00:00

May I know if there are enough memory , will it still use tempdb for dbcc check db ?

May I know the tempdb estimated to be use for dbcc checkdb is

roughly the max size of the table and its index in the database ?

I know there are dbcc checkdb with estimateonly , but I would like to know how it estimate at the back .

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,774 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. CarrinWu-MSFT 6,856 Reputation points
    2021-03-02T07:17:06.997+00:00

    Hi @sakuraime ,

    The ESTIMATEONLY option calculates how much space might be required in tempdb for the sort that holds the facts generated by the consistency-checking algorithms, taking into account all other specified options. DBCC CHECKDB goes through the motions of building all the batches of objects to check, but it doesn’t actually check them. Instead, it uses the metadata that it has gathered about each table and index to produce an estimate of the number of each type of fact that it generates. The various numbers are multiplied by the size of each fact type and added together to form a total size for that batch. The batch with the highest total size is the one that’s reported. The estimation algorithms are very conservative in their calculations to ensure that an accurate maximum size is returned. The actual amount of tempdb space taken up might be considerably lower. For example, the estimation algorithm estimates the number of facts required to track forwarding and forwarded records by simply counting all the records in a heap and multiplying by two. This is almost never the case, but it is a sufficient estimation.

    And also, please refer to How does DBCC CHECKDB WITH ESTIMATEONLY work? to get more information. Hope this will help you, if there has any other questions, please let me know. Thank you.

    Best regards,
    Carrin

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.