SQL Server

Database Recovery Methods Part 2

Buffering of Disk Blocks

The recovery procedure is frequently carefully tangled with operating system (OS) functions in specific, the buffering as well as caching of disk pages in main memory. Naturally, one (1) or more (N) disk pages which include the information items to be modified are cached into main memory buffers and then updated in memory prior to start writing back to disk again. The caching of disk pages is usually an operating system (OS) job; on the other hand as of its significance to the competence of recovery procedures, it is controlled by the Database Management System (DBMS) by means of invoking the low level operating systems (OS) routines. In common, it is suitable to think through recovery in terms of the database disk pages or blocks. Normally a group of in-memory buffers, called the Database Management System (DBMS) cache, is saved under the control of the Database Management System (DBMS) for the purpose of holding these buffers. A directory for the cache is castoff to retain a track of which database items are in the buffers.

This can be a relation or table of (Disk_Page_Address, Buffer_Location) data. At the time when Database Management System (DBMS) requires to perform any action on a number of listed items, it mainly examines the cache directory to decide whether the disk page covering the items is in the cache or not. When it is not covering, at that time the item should be found on disk, as well as the suitable disk pages are copied into the cache. It might be essential to swap or else flush a number of cache buffers to make space obtainable for the fresh item. Various page replacement strategies from operating systems (OS), like as Least Recently Used (LRU) or First In First Out (FIFO), can be castoff to select the buffers for replacement. Related with every single buffer in the cache is an unclean that can be comprised of the directory entry, to specify whether or not the buffer has been altered. At a time when page is first read from the database disk into a cache buffer, the cache directory is updated with the fresh disk page address, as well as the dirty bit is set to a zero (0). The moment the buffer is altered, the dirty bit for the matching directory entry is set to 1 (one). As soon as the buffer contents are substituted or flushed from the cache, the contents should first (1st) be written back to the equivalent disk page only when it’s dirty bit is equal to 1. Additional bit, known as the pin – unpin bit, also require a page in the cache to be pinned bit value equal to 1 (one) when it cannot be written back to disk as until now.

Two (2) key approaches can be used at the time of flushing an amended buffer back to disk. The first (1st) approach is well-known as in-place updating, it writes the buffer back to the identical original disk location, and therefore overwriting the previous data of any altered data objects on disk, Therefore, a sole copy of every single database disk block is preserved.

The second (2nd) approach is known as shadowing, it writes an updated buffer at a dissimilar disk location, and hence many versions of data objects can be preserved. On the whole, the previous value of the data object before updating is known as the before image (BFIM), in addition to the fresh value of data after updating is known as the after image (AFIM). In the second (2nd) approach which is shadowing, both the before image (BFIM) and the after image (AFIM) can be saved on disk; therefore, it is not severely essential to preserve a log for recovering the data.

In the upcoming part we will be focusing on Write – Ahead Logging, Steal or No-Steal, Force or No-Force