Choosing a database recovery model

The recovery model of a database is a critical setting that determines how the transaction log both records and retains information about transactions that occur in the database. It's important to remember that the setting always applies to the entire database.

As we'll explore in this post, there are 3 recovery models in SQL Server to choose from: FULL, BULK_LOGGED, and SIMPLE; each model has advantages, disadvantages, and tradeoffs. Only you can decide which model is appropriate for your particular situation and goals; we'll go through some of those tradeoffs and a few typical scenarios.

First of all, let's take a quick step back and ask why we care about what gets recorded in the transaction log.


What is recorded in the transaction log?

The transaction log contains an ordered sequence of physical operations that occur within a database. Each physical operation is written to the transaction log before the change itself is applied to the data page(s). This is called write-ahead logging, and it occurs regardless of the recovery model chosen.

Usually, the physical operations written to the transaction log also contain enough information to perform the inverse operation of the change (i.e., to undo a delete, do an insert). This combined with the write-ahead logging mechanism ensures transactions succeed or fail as a whole (atomicity) by giving SQL Server the ability to both undo and redo transactions. Hopefully those two bolded words set off hints about how logging is intimately tied to backup, restore, and several other features in SQL Server -- we'll get to those in a minute.


Minimal Logging

There are a handful of operations we can do in a database that can be minimally logged -- this means that only enough information required to undo the transaction is logged. In the case of a BULK INSERT, for example, all that's logged are page allocations -- we can undo those by deallocating the pages, but we can't redo them because the actual contents of the pages aren't recorded in the log.

We gain speed by writing to the transaction log less, but lose the ability to perform point-in-time recovery during the operation.

You might be wondering how a database restored using only transaction log backups can be complete after a point in time where an operation such as a BULK INSERT took place. How does SQL Server know what to put on those data pages, since only the allocations were logged?

The answer is in what is recorded by the first transaction log backup taken after the minimally-logged operation. SQL Server keeps track of extents that are modified by minimally-logged operations in a special metadata page (the ML map, or minimally-logged map, which contains 1 bit per extent), and copies those extents into the transaction log backup, along with the required portion of the transaction log itself. Yes, transaction log backups can contain data!

The net result of minimal logging isn't that we get the operation completely for free; we just defer a good chunk of the work to the backup subsystem for an immediate gain in speed.


