DBCC CHECKDB and REPAIR_ALLOW_DATA_LOSS with MSSQL

  • DBCC CHECKDB and REPAIR... purpose is to: get the database back to a consistent state so that processing can continue.
  • DBCC CHECKDB and REPAIR... purpose is NOT to recover data loss!.
  • When you use: REPAIR_ALLOW_DATA_LOSS, DBCC CHECKDB will delete pages from the database.
  • The pages deleted contain either indexes or actual user data.
  • Indexes information being deleted is OK, we can rebuild all the indexes, but what about the actual data?
  • If a row contains a text column without the proper off-row text storage, then the row is deleted.
  • if a row structure is corrupted, such as an invalid variable-length offset or an invalid data-type then the whole page is deleted. All the rows on that pages are deleted with it.

Applies to:

  • Microsoft SQL Server 2000
  • Microsoft SQL Server 2005
  • Microsoft SQL Server 2008
  • The only way to protect yourself, your data and your job is a proper, valid and VERIFIED backup!

DBCC CHECKDB and REPAIR_ALLOW_DATA_LOSS

  • Before using: DBCC CHECKDB ('sql911', REPAIR_ALLOW_DATA_LOSS);, you will need to have the database in single user mode.
  1. ALTER DATABASE sql911
  2. SET single_user WITH ROLLBACK IMMEDIATE;
  3. go
  4. DBCC checkdb ('sql911', repair_allow_data_loss);
  5. go
