The forum will be down for scheduled maintenance on Saturday, March 4 beginning at 10am EST. Actual downtime is unknown but may be several hours.

The documentation on the sa_clean_database system procedure is very careful to say "cleanable page" rather than "dirty page"... the checkpoint process handles dirty pages, not the database cleaner, they are two entirely different concepts.

Sadly, one of the examples in that Help topic makes several references to "dirty pages" when the code is dealing with cleanable pages... it's not just a matter of semantics, it's a mistreatment of an extremely complex topic (how the server handles physical data in the database) that can possibly lead developers down the wrong paths.

CREATE EVENT PeriodicCleaner
BETWEEN '9:00 am' and '5:00 pm'
     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 ) ))
     INTO @num_db_pages

-- 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;

asked 01 Mar '14, 13:10

Breck%20Carter's gravatar image

Breck Carter
accept rate: 20%

Thanks Breck for pointing this out. I have made a comment for the doc team on this page for sa_clean_database in the v16 docs. (FWIW: Putting a comment in DCX is a better place for making suggestions on improvements to the docs... but I understand why you posted this here since it is related to this recent question)

permanent link

answered 01 Mar '14, 17:48

Mark%20Culp's gravatar image

Mark Culp
accept rate: 40%

I should have done both... forgetful, lazy, pick one :)

(02 Mar '14, 04:32) Breck Carter
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 01 Mar '14, 13:10

question was seen: 797 times

last updated: 02 Mar '14, 04:32