Moebius Strip

Table Pattern: Rotating Log / Ring Buffer

Most database systems need some form of log table to keep track of events, for example for auditing purposes. To avoid the log growing forever, it is often a good idea to regularly rotate old log entries out of this table. For small log tables, running a DELETE statement works well for this purpose. However, as the log throughput grows, it is often preferable to use partition switching instead. In this blog, I will show you an implementation of a rotating log table.

The Table Structure

A rotating log is essentially a ring buffer where the writes happen at one part of the buffer and the old records are removed at the other end. For the implementation example, we will assume you wish to rotate the log daily, removing and aggregating old entries and making sure that the new entries are added to a place that is easy to rotate in the future.

It is possible to traditional partition “sliding window” techniques and add new partitions daily to achieve this. However, I find the splitting, merging and switching a bit clunky for this purpose. Instead, I prefer to implement a rotating log like this:

 

With this design, I never have to alter the partition function or scheme, I can simply switch out the old parts of the ring buffer.

For the purposes of this implementation, let me add a few more tables:

The RingBuffer_SwitchTarget will be used to switch out the old entries in RingBuffer. The RingBuffer_Aggregate is used to keep an aggregate of old data, which of course will be much smaller.

To illustrate, here is the flow we are implementing:

Ring Buffer Implementation

 

Rotating the Log

With the table structures in place, we can now write the procedure that rotates the log. Using a few simple math tricks, we can rotate and the log with a simple piece of code:

And that’s really all there is to it.

Summary

In this blog, I have shown you how to implement a rotating log without the need for a lot of partition function management. Using a ring buffer of dates, a full day of data can be rotated around with a single SWITCH statement.

The ring buffer mechanism can also be used for heaps or tables with only a cluster index that are so large that online index rebuilds don’t fit in memory and tempdb. Instead of rebuilding the entire table, each entry in the ring buffer can be rebuilt, one at a time, in a rotating manner. It is trivial to modify the above stored procedure to implement this functionality.