Removing Obsolete Application Data

Large amounts of data in a Microsoft SQL Server Notification Services application database can hinder performance in two ways. First, large tables can hurt query performance. Second, when data files become large, SQL Server might automatically increase the file size, which consumes system resources.

Notification Services uses a process called vacuuming to remove obsolete data from application databases. Vacuuming is a process that runs automatically for each application according to a schedule defined in the application definition.

When you define an application, you specify the application's data retention age and the vacuuming schedule. The data retention age is the minimum age at which event and notification data can be removed. For example, a retention age of one day specifies that data cannot be removed until it is at least one day old. The vacuuming schedule specifies one or more start times and durations for vacuuming. For more information, see Configuring Data Removal.

Data is removed on a quantum-by-quantum basis, meaning that all events and notifications processed during a quantum are removed as a unit.

When applications are running, verify that the vacuuming process is running as scheduled. If vacuuming is running but the application requires a different vacuuming configuration, you can modify vacuuming by editing the application definition and updating the application to apply the changes. If necessary, you can run vacuuming manually.

Verifying That Vacuuming Is Running

For every application, you should periodically check to ensure that vacuuming is running as scheduled. If large amounts of data accumulate in the application database, application performance might decline, and the database might run out of disk space. Notification Services provides stored procedures and performance counters for monitoring applications, which you can use to monitor vacuuming.

• The NSSnapshotApplications stored procedure returns data about when the vacuuming process last ran and how many event and notification rows were removed.
• The NS$instanceName: Vacuumer performance object provides performance counters that are relevant to vacuumer processing. Monitor the counters provided by this object to determine whether the vacuumer component is timing out, and to determine how many quanta have been vacuumed or are ready to be vacuumed.

Vacuuming removes notifications only if they have been delivered or if they have no additional retry attempts. Vacuuming removes events only if they have been processed. Vacuuming removes quanta only if the notifications and events associated with them have been removed. If data that you expect to be removed is not yet removed, there is likely a dependent piece of data that cannot yet be removed.

Modifying the Vacuuming Schedule

To adjust the vacuuming schedule for a Notification Services application, you must edit the application definition and then update the application to apply the changes to the application database. For more information about defining a vacuuming schedule, see Configuring Data Removal.

Running Vacuuming Manually

In general it is best to let the vacuumer run as scheduled. If vacuuming is causing slow system performance or is not removing enough data, change the schedule in the application definition and then update the application. Run vacuuming manually only if you determine that vacuuming has not run as scheduled and you need to remove data for space or performance reasons.

Use the NSVacuum stored procedure, located in the application database, to run vacuuming manually. For more information, see NSVacuum (Transact-SQL).

See Also

Concepts

Configuring Data Removal
Updating an Application

Other Resources

Vacuum Element (ADF)
NSVacuum (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance