Scheduling Maintenance Tasks

[Previous] [Next]

Several of the maintenance tasks mentioned above can be scheduled to run on your timetable through the SMS Administrator Console. These tasks can be found in the Database Maintenance folder under Site Settings. Two types of database maintenance objects can be configured: SQL commands and tasks.

Scheduling SQL Commands

No predefined SQL commands are available for you to schedule; you must configure these commands yourself. For example, among the recommended weekly tasks is a database size check. Database size can be viewed using the SQL Enterprise Manager, of course, but it can also be determined by executing the SQL stored procedure SP_SPACEUSED.

CAUTION

---

Before running any SQL stored procedure, be sure to consult the SQL Server documentation for correct syntax and usage.

You can configure this SQL stored procedure to run according to your defined schedule and generate a report based on its results. To do so, follow these steps:

1. Navigate to the SQL Commands folder under Database Maintenance in the SMS Administrator Console and select it.
2. Right-click on the folder, choose New from the context menu, and then choose SQL Command to display the SQL Command Properties window, shown in Figure 17-1.
3. Enter a descriptive name for the command.



Figure 17-1. The SQL Command Properties window.

4. Verify that Enable SQL Command is selected. Enter the command name in the SQL Command text box. Be sure to use the appropriate syntax or the command will fail.
5. In the Log Status To text box, enter the path and filename of the text file you want the command results written to. This must be an existing share and path.
6. Define your schedule, and then click OK.

The SQL command you created will now be listed in the SMS Administrator Console when you select the SQL Commands folder. You might consider scheduling other SQL maintenance commands such as DBCC CHECKDB, DBCC CHECKALLOC, DBCC CHECKCATALOG, and DBCC UPDATEUSAGE. For example, if you recently reindexed the database, you may want to run the DBCC UPDATEUSAGE command to reset space usage reporting so that SP_SPACEUSED returns accurate data. You could schedule this command to run with SP_SPACEUSED or separately, under its own schedule.

When the SQL command is run, it will write the results of its execution to the log file you specified. Figure 17-2 shows the results of the SQL command created in the previous example.

Figure 17-2. Results of running the SP_SPACEUSED SQL stored procedure as a SQL command.

The first line of data (beginning with SMS_A01) indicates the total database size and the available free space. The second line of data indicates the amount of reserved space, breaking this value down into the amount of space used by data, the index size, and unused space.

Scheduling Tasks

The other type of database maintenance objects you can schedule, tasks, are found in the Tasks folder in the SMS Administrator Console. The Tasks folder contains twelve predefined tasks; you cannot add tasks to this list. Table 17-1 describes these predefined tasks.

Notice that seven of these tasks are already enabled by default to ensure that vital tasks such as rebuilding indexes are carried out on a regular schedule. All the deletion tasks are designed to keep the database from becoming too large and unwieldy. You can, of course, modify the schedule and disable or enable any of these tasks as you choose. (To enable or disable a task in the Tasks folder, right-click on the task and choose Properties from the context menu. In the Task Properties window, then check or clear the Enable This Task option.) For example, you could enable the Export Site Database and Export Site Database Transaction Log tasks to schedule a regular backup of the SMS database without having to do so in SQL Server. However, as indicated in Table 17-1, you must have already defined a backup device through the SQL Server Enterprise Manager. We'll discuss how to define a backup device in the section "Backing Up the Site Database" later in this chapter.

Table 17-1. Database maintenance tasks

Task	Description
Export Site Database	Exports the SMS site database to a predefined SQL backup device.
Export Site Database Transaction Log	Exports the SMS Site Transaction log to a predefined SQL backup device. Do not use this command if Truncate Log On Checkpoint is enabled for the database (the default).
Export Software Metering Database	Exports the SMS software metering database to a pre-defined SQL backup device.
Export Software Metering Transaction Log	Exports the SMS Software Metering Transaction log to a predefined SQL backup device. Do not use this command if Truncate Log On Checkpoint is enabled for the database (the default).
Backup SMS Site Server	Performs a comprehensive backup of the SMS site data base, the software metering database, the \SMS directory on the site server, and the SMS and NAL registry keys on the site server.
Update Statistics	Rebuilds statistics created by SQL Server about data contained in indexes that enables the SQL Query Optimizer to determine the most efficient way to run a query. Enabled by default.
Rebuild Indexes	Rebuilds indexes created on database tables that are used to more efficiently retrieve data. Enabled by default.
Monitor Keys And Recreate Views	Monitors the integrity of primary keys used to uniquely identify all SMS database tables. Enabled by default.
Delete Aged Inventory History	Deletes all hardware inventory that has not been updated within a specified period of days (by default, 90 days). By default, this task is enabled and runs every Saturday.
Delete Aged Status Messages	Deletes status messages older than 7 days by default, and runs every day. Enabled by default.
Delete Aged Discovery Data	Deletes all discovery data records (DDRs) that have not been updated within a specified period of days (by default, 90 days). By default, this task is enabled and runs every Saturday.
Delete Aged Collected Files	Deletes all collected files that have not been updated within a specified period of days (90 days, by default). By default, this task is enabled and runs every Saturday.

The most powerful of these tasks is Backup SMS Site Server. This is by far the most comprehensive backup routine available for SMS. It backs up not only the SMS site database and software metering database, but also the full SMS directory structure on the site server and the SMS and NAL keys in the Windows NT registry on the site server—all necessary to fully recover a failed site server. This task is discussed in more detail in the section "Backing Up the Site Server" later in this chapter.