Lazy log truncation or why VLFs might stay at status 2 after log clearing

 Earlier this year I was sent an interesting question about why the person was seeing lots of VLFs in the log with status = 2 (which means ‘active’) after clearing (also known as ‘truncating’) the log and log_reuse_wait_desc showed NOTHING. I did some digging around and all I could find was an old blog post from 2013 […]

New VLF status value

At least since I started working on the SQL Server team (just after 7.0 shipped) and since then there have only been two VLF status codes: 0 = the VLF is not active (i.e. it can be (re)activated and overwritten) (1 = not used and no-one seems to remember what it used to mean) 2 […]

What is the FCB_REPLICA_SYNC spinlock?

A question came up on the Data Platform MVP email list last night asking what the FCB_REPLICA_SYNC spinlock is. I answered the question and then promised to do a quick blog post, as there’s no information online about it that I could find. Explanation In a nutshell, this spinlock is used to synchronize access to […]

Read committed doesn’t guarantee much…

A while back I was involved in an email thread where people were wondering about some ‘weird’ behavior from SQL Server. The problem was occurring on SQL Server 2016 and used the default isolation level of read committed. The scenario was as follows: Create a table with a few columns Batch 1: In one SSMS window, […]

SQLskills SQL101: Why does my heap have a bunch of empty pages?

SQLskills has an ongoing initiative to blog about basic topics, which we’re calling SQL101. We’re all blogging about things that we often see done incorrectly, technologies used the wrong way, or where there are many misunderstandings that lead to serious problems. If you want to find all of our SQLskills SQL101 blog posts, check out Here’s a question I’m often asked (paraphrasing): […]

SQLskills SQL101: How can a SELECT cause a database to change?

As Kimberly blogged about recently, SQLskills is embarking on a new initiative to blog about basic topics, which we’re calling SQL101. We’ll all be blogging about things that we often see done incorrectly, technologies used the wrong way, or where there are many misunderstandings that lead to serious problems. If you want to find all of our SQLskills SQL101 […]

How are default column values stored?

An interesting question came up in class yesterday: how is a default column value stored, and what if some rows exist when a column is added and then the default value changes? An example scenario is this: Step 1: Create a table with two columns Step 2: Add 10 rows Step 3: Add a third column to the […]

Why PFS pages cannot be repaired

