NSVacuum (Transact-SQL)
Manually runs the vacuuming process, removing obsolete data from the application database. If the current vacuuming schedule, as defined in the application definition file (ADF), is not adequate, it is best to modify the schedule and update the application. Running vacuuming manually is recommended only when absolutely necessary (for example, if the database is running out of disk space).
Important
Do not run the vacuuming process manually when it is running or is scheduled to run. Application errors might occur, resulting in no data being removed.
Syntax
[ schema_name . ] NSVacuum
[@SecondsToRun = ] max_vacuuming_time
Arguments
- [ @SecondsToRun =] max_vacuuming_time
Is the maximum number of seconds the NSVacuum stored procedure can run. If the vacuum process removes all data before this time, vacuuming stops at that point. If the vacuum process is not finished within this time, it stops without removing all obsolete data. The next time this stored procedure runs, vacuuming will resume at the point where it previously stopped. max_vacuuming_time is int and has no default value.
Remarks
Microsoft SQL Server Notification Services creates the NSVacuum stored procedure in the application database when you create the instance. When you update the application, Notification Services recompiles the stored procedure.
This stored procedure is in the application's schema, which is specified by the SchemaName element of the application definition file (ADF). If no schema name is provided, the default schema is dbo.
Use the NSSnapshotApplications stored procedure or the **NS$**instance_name: Vacuumer performance object to monitor vacuuming.
Permissions
Execute permissions default to members of the NSRunService and NSVacuum database roles, db_owner fixed database role, and sysadmin fixed server role.
Result Set
| Column Name | Data Type | Description |
|---|---|---|
Status |
int |
The current status of vacuuming. Possible values are 0 (running), 2 (completed), and 3 (time limit exceeded). You will not receive the value 0 when running vacuuming manually. |
QuantumsVacuumed |
int |
Number of quanta successfully removed from the database during the current vacuuming period. |
QuantumsRemaining |
int |
Number of quanta that could have been removed, but were not removed because the time limit was exceeded. |
Examples
The following example runs the vacuum process for five minutes. The stored procedure (like all other objects for this application) is in the Stock schema, as specified in the SchemaName element of the ADF.
EXEC dbo.NSVacuum
@SecondsToRun = 300;
See Also
Reference
NSSnapshotApplications (Transact-SQL)
Other Resources
NS$instance_name: Vacuumer Object
Removing Obsolete Application Data
Vacuum Element (ADF)
SchemaName Element (ADF)