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 out of 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, many of these same tasks, and database backup and restores, can be performed through SQL Server. In this section, we'll review the commands used for performing the essential maintenance tasks, and how to perform these tasks in SQL Server 6.5 and SQL Server 7.0.
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 are among the commands most often recommended by Microsoft.
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 both SQL Server 6.5 and 7.0.
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 "Backing Up and Restoring the Database" later in this chapter. We'll discuss how to run these commands in the following sections.
To execute a database maintenance command in SQL Server 6.5, launch the ISQL/w program tool, found in the SQL Server 6.5 programs group, to display the Connect Server dialog box (shown in Figure 18-10) and follow these steps:
Figure 18-10. The Connect Server dialog box.
Figure 18-11. The Query window.
Figure 18-12. The Results window.
To execute a database maintenance command in SQL Server 7.0, launch the Query Analyzer tool found in the SQL Server 7.0 programs group to display the Connect To SQL Server dialog box, shown in Figure 18-13, and follow these steps:
Figure 18-13. The Connect To SQL Server dialog box
Figure 18-14. The Query window.
TIP
Each of the DBCC commands and stored procedures may 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.
Figure 18-15. The Results window.