Last week there was a short discussion on Twitter about why PFS pages (damaged header, not individual PFS bytes) can’t be repaired (prompted by a #sqlhelp question about why they can’t be single-page restored, like other per-database allocation bitmaps). Just for the record, they can’t be fixed by automatic page repair in a mirror or […]


Last week I was sent an email question about the cause of LOGMGR_RESERVE_APPEND waits, and in Monday’s Insider newsletter I wrote a short explanation. It’s a very unusual wait to see as the highest wait on a server, and in fact it’s very unusual to see it at all. It happens when a thread is […]

Code to show rolled back transactions after a crash

In Monday’s Insider newsletter I discussed an email question I’d been sent about how to identify the transactions that had rolled back because of a crash, and I said I’d blog some code to do it. First of all you need to know the time of the crash. We can’t get this exactly (from SQL Server) unless SQL Server […]

Investigating the proportional fill algorithm

This is something that came up recently on the Microsoft Certified Master DL, and is something I discuss in our IEPTO1 class because of the performance implications of it, so I thought it would make an interesting post. Allocation Algorithms The SQL Server Storage Engine (SE) uses two algorithms when allocating extents from files in […]

When heap data pages become linked…

The pages at each level of an index are linked together in a doubly-linked list (using the m_nextPage and m_prevPage fields in their page headers) to allow ascending-order and descending-order scans, based on the index key(s). Data pages in a heap are NOT linked together, as there’s no ordering in a heap. However, there is […]

Code to analyze the transaction hierarchy in the log

Over the weekend there was a discussion on the MVP distribution list about the sys.dm_tran_database_transactions DMV and how one cannot use it to accurately determine how much log an operation has generated because it doesn’t provide a roll-up of the sub-transaction metrics to the outer transaction. This makes the output somewhat non-intuitive. The discussion prompted me to […]

Data recovery: investigating weird SELECT failures around corruption

An interesting corruption problem cropped up on the MCM distribution list yesterday and after I figured it out, I thought it would make a good blog post in case anyone hits a similar problem. In a nutshell, the problem was corruption such that a simple SELECT * query failed, but a SELECT * query with […]

How would indexes on AG readable secondaries work?

Last weekend there was a suggestion on the MVP distribution list about having temporary nonclustered indexes on AG readable secondaries, in the same way that you can have temporary statistics on them. I replied that in my opinion it would be extremely difficult to do that, and said I’d blog about why. Here’s my explanation. […]

Incomplete checkpoints and recovery

Back in 2009 I blogged about how checkpoints work (see How do checkpoints work and what gets logged) and I received a question in email on Monday that I thought would make a good little blog post. The question is (paraphrased): What happens if a checkpoint starts but doesn’t finish before a crash occurs? Will […]

Important change to VLF creation algorithm in SQL Server 2014

Since SQL server 2014 was released back in April last year, there have been some rumblings about changes to how many VLFs are created when the log is grown or auto-grown (I’ll just say auto-grown from now on, as that’s the most common scenario). I experimented a bit and thought I’d figured out the algorithm […]

When is fast recovery used?

It’s been a bit light on technical posts here over the last few months but now that summer’s over I’ll be ramping up again with lots of cool stuff planned. First up is a question that came up on the MCM distribution list this morning. There was a discussion of fast recovery (which I explained […]

Are mixed pages removed by an index rebuild?

This is a question that came up this morning during our IE1 class that I thought would make an interesting blog post as there are some twists to the answer. The first 8 pages that are allocated to an allocation unit are mixed pages from mixed extents, unless trace flag 1118 is enabled. See the following blog […]

Inside the Storage Engine: How are allocation unit IDs calculated?

It’s been a long time since I’ve written a post about pure internals, but every so often I get asked how an allocation unit ID is calculated from the m_objId and m_indexId fields that are stored in the header of every page. When DBCC PAGE dumps a page header’s contents, it does the necessary calculations […]

Parent transaction ID in 2012 fn_dblog output

All kinds of little bits of information have been added to the output of various DMVs, functions, and commands in SQL Server 2012. One which I only discovered recently, and about which I’m really excited (ok, I should probably get out more :-), is the inclusion of the parent transaction ID in the output of […]

New script: When were the sp_configure options last changed?

In my previous post I explained how the sp_configure settings are stored in a special page in the master database called the CONFIG block. Sometimes you might want to know when these were last changed if error logs are not available for some reason (as sp_configure changes are noted in the error log) or someone […]

Where are sp_configure settings stored? Another reason to backup master…

Earlier today I blogged on our SQL Server Pro magazine blog about false-alarm corruptions you will *definitely* see if you restore a backup of the master database as a user database and run DBCC CHECKDB on the restored copy. You might be doing this as part of offloading consistency checks to another server or validating […]

How are per-column modification counts tracked?

Earlier today there was a question on the MVP mailing list asking how SQL Server keeps track of per-column modification counts. From 2008 onwards, the hidden system table sys.sysrscols tracks modifications to table columns using the rcmodified column. The hidden system tables (introduced in 2005 when we rewrote the entire metadata management system) are only […]


This is a question that came up today on Twitter, and is actually something I’ve been meaning to blog about. One of the biggest space hogs in tempdb can be DBCC CHECKDB. It generates all kinds of information about what it’s seeing in the database (called facts) and stores them in a giant worktable. A […]

Initial VLF sequence numbers and default log file size

We're teaching on-site with a financial client in New York for two weeks and in a session this morning on log file architecture I was asked why the VLF (virtual log file) sequence numbers in a new database don't start at one. Here's an example: CREATE DATABASE foo; GO DBCC LOGINFO ('foo'); GO FileId  FileSize  […]

Do changes to index keys really do in-place updates?

There was an interesting discussion on Twitter this morning (started by my good friend Erin Stellato (blog|twitter)) about in-place updates for index records where the key value changes and the record remains on the same page. Various sources including the SQL Server 2008 Internals book (pg 361 – I didn't write or review that bit […]

A SQL Server DBA myth a day: (29/30) fixing heap fragmentation

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) Another quickie but goodie before the finale tomorrow! Myth #29: […]

A SQL Server DBA myth a day: (25/30) fill factor

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) I’m all mythed-out from yesterday, so today’s is a quick […]

A SQL Server DBA myth a day: (19/30) TRUNCATE TABLE is non-logged

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list.) Today's myth is very persistent, so it's high time it was debunked with a nice script to prove it too! […]

