SQL Server™ 2005 Bible

Chapter 37. Maintaining the Database

In This Chapter

• Using SQL Server's Database Consistency Checker (DBCC)

• Creating database maintenance plans

The Database Consistency Checker (DBCC) commands are at the heart of database maintenance, even since the earliest versions of SQL Server. However, thanks to Microsoft's zero-maintenance initiative, SQL Server 2005 is now easier to maintain than ever before. Not only are many of the traditional database maintenance duties no longer required, but the Maintenance Plan Wizard can set up a custom set of SQL Server Agent jobs that execute an excellent database maintenance plan.

DBCC Commands

Microsoft SQL Server's primary command for database maintenance is the Database Consistency Checker (DBCC) command and its 34 options.

The first DBCC command to become familiar with is the DBCC help command, which returns the syntax with all the options for any DBCC command:

DBCC Help ('CheckDB');

Result:

CheckDB [('database_name'
  [, NOINDEX | REPAIR])]
  [WITH NO_INFOMSGS[, ALL_ERRORMSGS]
  [, PHYSICAL_ONLY]
  [, ESTIMATEONLY][, TABLOCK]]
DBCC execution completed. If DBCC printed
  error messages,contact your system
  administrator.

All DBCC commands report their activity or errors found, and then conclude with the standard execution-completed statement, including the puzzling request to report any error to the system administrator. You are the database pro. If you're running DBCC, you're the best person to handle it.

Note

Several obsolete DBCC commands and options are included ...