Previous Section
 < Day Day Up > 
Next Section


Database Maintenance

As we’ve seen, some database maintenance tasks should be carried out on a regular basis—either daily, weekly, or monthly. For example, every day you might execute a database backup and review status messages and system performance. Once a week, you might monitor database size usage and purge old data from the database. Once a month, you might verify the integrity of the database backup by testing a restore of the database. Once a month, you might also review security and make appropriate adjustments such as resetting account passwords.

Most of these tasks can be performed or configured and scheduled to run through the SMS Administrator Console. However, you can perform many of these same tasks, and database backup and restores, through SQL Server. In this section we’ll review the commands used for performing the essential maintenance tasks and how to perform these tasks.

Commands Used for Performing Essential Maintenance Tasks

Some of the database integrity checking and space monitoring commands you might consider running on a weekly or monthly basis are listed below. These database consistency checker (DBCC) commands are certainly not the only ones available, but they’re among the commands that Microsoft most often recommends.

  • DBCC CHECKALLOC Checks the specified database to verify that all pages have been correctly allocated and used; reports the space allocation and usage.

  • DBCC CHECKDB Checks every database table and index to verify that they are linked correctly, that their pointers are consistent, and that they are in the proper sort order.

  • DBCC CHECKCATALOG Checks consistency between tables and reports on defined segments.

  • DBCC UPDATEUSAGE Used with a recently reindexed database to reset space usage reporting so that SP_SPACEUSED returns accurate data. You could schedule this command to run with SP_SPACEUSED or to run separately under its own schedule.

    Tip 

    To obtain a complete list and explanation of all Transact-SQL statements and stored procedures, including the DBCC commands, query the online help for SQL Server 2000.

Before you run any DBCC command, remember to set SQL Server to single-user mode. We’ll look at how to start SQL Server in single-user mode in the section entitled “Backing Up and Restoring the Database” later in this chapter. We’ll discuss how to run these commands in the following sections.

Executing a Maintenance Command Using SQL Server 2000

To execute a database maintenance command in SQL Server 2000, launch the Query Analyzer tool found in the Microsoft SQL Server programs group to display the Connect To SQL Server dialog box, shown in Figure 19.5, and follow these steps:

  1. In the Connect To SQL Server dialog box, click the Browse button to choose from a list of servers running SQL located on the network. Select [Local] to specify the use of the local server. If you need to start the service, select the Start SQL Server If It Is Stopped check box.


    Figure 19.5: The Connect To SQL Server dialog box.

  2. Select either Windows Authentication or SQL Server Authentication, depending on the security mode you enabled for your server running SQL. Supply a login name and password if appropriate.

  3. Click OK to display the Query window, shown in Figure 19.6. Select the database you want to query against from the list on the left and enter the command that you want to execute—in this case, DBCC CHECKDB in the Query window.

    Click To expand
    Figure 19.6: The Query Analyzer Query window.

  4. Choose Execute from the Query menu or click the Execute Query button (the green arrow) on the toolbar. The results of the query are displayed on the bottom half of the Query window, shown in Figure 19.7.

    Click To expand
    Figure 19.7: The Query Analyzer Results window.

    Tip 

    Each of the DBCC commands and stored procedures might have additional syntax options that will affect how the command is executed. Refer to your SQL Server documentation for a complete description of each command and its syntax.



Previous Section
 < Day Day Up > 
Next Section