Armed with this knowledge, let's take a look at the features of each of the three recovery models.



  • Full logging, all the time.
  • Supports point-in-time recovery (you must be taking transaction log backups).
  • Only transaction log backups internally clear the transaction log, so a good backup strategy is a must.
  • Required for certain features, such as Database Mirroring and Availability Replicas.
  • Typically used for OLTP databases, and where data protection is a high priority.



  • Full logging, except when the required conditions are met for minimally-logged operations.
  • Supports point-in-time recovery (again, you have to take transaction log backups) for all transaction log backups that do not contain minimally-logged operations. This means you should take a transaction log backup immediately before, and immediately after the minimally-logged operation(s), to maximize your ability to do a point-in-time restore.
  • Only transaction log backups internally clear the transaction log (same as FULL).
  • Used for performing bulk operations on a database that would otherwise prefer to be in FULL recovery. A database can be switched freely between FULL and BULK_LOGGED without breaking the transaction log backup chain.



  • Full logging, except when the required conditions are met for minimally-logged operations.
  • Does not support point-in-time recovery, and the transaction log cannot be backed up.
  • A database CHECKPOINT internally clears the transaction log as part of the process (since you can't take transaction log backups), which means you don't necessarily have to manage the transaction log as much as the other recovery models.
  • Usually used for databases that frequently handle large quantities of data (i.e., data warehouses), or databases where point-in-time recovery is not important.
  • Switching a database from FULL or BULK_LOGGED to SIMPLE breaks the transaction log backup chain. The transaction log backup chain can be restarted by switching from SIMPLE back to FULL or BULK_LOGGED, and taking a full or differential backup.


A good place to start choosing a recovery model would be by answering the following questions. Remember that these are only guidelines, and your specific situation will determine the best course of action.

  • Does the database require point-in-time recovery? (Yes = FULL or BULK_LOGGED, No = SIMPLE)
  • Are special features going be used on this database? (Yes = FULL or BULK_LOGGED -- usually FULL -- depending on the feature)
  • Will large quantities of data be imported into, or manipulated within this database? (Yes = BULK_LOGGED or SIMPLE)

Where does SQL Server store my data?

In this post, I'm going to give a high-level overview of where SQL Server stores user data. One of the most important topics, backup and restore, is closely related to how this (and logging) works, so it's important to have a solid understanding of these concepts to be able to plan and execute a successful backup and restore strategy. I'll cover logging and the transaction log in a future post.

The structures in which SQL Server stores data are arranged hierarchically. We're going to start with the smallest relevant units and work our way up.


Pages and Extents

In the SQL Server database engine, a page is an 8 KB region of storage (applies to both memory and disk). Each page has a header region that stores metadata about the page (the page's internal id, the type of the page, etc.), and a data region that contains the actual data.

While there are many different types of pages, there are only two basic classes:

  • Data Pages - Contains information related to data rows themselves.
  • Metadata Pages - Contains state information about other pages or extents.

Pages are grouped into sets of 8 (for a total of 64 KB), called extents, which makes the allocation system more efficient. Most extents only contain a single type of page1.



Logically groups extents into objects. A normal table is stored in a single partition, while a partitioned table is stored in multiple partitions. This is how ALTER TABLE ... SWITCH is able to efficiently move partitions to/from an independent table, or between partitioned tables -- a partition is a table.


Data Files

A data file is the basic unit of a database in the file system2, and it contains some or all of the extents that make up a partition (see next section on filegroups).

All databases have a primary data file, which stores structural (metadata) information about the database, and by convention has the file extension .mdf. The primary data file cannot be removed from the database.

Secondary data files can be added, which have the extension .ndf by convention.



A filegroup is a named logical construct that contains data files. (Note: log files do not belong to filegroups.) All databases have a PRIMARY filegroup that contains (at least) the primary data file. The PRIMARY filegroup cannot be removed. Filegroups (except PRIMARY) can be set as read-only.

A filegroup allows for the distribution of extent and page allocations between multiple data files. SQL Server uses two algorithms simultaneously when making allocations:

  1. Round-robin. Allocations are made sequentially from the data files within the filegroup (i.e., for a filegroup with 3 files, the allocations would come from File1, File2, File3, File1, File2, File3, etc.).
  2. Proportional fill. The number of allocations made from a file is proportional to the amount of free space in that file. The purpose of this is to eventually reach a steady-state where allocations are balanced between all the files. However, you can get into trouble by adding a new, empty, data file, which can result in almost all the allocations coming from that one file, probably defeating the purpose of the allocation distribution among the files in the first place.

Filegroups also serve as the target for object storage. For example, the table [dbo].[Sales] can be stored in a filegroup called SALES.



In this context, a database is a logical container that contains one or more filegroups (PRIMARY, plus zero or more others). One of the database's filegroups can be set as the default filegroup, where objects get created if the T-SQL statement does not explicitly specify a filegroup.


Now that you understand the basic internal storage structures SQL Server uses, you will be able to easily understand how many other areas of the system work, including backups, which is an important topic I will cover in a future post.


1 Mixed extents can contain multiple types of pages. These are a small minority of allocations.

2 Not getting into FILESTREAM or snapshot files here.


How are deadlocks created? (video)

Deadlocks occur when two processes both hold locks on resources, and also try to take locks on resources from the other process. This results in neither process being able to continue.

SQL Server resolves these situations automatically by terminating one of the processes, thus allowing the other(s) to proceed. This is done by an internal process called the Deadlock Monitor.

In this video demo, I demonstrate a basic sequence of events needed to create a deadlock. I also look at the locks that are held and requested by each process, and introduce you to reading a deadlock graph (which is what's captured from the Extended Events deadlock capturing system I presented last week).

(Sorry my cursor isn't visible in the video -- I blame the capture program I used. It won't happen again.)


If you want to reproduce the situation yourself, you can download the scripts here.


The Primary Key vs. The Clustered Index

When it comes to clustered vs. non-clustered indexes in SQL Server, there's still a lot of confusion with those types of indexes, and constraints, particularly the primary key. Frankly, that's entirely understandable, because it is a bit confusing.

Let me try to simplify.


First, there are two ways indexes can be created:

  1. Directly (i.e., CREATE ... INDEX).
  2. Certain types of constraints (UNIQUE, and PRIMARY KEY) create indexes to implement the constraint.

The behind-the-scenes constraint indexes appear in both Management Studio and sys.indexes, which make the waters a bit muddy, so to speak. This is also how there is spillover in terminology and statement syntax between indexes and constraints.

The key to understanding how this works is to separate the ideas of constraints and indexes.

In theory, the constraints I mentioned above could be implemented behind the scenes using another type of structure completely different than an index. It just so happens that an index is an available, reusable, efficient structure that is able to get the job done.


Second, there is a default type of index created by each type of constraint, and the default can be overridden:

  • A PRIMARY KEY constraint creates a clustered index by default.
  • A UNIQUE constraint creates a non-clustered index by default.

 Here are all the combinations, and the type of index that gets created:

/* Clustered index */
		PRIMARY KEY(WidgetId);

/* Clustered index */	

/* Non-clustered index */	

/* Non-clustered index */	
	ADD CONSTRAINT UC_MyTable_WidgetNumber

/* Non-clustered index */	
	ADD CONSTRAINT UC_MyTable_WidgetNumber

/* Clustered index */	
	ADD CONSTRAINT UC_MyTable_WidgetNumber


Finally, there is debate about whether a simple index, or a constraint should be created, because in a lot of cases, they can function identically with respect to user queries. There are several factors that come into play when making this decision:

  • Constraints have extra restrictions on them (i.e., columns in a primary key constraint cannot allow NULLs).
  • Creating a constraint instead of an index may serve to better document data/business rules in the database.
  • Constraints take a little more effort to drop, which reduces the likelihood of a mistake.
  • It may not be possible to represent a data/business rule using a constraint, while it is possible using a normal index. Constraint indexes can't use special features such as filters, for example.
  • Would creating an index with the same columns as a data/business rule better serve user queries? Constraint indexes don't allow included columns. If the table is large, creating and maintaining two separate indexes, one of which may be essentially redundant, can be expensive.

My personal philosophy is to first use constraints to maintain data/business rules, and then supplement with regular indexes as necessary. Constraints alone are frequently enough to satisfy user queries; however, there are definitely situations where it's clear that methodology should be broken. It's very situational, and really there's no better person qualified to evaluate the situation than you.



  • Indexes and constraints are separate objects.
  • Be aware of the default types of indexes created by the different types of constraints.
  • Create either constraints and/or indexes based on your individual needs.

Capture deadlocks 24/7 with Extended Events

Deadlocks can be notoriously difficult to reproduce in our development environments.

Most of the time:

  • There are multiple actual users involved with the problem.
  • The production workload, both in terms of volume and/or composition, can't be realistically reproduced in development either.
  • The tools we used to need to use to capture deadlocks (server-side trace, SQL Server Profiler) are prohibitively expensive to run all the time, or even for a short time, or we had to mess around with trace flags.

Here's where Extended Events come to the rescue, and really makes us able to be proactive to deal with deadlocks.

Extended Events allows us to capture deadlock graphs continuously, as they happen, 24 hours/day, 7 days/week, with very little performance overhead. Not only that, but the system we're going to set up is able to survive an instance restart! This is huge in terms of ease of management, and being able to have this information available when it's needed can be way more than half the battle.

Here is the meat of the script that sets up our event session for a SQL Server 2012 instance:

	ADD EVENT sqlserver.xml_deadlock_report 
	ADD TARGET package0.event_file
			FILENAME = N'\\CentralFileShare\MyTargetFile.xel',


Note that the database engine service account needs to be granted write access to the target location.

For ease of multi-server management, I recommend setting up a centralized file share to store all the output files. The full script was written with this in mind, and uses the server name as the file name automatically.

Security note: Deadlock graphs may contain sensitive information, such as data values in DML statements. The target location should be secured appropriately -- consider using subfolders in the target path to separate by server if a single folder isn't okay for your environment.


So, now that we've captured some deadlocks (yay?), how do we figure out what happened? Here is a first start. It reads the output and returns the event data (in this case, a deadlock graph) as XML. This script can be used for all types of Extended Event file targets, not just for deadlocks.

	CAST(event_data AS xml) AS EventXml
			'.xem', /* Required for 2008, optional for 2012 */

XPath can be used to further break things down, or the event data can be copied as text and saved as a .xdl file, which we can then open up in Management Studio to see the pretty deadlock graph. If you have a centralized management box, this script could be the basis of an Extended Events aggregation and reporting system. (Hopefully, you aren't dealing with that many deadlocks, but this type of system could be used for other purposes.)

In a future post, we'll look at how to analyze deadlock graphs.