You are a database administrator for an insurance firm and manage the SQL Server 2008 databases in thecompany. The company stores all customer data in a database named
Customers that resides on the SQLserver named Sql_1 . You notice that there are several allocation and consistency errors in the Cust_details table in the Customers
database. You want to repair these errors as quickly as possible. Which statement should you execute to achieve the objective?
A.
DBCC CHECKCATALOG (Customers);
B.
DBCC CHECKTABLE (‘Cust_details’, REPAIR_ALLOW_DATA_LOSS);
C.
DBCC CHECKDB (Cust_details, Customers, REPAIR_ALLOW_DATA_LOSS);
D.
DBCC CHECKALLOC (Cust_details, REPAIR_FAST);
Explanation:
You should execute the following statement:
DBCC CHECKTABLE (‘Cust_details’, REPAIR_ALLOW_DATA_LOSS);
The DBCC CHECKTABLE statement performs an integrity check on all the pages and structures that constitutethe specified table or indexed view. In this scenario, the consistency and allocation errors exist only in the Cust_details table. Therefore, to accomplish the task in the minimum possible time, you should check theintegrity at the table level. The REPAIR_ALLOW_DATA_LOSS argument used in the statement attempts toautomatically fix all the reported errors while performing the integrity check. Some data might be lost by using the REPAIR_ALLOW_DATA_LOSS argument. When you need to repair corrupt tables and do not want to affect thedatabase, you should use a
DBCC CHECKTABLE statement. You should not execute the following statement: DBCC CHECKCATALOG (Customers);
In this scenario, you only want to check the allocation and the integrity of the Cust_details table. The DBCCCHECKCATALOG statement performs integrity checks of the system tables within a database. You should not execute the following statement:
DBCC CHECKDB (Cust_details, Customers, REPAIR_ALLOW_DATA_LOSS);
This statement would generate a syntax error. You cannot specify the name specific database table in the DBCCCHECKDB statement. You can only specify a database name in this statement. Also, you should also not performthe allocation and integrity check on the whole database because, in this scenario, you must finish the task in theminimum possible time. An integrity check on the whole database will consume more time than an integrity checkon a single table. You should not execute the following statement:
DBCC CHECKALLOC (Cust_details, REPAIR_FAST);
This statement would generate a syntax error. The DBCC CHECKALLOC statement checks the consistency fordisk allocation structures for a particular database. You cannot specify the name of a table as an argument for thisstatement.Objective:
Maintaining a SQL Server DatabaseSub-Objective:
Maintain database integrity.References:
TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Technical Reference > Transact-SQLReference >DBCC (Transact-SQL) > DBCC CHECKTABLE (Transact-SQL) TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Technical Reference > Transact-SQLReference > DBCC (Transact-SQL) > DBCC CHECKDB (Transact-SQL)