Database Maintenance

[Previous] [Next]

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.

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 are among the commands most often recommended by Microsoft.

  • DBCC CHECKALLOC (DBCC NEWALLOC in SQL Server 6.5) 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 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.

Executing a Maintenance Command Using SQL Server 6.5

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:

  1. In the Connect Server dialog box, enter the name of the SQL server to which you want to connect by selecting it from the drop-down list, or by clicking the List Servers button and selecting the server from that list.
  2. click to view at full size.

    Figure 18-10. The Connect Server dialog box.

  3. Select either the Use Trusted Connection or Use Standard Security option, depending on the security mode you enabled for your SQL server. Supply a login ID and password if appropriate.
  4. Click Connect to display the Query window, shown in Figure 18-11. Enter the command you want to execute—in this case, DBCC CHECKDB.
  5. click to view at full size.

    Figure 18-11. The Query window.

  6. Choose Execute from the Query menu, or click the Execute Query button (the green arrow). The results of the query are displayed in the Results window, shown in Figure 18-12.
  7. click to view at full size.

    Figure 18-12. The Results window.

Executing a Maintenance Command Using SQL Server 7.0

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:

  1. In the Connect To SQL Server dialog box, if the SQL server is the local server, verify that the Local option is selected. Otherwise, click the Browse button to choose from a list of SQL servers located on the network. If you need to start the SQL Server service, select the Start SQL Server If Stopped check box.
  2. Figure 18-13. The Connect To SQL Server dialog box

  3. Select either Use Windows NT Authentication or Use SQL Server Authentication, depending on the security mode you enabled for your SQL server. Supply a login ID and password if appropriate.
  4. Click OK to display the Query window, shown in Figure 18-14, and enter the command that you want to execute—in this case, DBCC CHECKALLOC.
  5. click to view at full size.

    Figure 18-14. The Query window.

  6. 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 in the Results window, shown in Figure 18-15.

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.

    click to view at full size.

    Figure 18-15. The Results window.



Microsoft Systems Management Server 2.0 Administrator's Companion
Microsoft Systems Management Server 2.0 Administrators Companion (IT-Administrators Companion)
ISBN: 0735608342
EAN: 2147483647
Year: 1999
Pages: 167

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net