Professional SQL Server™ 2005 Programming

12.2. How the SQL Server Log Works

You definitely must have the concept of transactions down before you get into trying to figure out the way that SQL Server tracks what's what in your database. You see, what you think of as your database is only rarely a complete version of all the data. Except for rare moments when it happens that everything has been written to disk, the data in your database is made up of not only the data in the physical database file(s) but also any transactions that have been committed to the log since the last checkpoint.

In the normal operation of your database, most activities that you perform are "logged" to the transaction log rather than written directly to the database. A checkpoint is a periodic operation that forces all dirty pages for the database currently in use to be written to disk. Dirty pages are log or data pages that have been modified after they were read into the cache, but the modifications have not yet been written to disk. Without a checkpoint the log would fill up and/or use all the available disk space. The process works something like the diagram in Figure 12-1.

Figure 12.1. Figure 12-1

Don't mistake all this as meaning that you have to do something special to get your data out of the cache. SQL Server handles all of this for you. This information is only provided here to facilitate your understanding of how the log works, and, ...