DBCC results for 'sql911'.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
Service Broker Msg 9667, State 1: Services analyzed: 3.
Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
DBCC results for 'sys.sysrowsetcolumns'.
There are 595 rows in 6 pages for object "sys.sysrowsetcolumns".
DBCC results for 'sys.sysrowsets'.
There are 88 rows in 1 pages for object "sys.sysrowsets".
DBCC results for 'sysallocunits'.
There are 99 rows in 1 pages for object "sysallocunits".
DBCC results for 'sys.sysfiles1'.
There are 2 rows in 1 pages for object "sys.sysfiles1".
DBCC results for 'sys.syshobtcolumns'.
There are 595 rows in 5 pages for object "sys.syshobtcolumns".
DBCC results for 'sys.syshobts'.
There are 88 rows in 1 pages for object "sys.syshobts".
DBCC results for 'sys.sysftinds'.
There are 0 rows in 0 pages for object "sys.sysftinds".
DBCC results for 'sys.sysserefs'.
There are 99 rows in 1 pages for object "sys.sysserefs".
DBCC results for 'sys.sysowners'.
There are 14 rows in 1 pages for object "sys.sysowners".
DBCC results for 'sys.sysprivs'.
There are 120 rows in 1 pages for object "sys.sysprivs".
DBCC results for 'sys.sysschobjs'.
There are 65 rows in 1 pages for object "sys.sysschobjs".
DBCC results for 'sys.syscolpars'.
There are 484 rows in 8 pages for object "sys.syscolpars".
DBCC results for 'sys.sysnsobjs'.
There are 1 rows in 1 pages for object "sys.sysnsobjs".
DBCC results for 'sys.syscerts'.
There are 0 rows in 0 pages for object "sys.syscerts".
DBCC results for 'sys.sysxprops'.
There are 0 rows in 0 pages for object "sys.sysxprops".
DBCC results for 'sys.sysscalartypes'.
There are 27 rows in 1 pages for object "sys.sysscalartypes".
DBCC results for 'sys.systypedsubobjs'.
There are 0 rows in 0 pages for object "sys.systypedsubobjs".
DBCC results for 'sys.sysidxstats'.
There are 202 rows in 3 pages for object "sys.sysidxstats".
DBCC results for 'sys.sysiscols'.
There are 310 rows in 3 pages for object "sys.sysiscols".
DBCC results for 'sys.sysbinobjs'.
There are 23 rows in 1 pages for object "sys.sysbinobjs".
DBCC results for 'sys.sysobjvalues'.
There are 200 rows in 30 pages for object "sys.sysobjvalues".
DBCC results for 'sys.sysclsobjs'.
There are 14 rows in 1 pages for object "sys.sysclsobjs".
DBCC results for 'sys.sysrowsetrefs'.
There are 0 rows in 0 pages for object "sys.sysrowsetrefs".
DBCC results for 'sys.sysremsvcbinds'.
There are 0 rows in 0 pages for object "sys.sysremsvcbinds".
DBCC results for 'sys.sysxmitqueue'.
There are 0 rows in 0 pages for object "sys.sysxmitqueue".
DBCC results for 'sys.sysrts'.
There are 1 rows in 1 pages for object "sys.sysrts".
DBCC results for 'sys.sysconvgroup'.
There are 0 rows in 0 pages for object "sys.sysconvgroup".
DBCC results for 'sys.sysdesend'.
There are 0 rows in 0 pages for object "sys.sysdesend".
DBCC results for 'sys.sysdercv'.
There are 0 rows in 0 pages for object "sys.sysdercv".
DBCC results for 'sys.syssingleobjrefs'.
There are 133 rows in 1 pages for object "sys.syssingleobjrefs".
DBCC results for 'sys.sysmultiobjrefs'.
There are 111 rows in 1 pages for object "sys.sysmultiobjrefs".
DBCC results for 'sys.sysdbfiles'.
There are 2 rows in 1 pages for object "sys.sysdbfiles".
DBCC results for 'sys.sysguidrefs'.
There are 0 rows in 0 pages for object "sys.sysguidrefs".
DBCC results for 'sys.sysqnames'.
There are 91 rows in 1 pages for object "sys.sysqnames".
DBCC results for 'sys.sysxmlcomponent'.
There are 93 rows in 1 pages for object "sys.sysxmlcomponent".
DBCC results for 'sys.sysxmlfacet'.
There are 97 rows in 1 pages for object "sys.sysxmlfacet".
DBCC results for 'sys.sysxmlplacement'.
There are 17 rows in 1 pages for object "sys.sysxmlplacement".
DBCC results for 'sys.sysobjkeycrypts'.
There are 0 rows in 0 pages for object "sys.sysobjkeycrypts".
DBCC results for 'sys.sysasymkeys'.
There are 0 rows in 0 pages for object "sys.sysasymkeys".
DBCC results for 'sys.syssqlguides'.
There are 0 rows in 0 pages for object "sys.syssqlguides".
DBCC results for 'sys.sysbinsubobjs'.
There are 0 rows in 0 pages for object "sys.sysbinsubobjs".
DBCC results for 'EMP_WORK'.
There are 75 rows in 1 pages for object "EMP_WORK".
DBCC results for 'DEPARTMENT'.
There are 9 rows in 1 pages for object "DEPARTMENT".
DBCC results for 'ORG'.
There are 10 rows in 1 pages for object "ORG".
DBCC results for 'Nums'.
There are 1048577 rows in 1686 pages for object "Nums".
DBCC results for 'sales02'.
There are 32 rows in 3 pages for object "sales02".
DBCC results for 'trans01'.
There are 16 rows in 1 pages for object "trans01".
DBCC results for 'trans02'.
There are 13 rows in 1 pages for object "trans02".
DBCC results for 'sys.queue_messages_1977058079'.
There are 0 rows in 0 pages for object "sys.queue_messages_1977058079".
DBCC results for 'sys.queue_messages_2009058193'.
There are 0 rows in 0 pages for object "sys.queue_messages_2009058193".
DBCC results for 'sys.queue_messages_2041058307'.
There are 0 rows in 0 pages for object "sys.queue_messages_2041058307".
DBCC results for 'sales'.
There are 57 rows in 1 pages for object "sales".
DBCC results for 'projects'.
There are 20 rows in 1 pages for object "projects".
DBCC results for 'Emps'.
There are 32 rows in 1 pages for object "Emps".
CHECKDB found 0 allocation errors and 0 consistency errors in database 'sql911'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.