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)

Help and Information

Getting SQL Server 2005 Assistance