Archive for May, 2010

Undocumented CHECKDB options May 28th, 2010

Vinod Kumar

Many of my previous posts do talk a lot about how CHECKDB works within SQL Server with various interesting combination of scenarios. Though the use of CHECKDB and understanding of this topic is quite complex, in this post let me talk about the undocumented option of TABLERESULTS. It is quite handy and I used it in one of the demo at TechEd India 2010.


This option will allow CHECKDB to return a TDS based result set for all messages (informational and errors) returned as part of executing CHECKDB. This option maybe undocumented for multiple reasons:

* Maybe the code is not yet tested or there can be bugs that can disrupt the behavior end-users want.

The option is interesting as the result set if implemented fully could help produce a report of true errors encountered, specific repair actions needed for each error, etc.

The command is beneficial to analyze corrupt databases when brought in-house for troubleshooting. Esp for cases when there is corrupt index pages, you can quickly look at the INDID and figure out is they are only non-clustered indexes that have got damaged or if there are Clustered indexes also which have to be examined …

Microsoft is not under any obligation to provide information or support these commands or tools as these are undocumented. My previous posts around CHECKDB are –




CHECKDB and ResourceDB



Continue reading...


CHECKDB and TempDB May 24th, 2010

Vinod Kumar

Continuing the conversations around CHECKDB and my session at TechEd. Here is another interesting trivia about CHECKDB and its usage against TempDB. If you remember the post around CHECKDB going ONLINE on the blog the usage of Snapshot internally is something unique. If you get into the documentation of DATABASE SNAPSHOTs on BOL you can read – we cannot create a SNAPSHOT on system databases including TEMPDB. The only exception is MSDB but that is a conversation for another day :) …

So our internal snapshot for CHECKDB also fails gracefully and you will notice this when you issue the command against TEMPDB.

DBCC results for ‘tempdb’.
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.

This is because of the behavior of Tempdb with ability to recover the database on restart. You cannot create a database snapshot on the Tempdb database.

Interesting hidden within the message – the CHECKCATALOG and Service Broker checks are skipped. On closer inspection – it is not possible to run CHECKALLOC on Tempdb too.

The check statement was aborted. DBCC CHECKALLOC cannot be run on TEMPDB.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

There are undocumented trace flags that can force this action but those are surely out-of-scope and most importantly, unsupported :). BTW, while I was on the first error – even the CATALOG are not checked and you can confirm it from CHECKCATALOG command which also fails just like above.

The check statement was aborted. DBCC CHECKCATALOG cannot be run on TEMPDB.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

When it comes to TempDB corruptions, a restart will solve the same. But I highly recommend further investigation to the root cause analysis because you dont want to be in this situation over a weekend when you are on vacation. Mostly, errors comes from IO subsystem and that should be your first point of troubleshooting. Then move to the next layers.

Continue reading...


CHECKDB and DATA_PURITY May 21st, 2010

Vinod Kumar

DBCC CHECKDB command performs several operations in additional to checking the consistency of allocation structures and various objects inside a database. One such important interesting activity added is the DATA_PURITY checks. In SQL Server 2000, DBCC CHECKDB did not evaluate the values present in the columns to ensure that they are valid and conform to the rules specified for the data types for those columns. But there was an undocumented trace flag which could be used to force DBCC CHECKDB to perform some limited data purity checks. But I will refrain from getting into those shortcuts …

In SQL Server 2005, a documented option is available for the DBCC CHECKDB command that can be used to force the data purity checks. This is the WITH DATA_PURITY option. There are a couple of important points that you need to understand regarding these checks:

  • The data purity checks are performed automatically whenever DBCC CHECKDB is executed against a database created in SQL Server 2005
  • The data purity checks cannot be disabled for databases that were created in SQL Server 2005
  • The data purity checks are not performed automatically whenever DBCC CHECKDB is executed against a database created in SQL Server 2000 or earlier
  • If PHYSICAL_ONLY is specified, column-integrity checks are not performed.
  • In order for DBCC to perform data purity checks on a database created in SQL Server 2000 or earlier, you will need to execute DBCC CHECKDB with the DATA_PURITY option.
  • If this DBCC CHECKDB was successful, then a bit is turned on in the boot page to indicate future runs of DBCC CHECKDB perform data purity checks automatically. If you are still running SQL 2000 (you must upgrade), but for academic purposes you might want to see the bit info using the DBINFO command. This typical undocumented command can be used like this –


