DBCC CHECKDB’s NOINDEX Option

Kendra Little explains the NOINDEX option of DBCC CHECKDB and how it relates to PHYSICAL_ONLY:

PHYSICAL_ONLY looks at page structures and makes sure something isn’t obviously wrong. If you have the database’s page verification option set to CHECKSUM (the default since SQL Server 2005, and a very good thing), it runs a checksum on the page and compares it to a checksum recorded in the header when the page was last modified.

When you use the PHYSICAL_ONLY option, you’re telling SQL Server to skip logical checks. It won’t do things like make sure the data in a clustered and nonclustered index on a table is consistent with one another.

I like the analogy, as well as the how-to guide on messing up your database.  But try not to do that to a production database…

Related Posts

CHECKDB On Azure SQL Database

Kevin Feasel

2017-09-05

Cloud, DBCC

Arun Sirpal ponders running DBCC CHECKDB on Azure SQL Database: I was exchanging messages with Azure Support and even though I didn’t get a concrete answer to confirm this I ended up asking the question within a Microsoft based yammer group and yes they do automatically carry out consistency checks. This is great but it […]

Read More

When CHECKDB Snapshots Run Out Of Disk Space

Andy Galbraith walks through an error message in DBCC CHECKDB when the snapshot runs out of disk space: Looking in the SQL Error Log there were hundreds of these combinations in the minutes immediately preceding the job failure: — The operating system returned error 665(The requested operation could not be completed due to a file […]

Read More

Categories

August 2016
MTWTFSS
« Jul Sep »
1234567
891011121314
15161718192021
22232425262728
293031