Export (0) Print
Expand All
Expand Minimize
This topic has not yet been rated - Rate this topic

NSVacuum (Transact-SQL)

SQL Server 2005

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).

ms179860.note(en-US,SQL.90).gifImportant:
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.


[ schema_name . ] NSVacuum
    [@SecondsToRun = ] max_vacuuming_time
[ @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.

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.

Execute permissions default to members of the NSRunService and NSVacuum database roles, db_owner fixed database role, and sysadmin fixed server role.

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.

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;

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.