Paul on his blog talks about this interesting information of DBCCFlags bit out there. 0 means it will not be run and 2 will mean it will be enabled by default. There are specific checks performed for columns with the data types – bit, real, float, datetime, smalldatetime, decimal, numeric, binary, varbinary, nchar,nvarchar. There is a very good possibility that data purity checks show problems on upgraded databases because these checks were not performed in earlier versions unless the trace flag was used to force these checks.

Continue reading...


CHECKDB is ONLINE now May 18th, 2010

Vinod Kumar

The series of activities I have been writing on CHECKDB, I just dont seem to stop exploring interesting things about this command. It is so powerful that I just love it. In a session with MS Internal IT today I was talking about how CHECKDB is done as an ONLINE session and we changed to this mode since the SQL Server 2005+ days. So now CHECKDB is run of an internal snapshot database. This snapshot is similar in some respects as if you were to create a database snapshot using the CREATE DATABASE command. The snapshot created by CHECKDB is not visible to users in any form. When you query catalog views or system tables, there will be no reference associated to this snapshot.

Having said all this, the world was way different in the SQL Server 2000 days. For academic purposes, the CHECKDB used to perform expensive log scans to bring individual pages to a consistent state to perform analysis on the pages and generate facts to check the consistency. Also there was expensive locking involved to keep the analyzed data consistent. All of this is done on the live database on which data modification is happening. So now you get the fact on how CHECKDB has evolved over a period of time.

Coming back to the SQL 2005+ implementation, this internal snapshot are a transient replica of the database and is not a persistent replica like what we create using CREATE DATABASE with SNAPSHOT. So once the CHECKDB operation completes, we gracefully delete this transient database.

Now that we are on this interesting point, let me give you a behavior that is interesting that I was discussing with Pinal Dave today over lunch. Take this simple scenario, we have an “Open transaction” when the CHECKDB gets initialized. Just like below –

Begin Tran
Insert into temp1 values (1)

Now when I run the CHECKDB on this database (on a different window), when this above transaction is OPEN, there is an interesting message on the SQL Server Errorlogs that is worth noting.


Interestingly, this seems to have rolled-back our transaction without our consent? No way, dont panic. This has not rolled-back our database transaction, but since CHECKDB needs to run against a consistent database, it rolled-back our transaction on the “Transient Replica” aka internal snapshot. To double check this behavior, issue COMMIT back on our original query window (with INSERT stmt) and you can see the data with value of “1” has indeed got committed into the database :).

More interesting trivia on SQL Server coming your way … Thanks and feel free to pass your comments.

Continue reading...


CHECKDB and ResourceDB May 13th, 2010

Vinod Kumar

ResourceDB is a new system database that we introduced with SQL Server 2005+. Though this is hidden and cannot be access directly as we used to do with SQL Server 2000 days of changing metadata, there is some protection now. Anyways, there are lots more interesting trivia’s of using ResourceDB and I will reserve it for another day. Today the topic is around – “What if this hidden DB gets corrupt?” … Not exactly, but how can I run CHECKDB on this database?

DBCC CHECKDB (mssqlsystemresource)

This is the obvious choice if you attended my session at TechEd. But interestingly SQL Server throws an error stating –

Msg 2520, Level 16, State 11, Line 1
Could not find database ‘mssqlsystemresource’.
The database either does not exist, or was dropped before a statement tried to use it.
Verify if the database exists by querying the sys.databases catalog view.

This is fundamentally because the server is running in multi-server mode. Now there is a neat trick to this, if you try to run the same command against the MASTER database the output looks really interesting. If you scroll carefully through the content, there are some interesting lines not to be missed.

CHECKDB found 0 allocation errors and 0 consistency errors in database ‘master’.
DBCC results for ‘mssqlsystemresource’.
CHECKDB found 0 allocation errors and 0 consistency errors in database ‘mssqlsystemresource’.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

And the ERRORLOG carries an message stating –

DBCC CHECKDB (mssqlsystemresource) executed by Vinod found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 1 seconds.

If there is a corruption problem in the resource database and there is a need to get it repaired for the efficient functioning of the SQL Server. As part of your DR plans make sure to keep a copy of the ResourceDB files so that they can be easily brought to an consistent state.To summarize, you will need to replace the resource database if there is any physical corruption introduced in the database. Normally customers will not have the ability to write to this database and a Microsoft Support request needs to be raised.

Continue reading...