Scheduling Maintenance Tasks

---

You can schedule several of the maintenance tasks mentioned above to run on your timetable through the SMS Administrator Console. You can find these tasks in the Site Maintenance node 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. You can view database size using the SQL Enterprise Manager, of course, but you can also determine it 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 node under Site Settings, then Site Maintenance in the SMS Administrator Console and select it.

2. Right-click the folder, choose New from the context menu, and then choose SQL Command to display the SQL Command Properties dialog box shown in Figure 18.1.

3. Enter a descriptive name for the command.

   
   Figure 18.1: The SQL Command Properties dialog box.

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.

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 node. 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 might 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 18.2 shows the results of the SQL command created in the previous example.

Figure 18.2: Results of running the SP_SPACEUSED SQL stored procedure as a SQL command.

The first line of data (beginning with SMS_333) 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, is found in the Tasks node in the SMS Administrator Console. The Tasks folder contains 13 predefined tasks; you can’t add tasks to this list. Table 18.1 describes these predefined tasks.

Table 18.1: Database maintenance tasks

Task	Description
Backup SMS Site Server	Performs a comprehensive backup of the SMS site database, the software metering database, the \SMS directory on the site server, and the SMS and NAL registry keys on the site server. This task isn’t enabled by default.
Rebuild Indexes	Rebuilds indexes created on database tables that are used to more efficiently retrieve data. Enabled by default.
Monitor Keys	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 hasn’t 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 seven days by default and runs every day. Enabled by default.
Delete Aged Discovery Data	Deletes all discovery data records (DDRs) that haven’t 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 haven’t been updated within a specified period of days (90 days, by default). By default, this task is enabled and runs every Saturday.
Delete Aged Software Metering Data	Deletes metered software data that’s older than five days to conserve space in the SMS database. This task is enabled by default.
Delete Aged Software Metering Summary Data	Deletes metered software summary data that’s older than 270 days to conserve space in the SMS database. This task is enabled by default.
Delete Inactive Client Discovery Data	Deletes all client records that haven’t received an updated DDR within the specified number of days—for example, through Heartbeat Discovery. This task is useful when you’re using Active Directory Discovery to create DDRs for computers. When you delete a computer in Active Directory directory service, its Active Directory record might remain for some time before being purged. As a result, SMS Active Directory Discovery would continue to report the computer as a valid client, even though the computer is no longer physically present on the network.
Summarize Software Metering File Usage	Enables summarization of collected software metering data to conserve space in the SMS database. This task is enabled by default.
Summarize Software Metering Monthly Usage Data	Enables summarization of collected monthly software metering data to conserve space in the SMS database. This task is enabled by default
Clear Install Flag	Directs SMS to clear the install flag for clients that have been uninstalled. The install flag identifies to SMS those clients that have been installed as SMS clients. When the client is uninstalled, the install flag isn’t automatically removed, and the client can’t be successfully reinstalled. This task clears the flag so that a client can be reinstalled. This task isn’t enabled by default.

Notice that 10 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 the task and choose Properties from the context menu. In the Task Properties dialog box, select or clear the Enable This Task option.) For example, you could enable the Backup SMS Site Server task to schedule a regular backup of the SMS database (and site server) without having to do so in SQL Server.

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 registry on the site server—all necessary to fully recover a failed site server. This task is discussed in more detail in the section entitled “Backing Up the Site Server” later in this chapter.

---