DBCC CHECKDB Question

Question

DBCC CHECKDB Question

Chaitanya Kiran 841

Does DBCC CHECKDB read pages from memory or from source db on disk?

0 comments

4 answers

Your answer

Answer 1

YufeiShao-msft 7,156

Hi @Chaitanya Kiran ,

DBCC CHECKDB use database snapshots, when DBCC CHECKDB reads a page in the context of the database snapshot, that page must be read from the source database on disk, and database snapshots use NTFS sparse-file techology
https://www.sqlskills.com/blogs/paul/checkdb-from-every-angle-complete-description-of-all-checkdb-stages/

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

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.

Chaitanya Kiran 841 Reputation points

2022-03-06T14:25:49.747+00:00

Hi Yufeishao,
If it reads all pages only from source database disk file, then whats the point in having database snapshot sparse file?
YufeiShao-msft 7,156 Reputation points

2022-03-09T02:20:37.18+00:00

DBCC CHECKDB uses an internal database snapshot yo provide the required tranactional consistency, but the snapshot file grows in size as changes are made to the source database, a file produced by the NTFS file system requires significantly less disc space than it would otherwise, a sparse file is used to store pages that have been copied to a database snapshot.
When a sparse file is created, it takes up very little disc space, as data written to a database snapshot, NTFS gradually allocates space on the disc to the corresponding sparse file.

Answer 2

Anonymous

After executing DBCC checkdb , I have got the following errors in one of my DB. Any solution please

Msg 5901, Level 16, State 1, Line 1
One or more recovery units belonging to database 'xxx' failed to generate a checkpoint. This is typically caused by lack of system resources such as disk or memory, or in some cases due to database corruption. Examine previous entries in the error log for more detailed information on this failure.
Msg 1823, Level 16, State 2, Line 1
A database snapshot cannot be created because it failed to start.
Msg 1823, Level 16, State 8, Line 1
A database snapshot cannot be created because it failed to start.
Msg 7928, Level 16, State 1, Line 1
The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.
DBCC results for 'xxx'.
DBCC CHECKDB will not check SQL Server catalog or Service Broker consistency because a database snapshot could not be created or because WITH TABLOCK was specified.
DBCC results for 'sys.sysrscols'.

Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2022-07-29T21:23:36.13+00:00

Rather than piggybacking on old threads, you are strongly encouraged to start a new thread, describing your issue from start to end. Don't forget to include the output of "SELECT @@version" in your post.
YufeiShao-msft 7,156 Reputation points

2022-08-01T08:36:04.497+00:00

If you have a issue, please start a new thread and decribe your issue in detail, this has to help you solve the problem quickly

Thank you for understanding!

Answer 3

Shashank Singh 6,251

It reads from disk ( which is snapshot basically like Erland pointed) but the checkdb is performed in memory.

Chaitanya Kiran 841 Reputation points

2022-03-05T00:44:45.817+00:00

So, it reads from both sparse file as well as source database disk, correct?
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2022-03-05T11:06:00.61+00:00

Yes, and it does not really know from where it's reading. NTFS handles that part.
Chaitanya Kiran 841 Reputation points

2022-03-06T14:23:34.77+00:00

Hi Shashank,

When you say disk, do you mean source database disk? Then, whats the use of the database snapshot sparse file?

Answer 4

Erland Sommarskog 134.7K MVP Volunteer Moderator

It reads from disk. More precisely, it reads from a hidden database snapshot. So when DBCC CHECKDB starts, it creates a database snapshot, which is a sparse file (that's a Windows feature) to get a consistent view of the database.

Chaitanya Kiran 841 Reputation points

2022-02-27T22:11:50.233+00:00

So it reads from sparse file or from source database on disk?
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2022-02-27T22:14:57.103+00:00

Both. :-)

The way it works is this: a sparse file has seemingly the same size as the source file, but as long as the page in the source database has not changed, a read against the sparse file, will actually read the page from the source database. But if the page is changed in the source database, the original page will be physically written to the sparse file.
Chaitanya Kiran 841 Reputation points

2022-03-01T01:43:30.757+00:00

If the page is changed in the source database, the pre-changed version of the page is copied to sparse file. So,in this case , DBCC CHECKDB will read from sparse file, correct?
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2022-03-01T22:06:10.133+00:00

Yes, that's correct.

Share via

DBCC CHECKDB Question

4 answers

Your answer