A SQL Server DBA myth a day: (6/30) three null bitmap myths

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) Today’s myth is a multi-parter especially for the folks in […]

Inside sys.dm_db_index_physical_stats

Way back in the mists of time, at the end of the last century, I wrote DBCC SHOWCONTIG for SQL Server 2000, to complement my new invention DBCC INDEXDEFRAG. I also used to wear shorts all the time, with luminous orange, yellow, or green socks. Many things change – I now have (some) dress sense, […]

Turning off the ghost cleanup task for a performance gain

I've blogged about ghost records and the ghost cleanup task a couple of time before (the only place it is really explained AFAIK), but one of my fellow MVPs was asking me some questions about it today for a customer of theirs and couldn't find the trace flag to turn it off. My previous blog […]

Lock logging and fast recovery

One of the things I love teaching is how the transaction log and logging/recovery work. I presented a session on this at both PASS and SQL Connections in the last two weeks, and in both sessions I promised to write some blog posts about the deep internals of logging operations. This is the first one […]

What does checkpoint do for tempdb?

Last week I posted in-depth about how checkpoints work and what exactly goes on (see How do checkpoints work and what gets logged). About a year ago I posted about why the buffer pool on a busy system may seem to have an inordinate amount of dirty tempdb pages in it, and now I want […]

How do checkpoints work and what gets logged

(Check out my Pluralsight online training course: SQL Server: Logging, Recovery, and the Transaction Log.) This is a blog post I’ve been meaning to do for a while, and I’ve recently noticed some info on the web about checkpoints which is a little misleading, so I want to do a quick post to explain how […]

Memory: Large Pages and MemToLeave

I'm constantly surprised by the number of people that don't follow the CSS SQL blog – some of the best SQL people in the industry post there. I just revisited a couple of really interesting posts by my good friend Bob Ward on memory management internals that I'd like to publicize. The first (from June […]

CHECKDB From Every Angle: How to tell if data purity checks will be run?

Here’s a question that came up recently: if I’ve upgraded a database from SQL 2000 or before, how can I tell if the data purity checks will be run or not? As you may know, DBCC CHECKDB in 2005 onwards includes ‘data purity’ checks. These look for column values where the value is outside the […]

Forwarding and forwarded records, and the back-pointer size

This is a question that comes up every so often, most recently this morning while teaching a private class (and Kimberly's teaching now): how large is the forwarded record back-pointer? (And I haven't posted anything geeky for a while…) In a heap it is possible to get forwarding and forwarded records. They occur when a […]

Investigating locking and deadlocking with %%lockres%%

