Microsoft Systems Management Server 2003 : Maintaining the Database Through Microsoft SQL Server - Database Maintenance

9/15/2012 3:01:36 AM

SMS Database Components

The SMS database contains data objects and their attributes arranged in an organized fashion. Each database consists of four main elements, as follows:

  • Tables

  • Indexes

  • Event triggers

  • Stored procedures

A table is a database object that contains data in the database organized as a collection of rows and columns. Each row in the table represents a data record, and each column represents an associated field for that record. Generally, each table defines one or more columns (fields) as a key entry that can be used to link tables for the purpose of sorting, searching, and reporting on data in the database. SMS 2003 contains more than 200 predefined tables.

An index can be thought of as a companion object to a table. Separate from the table, an index functions much like an index in a book, providing a quick way to search and locate data. If an index is available for a table, your queries will exhibit better performance. If no index is available, the entire table must be searched. The two index types, clustered index and nonclustered index, determine how the data records are searched. The nonclustered index is similar to a book index—each entry contains a bookmark that tells the database where to find the data that corresponds to the key in the index. For example, when you look up an entry such as “site-server” in a book index, you might be directed to several locations in the book. The index doesn’t represent the order in which the data is stored in the book. The clustered index is similar to a telephone directory—it contains the data itself, not just the index keys. The clustered indexes are usually based on a primary key defined in each table. Each index entry corresponds to the order in which the data is stored in the book. Like looking up a name in the phone book, when you find the name, you also find the address and phone number.

When you execute a query, you’re searching tables for a specific value based on the criteria you enter, using indexes whenever possible. The query result represents the records or data values obtained from records contained in one or more tables. SMS 2003 contains more than 250 predefined indexes.

An event trigger is a Transact-SQL statement that’s executed whenever a specific event occurs in a given table. The Transact-SQL language is used for communication between applications and SQL Server. It’s an enhancement to SQL and provides a comprehensive language for defining tables, maintaining tables, and controlling access to data in the tables. If data is added, deleted, or modified within a specific table, an event trigger will be executed. SMS uses event triggers to notify its components that an event has occurred that a particular component needs to attend to. Event triggers cause components to “wake up” in response to an event rather than waiting for a specific polling cycle to occur. Obviously, this translates to better performance for the site server. For example, when you change a site setting, an event trigger causes SQL Monitor to write a wake-up file in the Hierarchy Manager inbox. SMS 2003 uses over 200 event triggers.

A stored procedure is a group of Transact-SQL statements that have been compiled into a single executable routine. You could think of a stored procedure as a kind of batch file for SQL Server. When a SQL Server event activates a trigger, a corresponding stored procedure is executed that writes the wake-up file into the appropriate SMS component’s inbox on the site server. Two common stored procedures that you might execute are SP_SPACEUSED, which displays the amount of reserved and actual disk space used by a table in the database or by the entire database, and SP_WHO, which identifies SQL Server connections (users and processes) currently in use. Both are included with SQL’s master database.

SQL Server Management Tools

A quick scan of the SQL Server program group reveals that many tools are installed to assist the SQL Server administrator in maintaining the server running SQL. Unless you’re the SQL Server administrator as well as the SMS administrator or you get the necessary education to fully understand and appreciate the product, you’ll probably use only two or three of these tools. The SQL Server 2000 program tools are described in Table 1.

Table 1. SQL Server 2000 program tools
Analysis ServicesA folder that provides access to the Analysis Manager, another link to Books Online and MDX Sample Application. The Analysis Manager is a console application that provides an interface for accessing and managing analysis servers. For more information about Analysis Services, please refer to Books Online.
Books OnlineProvides an online version of the documentation set SQL Server Books Online with full searching capabilities.
Client Network UtilityUsed to set the custom server connection, DB-Library, and Net-Library configuration for clients.
Configure SQL XML Support In IISUsed to define and register a new virtual directory in Internet Information Services (IIS) so that HTTP can be used to access a SQL Server 2000 database.
Enterprise ManagerMMC snap-in that facilitates the configuration of the server running SQL and the management of SQL Server databases, including devices and databases, space usage, backups and restores, permissions, data import and export, and so on, through object-level folders.
Import And Export DataStarts the Data Transformation Service Import/Export Wizard, designed to facilitate the import, export, and conversion of data from various data formats.
ProfilerUsed to monitor server events, procedure calls, and other real-time server activity; Profiler can also filter events and direct output to the screen, file, or table.
Query AnalyzerGraphical query interface used to execute maintenance tasks such as Transact-SQL statements or stored procedures.
Server Network UtilityUsed to configure SQL Server to use Net-Libraries and to specify the network protocol stacks on which the server will listen for SQL Server clients’ requests.
Service ManagerStarts, stops, and pauses the SQL Server, SQL Server Agent, and MS DTC services. Also implemented as a taskbar program.

Many SQL Server maintenance tasks specific to the SMS database can be configured and scheduled to run through the SMS Administrator Console, including a complete site server backup . You can also effect database backups by using a third-party backup program capable of including SQL Server databases as part of its backup routine. Consequently, as an SMS administrator, you’re most likely to use Enterprise Manager to perform any additional or advanced maintenance tasks. Through this interface you can create database devices, manage space usage, configure the server, schedule events, back up and restore the database, and so on. 

As with most Microsoft BackOffice applications, when you install SQL Server several performance objects and counters are included to assist you in evaluating the ongoing performance and resource use of your server running SQL as well as to facilitate the troubleshooting of specific performance-related problems. To view the available SQL Server performance objects and counters, you can start the Windows System Monitor utility, accessible through the Performance Console. For example, the SQL Server:Database object has a counter called Data File(s) Size (KB) that will help you monitor the cumulative size of your databases, such as Tempdb.

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.

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.


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. 

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 1, and follow these steps:

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 1. The Connect To SQL Server dialog box.

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.

Click OK to display the Query window, shown in Figure 2. 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.

Figure 2. The Query Analyzer Query window.

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 3.

Figure 3. The Query Analyzer Results window.

  •  Microsoft Systems Management Server 2003 : Maintaining the Database Through Microsoft SQL Server - SQL Server Components
  •  Microsoft Visual Basic 2008 : Processing and Storing Data in SQL Server 2005 - Optimizing the LINQSQL Class
  •  ASP.NET 4 in VB 2010 : ADO.NET Fundamentals (part 6) - Disconnected Data Access
  •  ASP.NET 4 in VB 2010 : ADO.NET Fundamentals (part 5) - Direct Data Access - Updating Data
  •  ASP.NET 4 in VB 2010 : ADO.NET Fundamentals (part 4) - Direct Data Access - The DataReader
  •  ASP.NET 4 in VB 2010 : ADO.NET Fundamentals (part 3) - Direct Data Access - Creating a Connection, The Select Command
  •  ASP.NET 4 in VB 2010 : ADO.NET Fundamentals (part 2) - SQL Basics
  •  ASP.NET 4 in VB 2010 : ADO.NET Fundamentals (part 1) - Understanding Databases, Configuring Your Database
  •  SQL Server 2008 : Failover clustering - Installing a clustered SQL Server instance
  •  SQL Server 2008 : Failover clustering - Clustering topologies and failover rules
    Video tutorials
    - How To Install Windows 8

    - How To Install Windows Server 2012

    - How To Install Windows Server 2012 On VirtualBox

    - How To Disable Windows 8 Metro UI

    - How To Install Windows Store Apps From Windows 8 Classic Desktop

    - How To Disable Windows Update in Windows 8

    - How To Disable Windows 8 Metro UI

    - How To Add Widgets To Windows 8 Lock Screen

    - How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010
    programming4us programming4us