Database Cleaning

The second main task of the BAM service is cleaning the BAM database to avoid that the database keeps growing without end. There are two supported ways of cleaning:

  • Keeping a number of days of log data (cleaning to days),
  • Keeping a maximum size of log data (cleaning to size).

Both cleaning methods are not exclusive to each other. The cleaning to size method is run right after the cleaning to days method. The cleaning to size method can even be disabled separately if needed.

Consult the article Configuration for more specific information on how to configure this task.

Sliding Window if Partitioned Log Table

Additionally this task is also responsible for creating additional partitions to the log table if the BAM service detects the log table being partitioned. The creation and cleaning process regarding the partitions can be describe as a sliding window placed on top of the log table.

Wih every cleaner task run (on a daily basis), the adding and dropping of partitions behaves as a kind of window or view on top of the log table, sliding forward each day.

Cleaning to Days

During this execution, the service will remove all log data with a timestamp that is older than the number of days subtracted from the current date and time.

For example: let's assume the current date is 20/01/2022 (20th of January 2022), and the configuration is set to keep 10 days of data. This would mean that the cleaning task removes all log data with a timestamp before 10/01/2022 (10th of January 2022).

Partitioned Log Table

In case of a partitioned log table, full partitions are removed from the table instead of deleting single records. This makes the cleaning action a very quick and efficient process.

Cleaning to Size

This cleaning method removes the oldest log data until the maximum of log data size is reached. In other words: first a check is done on the current amount of log data present in the database, compares that value to the configured value in the settings, then calculates the number of records to delete and executes the removal in batches if needed.

Note

The comparison of size is done on the total amount of data inside the database, not the database size itself.

For example: consider a 15 GByte database contains 10 GByte of data. The cleaning method will compare the setting with the 10 GByte data size.

Keep in mind that this cleaning step comes on top of the cleaning to days to keep. This could cause, even when configuration settings state to keep at least 10 days of log data in the database, that due to the maximum size value actually more data will be removed to satisfy that constraint.

Disabling this subtask can be done by setting the configuration value to 0 or 0.0. See Configuration for more information.

Cleaning From Ordinary Table

For a normal log table, the data removal is done on a by record basis.

Important

When using record deletion, cleaning to size is the slowest of both cleaning methods. This only exists to avoid having too much information in the BAM database, for example when having the risk of running out of storage space for the database, or having a fixed limit on the database file size like with the SQL Server Express editions.

If this method is run at a daily basis, it may means that more logging is generated each day and it could be useful to decrease the setting on the cleaning to days method.

Cleaning From Partitioned Table

On a partitioned table, entire partitions are dropped at once to speed up the cleaning process. It may remove more data from the database then absolutely required but this way keeping the database size low is a easy to go to solution without any speed impact.

Update Table Statistics Regularly

Table statistics help the SQL Server query optimiser selecting the proper query execution plan to retrieve, update or remove data. As such, update the statistics of the tables in the BAM database on a weekly basis.

Updating those statistics can be done through either:

  • A database job (preferably, and advised by Microsoft),
  • Or using one of the built-in Database Maintenance Tasks of the BAM service.

Consult the Database Maintenance Tasks article for more information about table statistics.