Microsoft® SQL Server® 2008 Bible

Chapter 42. Maintaining the Database

IN THIS CHAPTER

• Using SQL Server's Database Console Commands (DBCC)

• Creating database maintenance plans

The previous chapter covered database recovery planning. This chapter explores various database maintenance tasks that need to be performed regularly, such as database backups, database integrity checks, and index maintenance. It also discusses database maintenance using Transact-SQL Database Console Commands (DBCC) and Maintenance Plans.

DBCC Commands

Microsoft SQL Server Database Console Commands (DBCC) are used for checking database integrity, performing maintenance operations on databases, tables, indexes, and filegroups, and collecting and displaying information during troubleshooting issues.

The first DBCC command to become familiar with is the DBCC HELP command, which returns the syntax and all the options for any DBCC command. The following command returns the syntax for DBCC CHECKDB:

DBCC HELP ('CHECKDB');

Result:

dbcc CHECKDB
(
    { 'database_name' | database_id | 0 }
    [ , NOINDEX
    | { REPAIR_ALLOW_DATA_LOSS
    | REPAIR_FAST
    | REPAIR_REBUILD
    } ]
)

    [ WITH
        {
            [ ALL_ERRORMSGS ]
            [ , [ NO_INFOMSGS ] ]
            [ , [ TABLOCK ] ]
            [ , [ ESTIMATEONLY ] ]
            [ , [ PHYSICAL_ONLY ] ]
            [ , [ DATA_PURITY ] ]
            [ , [ EXTENDED_LOGICAL_CHECKS  ] ]
        }
    ]
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.

What's New in SQL Server Database Maintenance?

In earlier versions of SQL Server, DBCC stood for "Database Consistency Checker"—now it is renamed ...