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.
Important
If you modify the vacuuming schedule, configure the schedule so that vacuuming runs at times of low system activity. Vacuuming consumes system resources and might cause lower application performance while the vacuumer is running.
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.
Important
Do not run the vacuumer manually while the vacuumer is running or if it is scheduled to run. Application errors might occur, with obsolete data left intact. Look at the application definition to determine the vacuuming schedule. If the application definition does not contain a vacuuming schedule, the application does not automatically remove obsolete data.
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)