Starts the database cleaner and sets the maximum length of time for which it can run.
sa_clean_database( [ duration ] )
duration The number of seconds that the clean operation is allowed to run. If no argument is specified, or if 0 is specified, the database cleaner runs until all pages in all dbspaces have been cleaned.
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.
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 a number of 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 inactivity.
Database cleaner statistics are available through four database properties:
CleanablePagesAdded returns the number of pages that need to be cleaned
CleanablePagesCleaned returns the number of pages that have already been cleaned
CleanableRowsAdded returns the number of rows that need to be cleaned
CleanabledRowsCleaned 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 require cleaning.
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. See CREATE EVENT statement.
DBA authority required
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 SCHEDULE START TIME '6:00 pm' ON ( 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday' ) HANDLER BEGIN CALL sa_clean_database( ); END;
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 PERIODIC_CLEANER SCHEDULE BETWEEN '9:00 am' and '5:00 pm' EVERY 1 HOURS HANDLER BEGIN DECLARE @num_db_pages INTEGER; DECLARE @num_dirty_pages INTEGER; -- Get the number of database pages SELECT (SUM( DB_EXTENDED_PROPERTY( 'FileSize', t.file_id ) - DB_EXTENDED_PROPERTY( 'FreePages', t.file_id ) )) INTO @num_db_pages FROM (SELECT file_id FROM SYSFILE) AS t; -- Get the number of dirty pages to be cleaned SELECT (DB_PROPERTY( 'CleanablePagesAdded' ) - DB_PROPERTY( 'CleanablePagesCleaned' )) INTO @num_dirty_pages; -- 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 ); END IF; END