O'Reilly logo

Microsoft® SQL Server® 2008 Bible by Uttam Parui, Mike White, Paul Nielsen

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required