Database Checkpoints in SQL Server

In this article, we will talk about SQL Server Checkpoints.

To enhance performance, SQL Server applies modifications to database pages in memory. Often, this memory is called the buffer cache or buffer pool. SQL Server does not flush these pages to disk after every change. Instead, the database engine does checkpoint operation on each database from time to time. The  CHECKPOINT operation writes the dirty pages (current in-memory modified pages) and also writes details about the transaction log. (more…)

Dive Into SQL Server Transaction Log — Part 3

This is the third article in a series of articles about SQL Server transaction log. In this article, we will have a look at log record details.

Log Records

Log records are the core of the logging and recovery mechanisms. A log record describes a single change in a database. Thus, every change to a database has a log record or log records that help to describe that particular change.  Although you do not need to understand log record details, to understand what is going on with logging and recovery, those details are extremely interesting. (more…)

Dive Into SQL Server Transaction Log — Part 1

Chances are you already know that every SQL Server database contains one or more transaction log files, in addition to data files, that records all the transactions and database modifications made by each transaction. The transaction log is an essential part of any database, and the database administrator must keep it under observation. One of the most important tasks for the database administrator is to truncate the transaction log file to keep it from overflow. Why? Because the transaction log plays the most important role when it comes to the database recovery. (more…)

Retrieving SQL Server Metadata with Help of T-SQL

This article contains a list of scripts that mine the SQL Server metadata in the various system functions, stored procedures, tables, and catalog views. Metadata queries are really helpful in discovering information for a given database schema. You can copy all the T-SQL scripts that are listed in this article and use for your own purposes. However, please do some tests before apply to a production database.

(more…)