Use this optional UNSIGNED INTEGER parameter to specify the number of seconds that the clean operation is allowed to run.
The default is 0 which is interpreted to mean that no limit is imposed on the duration that the cleaner runs.
The database cleaner is an internal task that runs on a default schedule. You can use this system procedure to force the database
cleaner to run immediately and to specify how long the cleaner can run each time it is invoked. When the duration is 0, the
database cleaner runs until all pages in all dbspaces have been cleaned.
If you use this system procedure to start the database cleaner while a database is being validated, the database cleaner does
not run until validation is complete.
Some database tasks, such as processing snapshot isolation transactions, index maintenance, and deleting rows, can execute
more efficiently if some portions of the request are deferred to a later time. These deferrable activities typically involve
cleanup by removing deleted, historical, and otherwise unnecessary entries from database pages, or reorganizing database pages
for more efficient access.
Postponing some of these activities not only allows the current request to finish more quickly, it potentially allows cleanup
to occur when the database server is less active. These unnecessary entries are identified so that they are not visible to
other transactions; however, they do take up space on a page, and must be removed at some point.
The database cleaner performs any deferred cleanup activities. It is scheduled to run every 20 seconds. When it is invoked,
the database cycles sequentially through the database's dbspaces, examining and cleaning each cleanable page before moving
on to the next one. When invoked automatically by the database server, the database cleaner is a self-tuning process. The
amount of work that the database cleaner performs, and the duration for which it executes, depend on several factors, including
the fraction of outstanding cleanable pages in a dbspace, the current amount of activity in the database server, and the amount
of time that the database cleaner has already spent cleaning. If, after running for 0.5 seconds, the cleaner detects active
requests in the server, it stops and reschedules itself to execute at its regular interval. The database cleaner attempts
to process pages when there are no other requests executing in the server, and therefore takes advantage of periods of server
Database cleaner statistics are available through four database properties:
returns the number of pages that need to be cleaned
returns the number of pages that have already been cleaned
returns the number of rows that need to be cleaned
returns the number of rows that have already been cleaned
The difference between the values of CleanablePagesAdded and CleanablePagesCleaned indicates how many database pages still
You can use the sa_clean_database system procedure to configure the database cleaner to run until all the pages in a database
are cleaned, or to specify a maximum duration for the database cleaner to run.
To further customize the behavior of the database cleaner, you can set up an event that starts the database cleaner if the
number of pages or rows that need to be cleaned exceed a specified threshold.
The following example sets the duration of the database cleaner to 10 seconds:
CALL sa_clean_database( 10 );
The following example creates a scheduled event that runs daily to allow the database cleaner to run until all pages in the
database are cleaned:
CREATE EVENT DailyDatabaseCleanup
START TIME '6:00 pm'
ON ( 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday' )
CALL sa_clean_database( );
The following example forces the database cleaner to run when 20% or more of the pages in the database need to be cleaned:
CREATE EVENT PeriodicCleaner
BETWEEN '9:00 am' and '5:00 pm'
EVERY 1 HOURS
DECLARE @num_db_pages INTEGER;
DECLARE @num_dirty_pages INTEGER;
-- Get the number of database pages
SELECT (SUM( DB_EXTENDED_PROPERTY( 'FileSize', t.dbspace_id ) -
DB_EXTENDED_PROPERTY( 'FreePages', t.dbspace_id ) ))
FROM (SELECT dbspace_id FROM SYSDBSPACE) AS t;
-- Get the number of dirty pages to be cleaned
SELECT (DB_PROPERTY( 'CleanablePagesAdded' ) -
DB_PROPERTY( 'CleanablePagesCleaned' ))
-- Check whether the number of dirty pages exceeds 20% of
-- the size of the database
IF @num_dirty_pages > @num_db_pages * 0.20 THEN
-- Start cleaning the database for a maximum of 60 seconds
CALL sa_clean_database( 60 );