DBCC CHECKDB logging

Question

Is DBCC CHECKDB logged operation?

Answer 1

Hi Chaitanya Kiran,

What I am asking is plain and only DBCC CHECKDB command. Is it logged?

No. I did it in my test database. There is no change in the transaction log before and after DBCC CHECKDB.

Best regards,

Seeya

Answer 2

Plain DBCC CHECKDB is not, since it is not doing any updates. If you use any of the REPAIR options, yes it is. Or, well, I have not tested of verified, but anything else would be extremely remarkable, since if a non-logged operation is interrupted, your database is corrupt. (And in case of DBCC CHECKDB REPAIR, even more than it was before.)

Generally, there are no non-logged operations in SQL Server.

Answer 3

Hi Chaitanya Kiran,

Please refer to this MS document: DBCC CHECKDB (Transact-SQL)

You can read this article to know what DBCC CHECKDB is doing.

DBCC CHECKDB Checks the logical and physical integrity of all the objects in the specified database by performing the following operations:

• Runs DBCC CHECKALLOC on the database.
• Runs DBCC CHECKTABLE on every table and view in the database.
• Runs DBCC CHECKCATALOG on the database.
• Validates the contents of every indexed view in the database.
• Validates link-level consistency between table metadata and file system directories and files when storing varbinary(max) data in the file system using FILESTREAM.
• Validates the Service Broker data in the database.

I find a link of corrupt database for you: https://www.sqlserverscience.com/tools/how-to-corrupt-a-database/

I did this test. After I perform the destruction operation:

Then, I run DBCC CHECKDB with repair_allow_data_loss:

We check transaction log:

The number of transaction logs has changed, and you can see the operations.

Best regards,

Seeya

---

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".