Page tree
Skip to end of metadata
Go to start of metadata

This command performs validation of descriptors in the metadata cache.

  • verify translation tables on the system catalogs
  • verify that contents of a sysobjects row matches contents of sysobjects row cached in a des
  • verify that contents of a sysindexes row matches contents of a sysindexes row cached in a ides
  • verify that contents of a syspartitions row matches contents of a syspartitions row cached in a pdes
  • validates target pages cached in a pdes

Syntax

dbcc checkmdcache( [ dbid | dbname [, objid={0 | 1 | 2 |...} [, verbose={0|1} [, lock={0|1} ] ]
dbid: Database id, if dbid is 0, it checks current database.
dbname: Database name, if not supplied, then verifies current database
objid: Object id of the object whose metadata cache is to be verified. If objid is 0, all the objects in the database are checked
1: detailed messages are printed about the check being performed.
1: An exclusive table lock is obtained prior to any integrity checks to ensure spurious failures are not reported due to any on-going concurrent activity that may be modifying a metadata cache.

verbose-0(default)- no messages are printed unless an integrity check fails.lock: 0(default) No lock is obtained on the object being verified.

Example 1

Check metadata cache for current database

1> dbcc checkmdcache
2> go
### Objversionts match failed. disk 9017160 cache 25689520

Object validation failed: dbname 'pubs2' dbid '5' objname 'mymsgs' objid '844527011'

Please run with 'verbose' option for more details

Database 'pubs2' dbid '5' metadata cache validation failed

WARNING!! dbcc checkmdcache execution failed with some errors

DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.

Example 2

Now run dbcc checkmdcache with verbose option 1

1> dbcc checkmdcache(pubs2,844527011,1)
2> go
=========================================
Validating object: dbname 'pubs2' dbid '5' objname 'mymsgs' objid '844527011'
No lock held on dbid 5 objid 844527011
If you see errors, rerun with 'lock' option set to avoid spurious errors.
Validating sysobjects metadata for objid 844527011
### Objversionts match failed. disk 9017160 cache 25689520
Validating target page list for indid 0 partition 860527068
Validating target page list for indid 0 partition 876527125
Validating target page list for indid 0 partition 892527182
Validating target page list for indid 0 partition 908527239
Validating target page list for indid 0 partition 924527296
Index rows for objid 844527011 in dbid 5 being verified
Checking disk indid 0 Versus cached indid 0
Partition rows for objid 844527011, indid 0 are being verified
Checking disk indid 0 ptnid 860527068 Versus cached indid 0 ptnid 860527068
Checking disk indid 0 ptnid 876527125 Versus cached indid 0 ptnid 876527125
Checking disk indid 0 ptnid 892527182 Versus cached indid 0 ptnid 892527182
Checking disk indid 0 ptnid 908527239 Versus cached indid 0 ptnid 908527239
Checking disk indid 0 ptnid 924527296 Versus cached indid 0 ptnid 924527296
Verifying pdes local hash table for Objid 844527011, Indid 0
Partition keys for tabid 844527011 and indid 0 being verified
Ptnkey verification for tabid 844527011 and indid 0 successful
Partition boundary for tabid 844527011 and indid 0 being verified
Partition boundary verification for tabid 844527011 and indid 0 successful

Object validation failed: dbname 'pubs2' dbid '5' objname 'mymsgs' objid '844527011'
=========================================

Database 'pubs2' dbid '5' metadata cache validation failed

WARNING!! dbcc checkmdcache execution failed with some errors

DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.

Example 3

Run dbcc checkmdcache with verbose option 1 and lock option 1

1> dbcc checkmdcache(pubs2,844527011,1,1)
2> go
=========================================
Validating object: dbname 'pubs2' dbid '5' objname 'mymsgs' objid '844527011'

Obtained EX_TAB lock on dbid 5 objid 844527011
Validating sysobjects metadata for objid 844527011
### Objversionts match failed. disk 9017160 cache 25689520
Validating target page list for indid 0 partition 860527068
Validating target page list for indid 0 partition 876527125
Validating target page list for indid 0 partition 892527182
Validating target page list for indid 0 partition 908527239
Validating target page list for indid 0 partition 924527296
Index rows for objid 844527011 in dbid 5 being verified
Checking disk indid 0 Versus cached indid 0
Partition rows for objid 844527011, indid 0 are being verified
Checking disk indid 0 ptnid 860527068 Versus cached indid 0 ptnid 860527068
Checking disk indid 0 ptnid 876527125 Versus cached indid 0 ptnid 876527125
Checking disk indid 0 ptnid 892527182 Versus cached indid 0 ptnid 892527182
Checking disk indid 0 ptnid 908527239 Versus cached indid 0 ptnid 908527239
Checking disk indid 0 ptnid 924527296 Versus cached indid 0 ptnid 924527296
Verifying pdes local hash table for Objid 844527011, Indid 0
Partition keys for tabid 844527011 and indid 0 being verified
Ptnkey verification for tabid 844527011 and indid 0 successful
Partition boundary for tabid 844527011 and indid 0 being verified
Partition boundary verification for tabid 844527011 and indid 0 successful
Object validation failed: dbname 'pubs2' dbid '5' objname 'mymsgs' objid '844527011'
=========================================
Database 'pubs2' dbid '5' metadata cache validation failed

WARNING!! dbcc checkmdcache execution failed with some errors
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.

Example 4.

Fix versionts of this table by setting versionts to NULL

1> select id, versionts, name from sysobjects where id=844527011
2> go
id versionts

         name



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



   844527011 0x00000000000c8beb00120000
         mymsgs

1> update sysobjects set versionts=NULL where id=844527011
2> go
(1 row affected)
1> checkpoint
2> go
Run dbcc checkmdcache again to make sure error is fixed.

1> dbcc checkmdcache
2> go
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.

  • No labels