I’ve just read a very good, very deep, and very interesting blog post by James Rowland-Jones. In the post, James investigates some locking issues using a variety of means and explains about the undocumented %%lockres%% function with you can use to figure out what the wait resource will be for individual table rows (basically the […]

How expensive are page splits in terms of transaction log?

Page splits are always thought of as expensive, but just how bad are they? In this post I want to create an example to show how much more transaction log is created when a page in an index has to split. I'm going to use the sys.dm_tran_database_transactions DMV to show how much more transaction log […]

Misconceptions around null bitmap size

I'm starting a new series called 'Misconceptions' – a series of short posts that debunk some of the many myths and misconceptions that exist about the way SQL Server behaves. I actually already did the first post a couple of weeks back (Misconceptions around TF 1118) but just went back to re-tag it. In this […]

Performance bug: NOLOCK scans involving off-row LOB data

Here's an interesting bug that was just recently fixed, and someone actually hit it today, so it's time to blog it. Bottom line is that if you're doing NOLOCK scans of tables involving LOB data, the perfmance might suck. 2005 SP3 CU3 has the bug fix, but unfortunately the KB article was written by someone […]

Misconceptions around TF 1118

[Edit 2016: This trace flag is required in all version of SQL Server up to and including SQL Server 2014. Every instance of SQL Server in the world should have this trace flag enabled. In SQL Server 2016, the behavior enabled by the trace flag is the default, so the trace flag is no longer […]

FILESTREAM garbage collection

In my previous posts on FILESTREAM I discussed the directory structure of the FILESTREAM data container and how to map the directories to database tables and columns. In this post I’m going to explain how and when the FILESTREAM garbage collection process works as that doesn’t seem to be documented anywhere (even in the FILESTREAM whitepaper […]

Inside the Storage Engine: More on the circular nature of the log

  Every so often (well, ok, very frequently) my internal list of ‘I must blog about that’ gets too big and I have an almost uncontrollable urge. Luckily, these urges end up with me blogging several times, otherwise who knows what kind of trouble I’d get into? :-) First up today is some pretty deep internals […]

Ghost cleanup redux

Way back at the start of me blogging here I wrote a comprehensive description of ghost records and the ghost cleanup process – see Inside the Storage Engine: Ghost cleanup in depth. A question came up in the class I’m teaching this week that’s worth answering in a blog post – do ghost records occur […]

FILESTREAM directory structure

After writing the FILESTREAM whitepaper for Microsoft, I’ve had lots of questions about the structure of the FILESTREAM data container. The FILESTREAM data container is the technical term for the NTFS directory structure where all the FILESTREAM data is stored. When you want to use FILESTREAM data, you first add a filegroup (during or after […]

Misconceptions around instant file initialization

Whenever I teach a class I’m amazed at the number of DBAs who don’t know about instant file initialization. Without wanting to redo blog posts that others have done, in a nutshell it allows a file to be create or grown ‘instantly’ by not having to zero the space in the file. This is especially […]

Search Engine Q&A #27: How does the storage engine find variable-length columns?

This question came up in a class I was teaching a few weeks ago: given that the variable-length column offset array in a record only stores offsets, how does the storage engine find a particular variable-length column? The question comes because there's nothing in the record to say which variable-length column is which – so […]

TechNet Magazine: feature article on understanding logging and recovery

Wow – today is all about new content. As if I haven't already blogged about enough stuff to keep you reading through next week, the February issue of TechNet Magazine is now available and contains a feature article I wrote about understanding how logging and recovery work inside SQL Server. The article covers: What is […]

Comprehensive tempdb blog post series

Over the last few weeks Sunil Agarwal (from the SQL Storage Engine team) has posted a great series of blog articles about tempdb and the version store, over on my old stomping ground – the Storage Engine blog. The articles are well worth reading – the links are: Managing tempdb part 1 and part 2 Version […]

Corruption bug that people are hitting: Msg 8914 – PFS free space

(Posted with permission of the dev team) Here's an interesting bug that people are hitting. I found out about this while here in Barcelona at TechEd when I got roped into a discussion with a couple of Microsoft colleagues. Their customer was seeing errors like the following: Msg 8914, Level 16, State 1, Line 1 […]

SQL Server 2008 Internals available to pre-order on

  Very exciting! The SQL Server 2008 Internals book we're working on with Kalen is now available to pre-order on Amazon, ahead of the planned publication date of February 18th next year. I'm neck-deep in the Consistency Checking Internals chapter right now – its shaping up to be about 100 pages on its own! There's […]

How the on-disk data structures fit together

In the last few classes I've taught, we've discussed the on-disk structures where everything is stored in a database, and I've also been asked privately where all the info is on my blog. I know that a lot of people reading my blog now are new in the last six months or so and will […]

Why does the buffer pool contain so many dirty tempdb pages?

(Quickie post #2 while it's Kimberly's turn to lecture this morning…) Greg asked a question regarding the script I posted to examine buffer pool contents (paraphrased) – why does the buffer pool seem to contain such a high proportion of dirty tempdb pages on busy production systems? The answer is to do with the recoverability of […]

Inside the Storage Engine: What’s in the buffer pool?

This is a quick post inspired by a question I was sent in email (thanks Marcos!) which very neatly lets me show a DMV I’ve been meaning to blog about for a while. And the weather here in Redmond really sucks right now so I can’t go outside – blogging will serve as my work-avoidance […]

Search Engine Q&A #21: File header pages, and file header corruption

Following on from my previous post on boot pages and boot page corruption, I’ve been asked about file header pages – and I was already planning this post as the next in the series. So what’s a file header page? Every data file in a database has the very first 8kb page (i.e. page 0 […]

Search Engine Q&A #20: Boot pages, and boot page corruption

  Now that I’ve done all the business-related blog posts, back to the good stuff to stop people complaining! Something that’s cropped up a few times over the summer so far is people trying to repair boot page corruptions. First off, what’s a boot page? Every database has a single page that stores critical information […]

Inside The Storage Engine: Does turning on page checksums discard any torn-page protection?

This is a really interesting question that came up in the Microsoft Certified Architect class I’m teaching at present – if a database has torn-page protection enabled, and page checksums are enabled, is all the existing torn-page detection lost? This is an important question, because enabling page checksums doesn’t suddenly make all allocated pages be […]

SQL Server 2008: New (undocumented) physical row locator function

One of the drawbacks of not being in the SQL team at Microsoft any longer is that I don’t know about all the undocumented features in the next release – I have to hunt around for them like everyone else :-( So I was poking about in SSMS in 2008 CTP-6 and noticed a function called sys.fn_PhysLocCracker […]

Inside The Storage Engine: GAM, SGAM, PFS and other allocation maps

Well this one is well overdue and I'm in the middle of writing a class where I want to reference this blog post – so I suppose I'd better write it!! This is an updated post from my old Storage Engine blog that now covers DIFF and ML map pages. In some previous posts in […]

Inside The Storage Engine: sp_AllocationMetadata

While I was at Microsoft, I wrote some code in the Storage Engine to very easily return all the IAM chains/allocation units (see this post for more details of the internals of those), what type they are, and the relevant page IDs (first, root, first-IAM) so I could go spelunking with DBCC PAGE. Since I […]

More on how much transaction log a full backup includes

  In a previous post (see here) I debunked a myth about how much transaction log a full backup would include. I had a question in the blog post comments that asked (paraphrasing): The full backup has to include all the transaction log from the begin LSN of the oldest active transaction at the time […]

Inside the Storage Engine: When do versioning tags get added?

Ok – so we did more partying than we thought so blog posts have been a little sparse this month, but here’s one to end off the year. There’s a popular impression that turning on snapshot isolation and then rebuilding indexes will cause all rows in the table to get the extra 14-byte versioning tags. […]

Search Engine Q&A #10: When are pages from a truncated table reused?

This is a question I was sent a week or so ago – if a table is truncated inside a transaction, what protects the integrity of the table’s pages in case the transaction rolls back? Let’s find out. First off I’ll create a simple table to experiment with: We can see what pages and extents […]

Inside the Storage Engine: Ghost cleanup in depth

Over the years I was in the Storage Engine team I saw a lot of concern on the various forums about the ghost cleanup task. There have been a few bugs with it in previous versions  (see these KB articles – 932115 and 815594) and there's very little info available on it. For some reason […]

Inside the Storage Engine: Proof that records are not always physically stored in index key order

I mentioned this in my Anatomy of a page post – its a common misconception that records in an index are ALWAYS stored in the same physical order as the logical order defined by the index key. Here’s proof for you that this is incorrect (as well as introducing you to the other dump styles […]

Inside the Storage Engine: IAM pages, IAM chains, and allocation units

This is a combo from some previously posted material, with some more DBCC PAGE output thrown in. IAM pages An IAM (Index Allocation Map) page tracks approximately 4GB worth of space in a single file, aligned on a 4GB boundary. These 4GB chunks are called ‘GAM intervals’. An IAM page tracks which extents within that […]

Inside the Storage Engine: Anatomy of an extent

This one’s a quickie. In the previous post I explained about database pages – their structure and some page types. Now I’d like to explain how pages are grouped into units called extents. An extent is a group of eight physically consecutive pages in a data file. Extents are always aligned on 64KB boundaries (i.e. […]

Inside the Storage Engine: Anatomy of a page

Next up in the Inside the Storage Engine series is a discussion of page structure. Pages exist to store records. A database page is an 8192-byte (8KB) chunk of a database data file. They are aligned on 8KB boundaries within the data files, starting at byte-offset 0 in the file. Here’s a picture of the […]

Inside the Storage Engine: Using DBCC PAGE and DBCC IND to find out if page splits ever roll back

Time for the first post in the Inside the Storage Engine series. I’m going to focus on SQL Server 2005 in this series and I’ll point out major differences between 2005 and previous versions. Please drop me a line if there’s something you’d like to see explained and demo’d. Before jumping into how things work, […]

Inside the Storage Engine: Anatomy of a record

This week I’m going to post a bunch of info on the basic structures used to store data and track allocations in SQL Server. A bunch of this was posted back when I started blogging at TechEd 2006 but I want to consolidate/clarify info and add more about using DBCC PAGE to examine the various […]