Whenever I’m teaching about index fragmentation I get asked how to track page splits proactively. This can be useful to discover fragmentation occurring in indexes you didn’t know had fragmentation problems, without running the sys.dm_db_index_physical_stats DMV (see here for how that works) against all the indexes in your databases. Today this came up multiple times, both in class and in [...]
Paul S. Randal
In Recovery...
Tracking page splits using the transaction log
Posted on: February 7, 2013 11:43 am
New 7.5 hour online course on logging, recovery, and the transaction log
Posted on: November 16, 2012 3:33 pm
As you know we're recording a lot of content for Pluralsight, and they've just published my latest course today: SQL Server: Logging, Recovery, and the Transaction Log. This is a carefully structured, 7.5 hour brain dump of everything I know about logging and recovery, which will be useful whether you're a beginner, a seasoned DBA, or [...]
Parent transaction ID in 2012 fn_dblog output
Posted on: October 4, 2012 9:15 pm
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 [...]
Multiple log files and why they’re bad
Posted on: September 24, 2012 3:48 pm
About a month ago I kicked off a survey with some code to run to figure out how many log files your databases have (see here). There are all kinds of misconceptions about transaction logs and how to configure them (and how they work) and I'm amazed at the misinformation I continue to see published. For [...]
Survey: transaction log files per database (code to run)
Posted on: August 29, 2012 12:11 pm
In this survey I'd like to see what the distribution of the number of log files per database is for your servers. I'm planning to do similar surveys through the rest of the year around data files and filegroups too. Please run the following code (with example output): SELECT DISTINCT [LogFiles], COUNT (*) [...]
Using fn_dblog, fn_dump_dblog, and restoring with STOPBEFOREMARK to an LSN
Posted on: May 17, 2012 3:05 am
I’ve blogged a bunch about using the undocumented fn_dblog function I helped write (and I’ve got a lot more to come :-) but here’s one I haven’t mentioned on my blog before: fn_dump_dblog (although I have talked about it at conferences last year). Here’s a scenario: someone dropped a table and you want to find [...]
Understanding data vs log usage for spills in tempdb
Posted on: May 11, 2012 2:08 am
Earlier this morning I noticed a discussion on the SQL MCM distribution list (that all the original MCM instructors are part of) that was trying to make sense of a huge disparity between tempdb data file usage and log file usage. I explained the answer and thought I'd share it with you all too. The [...]
TechNet Magazine: September 2011 SQL Q&A column
Posted on: September 14, 2011 5:43 am
The September edition of TechNet Magazine is available on the web now and has the latest installment of my regular SQL Q&A column. This month's topics are: Online index operations logging changes in SQL Server 2008 Is that transaction contained in my full backup? ALTER INDEX … REBUILD vs. ALTER INDEX … REGORGANIZE Avoiding regular [...]
Initial VLF sequence numbers and default log file size
Posted on: September 13, 2011 11:52 am
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 [...]
TechNet Magazine: July 2011 SQL Q&A column
Posted on: July 21, 2011 3:11 pm
The July edition of TechNet Magazine is available on the web now and has the latest installment of my regular SQL Q&A column. This month's topics are: Deferred log truncation from concurrent data and log backups Database mirroring monitoring Multiple transaction log files Best use of SSDs in a SQL environment (high-level) Check it out [...]
TechNet Magazine: January 2011 SQL Q&A column
Posted on: January 19, 2011 9:27 am
The January edition of TechNet Magazine is available on the web now and has the latest installment of my regular SQL Q&A column. This month's topics are: Diagnosing I/O subsystem bottlenecks Capacity planning for transaction logs Why there are no non-logged operations in user databases Check it out at http://technet.microsoft.com/en-us/magazine/gg552991.aspx.
Log file configuration metrics for 17000 databases
Posted on: July 4, 2010 5:20 am
(Happy Independence Day to everyone back home in the USA! We're in Dublin for another week…) Back at the end of April I conducted a survey of transaction log characteristics (see here) and now I present the results to you. I had a great response to the survey from almost 100 people, and the total [...]
Disaster recovery 101: backing up the tail of the log
Posted on: June 18, 2010 9:21 am
One of the first things you should always check when a database has been damaged and you're going to perform a restore operation is whether you need to back up the tail of the log. The tail of the log contains the transaction log that's been generated since the most recent log backup was taken. If [...]
Bug: log file growth broken for multiples of 4GB
Posted on: May 15, 2010 7:23 pm
I was teaching an internals/maintenance/performance class for Microsoft DBAs this week on the Redmond campus and one of the students (thanks Crystal!) showed me a really strange bug that I'd never seen before. One of the things that Kimberly and I both recommend is not having transaction log VLFs be too large, with 500MB being [...]
A SQL Server DBA myth a day: (30/30) backup myths
Posted on: April 30, 2010 9:19 am
(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.) The month is finally over so time for the grand [...]
A SQL Server DBA myth a day: (28/30) BULK_LOGGED recovery model
Posted on: April 28, 2010 2:28 am
(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.) The BULK_LOGGED recovery model continues to confuse people… Myth #28: [...]
A SQL Server DBA myth a day: (26/30) nested transactions are real
Posted on: April 26, 2010 11:03 am
(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.) Nested transactions are an evil invention designed to allow developers [...]
Survey: how are your transaction logs configured?
Posted on: April 23, 2010 1:54 pm
Over the last few months there's been some noise (mostly of my making) on Twitter about the number of VLFs in transsction logs. Given the large numbers of people who read the blog and follow me on Twitter, I thought it would be very interesting to collect some statistics from a few hundred of you [...]
A SQL Server DBA myth a day: (19/30) TRUNCATE TABLE is non-logged
Posted on: April 19, 2010 11:57 am
(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: (14/30) clearing the log zeroes out log records
Posted on: April 14, 2010 6:07 am
(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 is the big day – 8am PST I'm presenting [...]
Script: open transactions with text and plans
Posted on: February 1, 2010 4:37 pm
Here’s a little script I knocked up this afternoon to tell me who has open transactions on the server – not just the single oldest active transaction that DBCC OPENTRAN returns. It gives back: session ID login name database context transaction begin time how many log records have been generated by the transaction how much [...]
Benchmarking: 1-TB table population (part 2: optimizing log block IO size and how log IO works)
Posted on: January 27, 2010 11:23 am
(For the hardware setup I'm using, see this post. For the baseline performance measurements for this benchmark, see this post.) In my previous post in the series, I described the benchmark I'm optimizing – populating a 1-TB clustered index as fast as possible using default values. I proved to you that I had an IO bottleneck [...]
Interesting case of watching log file growth during a perf test
Posted on: January 18, 2010 11:56 am
I'm running some performance tests on the hardware we have (more details on the first of these tomorrow) and I was surprised to see some explosive transaction log growth while running in the SIMPLE recovery model with single row insert transactions! Without spoiling tomorrow's thunder too much, I've got a setup with varying numbers of [...]
What can cause log reads and other transaction log questions
Posted on: December 9, 2009 4:14 pm
Earlier today there was a question on SQL Server Central where someone wanted to know what could be causing so many reads on their transaction log. I was asked to chime in by fellow MVP Jonathan Kehayias (who also sent me some questions that I've answered in this post – thanks Jon!), so I did, with [...]
Lock logging and fast recovery
Posted on: November 13, 2009 1:24 pm
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 [...]
New script: is that database REALLY in the FULL recovery mode?
Posted on: October 7, 2009 1:27 pm
One of the perennial problems facing both experienced and involuntary DBAs is how to tell whether a database is really in the Full recovery model or not? This is complicated by the fact that when you switch a database into the Full recovery mode, it actually behaves as if it’s in the Simple recovery mode [...]
What does checkpoint do for tempdb?
Posted on: October 7, 2009 11:49 am
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
Posted on: September 27, 2009 9:28 pm
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 checkpoints work as far as log records are concerned. When a checkpoint operation occurs, no [...]
TechNet Magazine: August 2009 SQL Q&A column
Posted on: July 17, 2009 1:57 pm
The August edition of TechNet Magazine is available on the web now and has the latest installment of my regular SQL Q&A column. This month's topics are: Proactive page checksum failure detection Why GUIDs make for bad clustered index keys Possible problems using a log shipping secondary for reporting Recovery model choice for log size [...]
Finding out who dropped a table using the transaction log
Posted on: June 16, 2009 6:27 pm
I came across a question on ServerFault this afternoon that inflamed my desire to be ultra-geeky (it was really already inflamed after teaching backup and restore internals all afternoon). Basically the question boiled down to how to find out who dropped a table if there’s no other way except the transaction log (e.g. no tracing [...]
Misconceptions around the log and log backups: how to convince yourself
Posted on: May 29, 2009 11:09 am
There’s still a widely held misconception that when properly in the FULL or BULK_LOGGED recovery models that full or differential backups can truncate the log. No. It *NEVER* happens. This is one of the reasons why I’m doing a whole spotlight session on this at PASS this year – the transaction log and its behavior [...]
New script: how much data will the next log backup include?
Posted on: May 24, 2009 2:32 pm
About a year ago, I blogged a cool script that would work out how much of a database has changed since the last full backup – i.e. how big will the next differential backup be. You can find that script at New script: How much of the database has changed since the last full backup?. [...]
A sad tale of mis-steps and corruption (from today)
Posted on: May 12, 2009 8:36 pm
This is a true story, and unfolded over the last few days. It's deliberately written this way, I'm not trying to be patronizing – just illustrating the mistakes people can make if they don't know what not to do. Once upon a time (well, a few days ago), there was a security person who had access [...]
How expensive are page splits in terms of transaction log?
Posted on: May 12, 2009 5:50 pm
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 [...]
Why could restoring a log-shipping log backup be slow?
Posted on: April 21, 2009 5:48 pm
A short post to start the day (in India) and then at lunchtime I'll do how it works: FILESTREAM garbage collection. This is a question that came up recently on an MVP discussion list: why does a 200MB log backup take 40 minutes to restore on my log-shipping secondary? First answer I thought of was [...]
Inside the Storage Engine: More on the circular nature of the log
Posted on: April 21, 2009 12:42 am
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 [...]
Importance of proper transaction log size management
Posted on: April 10, 2009 12:21 pm
Last week's survey was on how *you* manage the size of your transaction log (see here for the survey). Here are the results as of 4/10/2009 – the most popular survey yet: In my opinion (guarantee that I'm going to say stuff that some of you won't agree with!), there are only two valid answers in [...]
Weekly survey: transaction log size management
Posted on: April 3, 2009 9:59 am
In this week's survey, I'm interested in how *you* manage the size of your transaction log. I'll report on the results around 4/10/2009. Thanks! (No comments please… wait till the results post next week)
Ghost cleanup redux
Posted on: March 19, 2009 10:44 am
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 [...]
How can a log backup fail but a full backup succeed?
Posted on: March 7, 2009 8:57 am
This is an interesting case that cropped up yesterday – the transaction log is damaged so a log backup doesn't work (with the error below): Backup detected log corruption in database FakeDBName. Context is FirstSector. LogFile: 2 'F:\SQLLOGS\XYZ\FakeDBName_Log.ldf' VLF SeqNo: x502e VLFBase: x2ce40000 LogBlockOffset: x2d0a9000 SectorStatus: 2 LogBlock.StartLsn.SeqNo: x4ee3 LogBlock.StartLsn.Blk: x1348 Size: x200 PrevSize: x400 [...]
Performance impact of lots of VLFs in the transaction log
Posted on: February 9, 2009 6:18 pm
One of the things I mentioned in my recent TechNet Magazine article on Understanding Logging and Recovery was the need to manage the transaction log so the number of VLFs (virtual log files) does not get too large (with too large being more than, say, 100 VLFs). Linchi Shea (a fellow MVP) has just posted [...]
TechNet Magazine: February 2009 SQL Q&A column
Posted on: January 16, 2009 10:15 am
OK – last content post today. I forgot that the February TechNet Magazine also has the latest edition of my regular SQL Q&A column. This month's column covers: Should backup compression be enabled at the instance level? Client redirection during database mirroring failovers Partition-level lock escalation in SQL Server 2008 Is it ever safe to [...]
TechNet Magazine: feature article on understanding logging and recovery
Posted on: January 16, 2009 10:01 am
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 [...]
Conference Questions Pot-Pourri #10: Shrinking the database before taking a backup
Posted on: November 26, 2008 5:43 am
Here's a question I got from someone who attended our database maintenance workshop at PASS last week (paraphrased): I attended your pre-conference session on database maintenance and found it to be very informative. From what you told use though, I think I need to change my nightly backup procedure. I like to get my databases [...]
Conference Questions Pot-Pourri #9: Q&A around compression features
Posted on: November 11, 2008 9:29 am
Today's post is based on a bunch of questions I've had around the various compression features in SQL Server 2008. Does turning on data compression or backup compression compress the transaction log files on disk? No. The transaction log is not compressed in any way under any circumstances. Rows from tables and indexes that have [...]
New minimally-logged insert functionality in SQL Server 2008
Posted on: October 28, 2008 1:26 am
During early SQL Server 2008 development, the Storage Engine team introduced additional minimally-logged functionality. And then it was removed again for RTM. Now it seems that they've bowed under customer pressure and have made the functionality available again (see Sunil's announcement here). Using trace flag 610 in the RTM build, you enable the potential for [...]
TechNet Magazine: October SQL Q&A column
Posted on: September 23, 2008 1:07 am
It’s been almost two weeks since my last post as we’ve been offline in Scotland (another photo post to follow – I owe you two now…) but now we’re back for the crazy Fall conference and teaching season. The latest installment of my regular Q&A column in TechNet Magazine is available at http://technet.microsoft.com/en-us/magazine/cc895648.aspx. This month [...]
Should Microsoft provide a transaction log reader tool?
Posted on: August 26, 2008 12:21 am
Now this one’s sure to spark some controversy… I was checking my RSS feeds of the SQL blogs that I follow and noticed an interesting post on Kalen’s blog (the post is here). She’d been lambasted for suggesting in the weekly SQL Server Magazine newsletter that *Microsoft* shouldn’t provide a log reader tool and asked [...]
Search Engine Q&A #25: Why isn’t my log backup the same size as my log?
Posted on: August 25, 2008 12:23 am
I woke up this morning and someone had replaced my wife with someone who likes to blog :-). Kimberly’s turned over a new leaf and is going to blog much more often – in fact she’s blogged 4 times today already. Check out her blog here. Continuing on the transaction log theme of the last few [...]
Search Engine Q&A #24: Why can’t the transaction log use instant initialization?
Posted on: August 20, 2008 7:47 pm
This is a quickie in response to a blog comment from my previous post on instant initialization: How to tell if you have instant initialization enabled? The comment was: I must say, I love instant initialization. It's awesome. But I always wondered why it's not available for the log file. I assume there's a technical reason… [...]
Search Engine Q&A #23: My transaction log is full – now what?
Posted on: August 14, 2008 3:05 pm
There's been a spate of problems on the forums and in my inbox with people's transaction logs filling up. With the increase in "involuntary DBAs", there are more people being thrust into the role of database administration without being given the chance to learn the skills, tips, and tricks necessary. One of the most common [...]
Search Engine Q&A #19: Misconceptions around index rebuilds (allocation, BULK_LOGGED mode, locking)
Posted on: June 8, 2008 5:12 pm
Over the last few weeks I've seen (and helped correct) quite a few myths and misconceptions about index rebuild operations. There's enough now to make it worthwhile doing a blog post (and it's too hot here in Orlando for us to go sit by the pool so we're both sitting here blogging)… Myth 1: index [...]
More on how much transaction log a full backup includes
Posted on: January 31, 2008 8:01 am
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 the [...]
Conference Questions Pot-Pourri #3: unexpected index rebuild results and bulk-logged operations
Posted on: November 6, 2007 11:17 pm
A couple more questions from the last couple of classes. Q1) Why doesn’t performing an index rebuild alter the fragmentation? A1) Here are the possibilities – all of which I’ve seen happen: There isn’t an index – either DBCC DBREINDEX or ALTER INDEX … REBUILD are being run on a table that only has a heap, [...]
Why won’t my log clear in SIMPLE recovery mode? SQL 2000 bug or very large VLFs
Posted on: November 5, 2007 2:59 pm
Quick post this morning before this gets knocked out of my head by the Disaster Recovery session we're doing today at SQL Connections. This came up yesterday for a few people both on 2000 and 2005 – the database is running in SIMPLE recovery mode but the log isn't getting cleared as it usually does [...]
Debunking a couple of myths around full database backups
Posted on: October 25, 2007 9:42 pm
I’ve been involved in a few conversations today that have highlighted some big misconceptions about how backups work. I’d like to use this blog post to debunk them. I checked everything I say here with my friend Steve Schmidt, the developer on the Storage Engine team responsible for BACKUP/RESTORE for the last ten years. Myth 1: [...]
More on Database Mirroring performance and index maintenance
Posted on: October 19, 2007 12:37 am
There’s been some discussion over on the SQL Server Central forums about database mirroring performance, and one question in particular on how to do index rebuilds on mirrored VLDBs. Remember that in database mirroring, the database has to be in the full recovery mode, so all index rebuilds are fully logged. It all comes down [...]
BACKUP LOG WITH NO_LOG – use, abuse, and undocumented trace flags to stop it
Posted on: October 12, 2007 5:09 pm
This has come up several times over the last few days, and is something that Kimberly and I discuss (and deplore) when we lecture – the use of BACKUP LOG WITH NO_LOG (or TRUNCATE_ONLY – they’re synonymous) to allow log truncation. How is it used? The common use is when the transaction log grows to [...]
SQL Server 2008: Performance boost for Database Mirroring
Posted on: October 11, 2007 12:06 am
As I mentioned in a previous post, one of the new features for database mirroring in SQL Server 2008 is log stream compression. There’s a good explanation of what this is (along with some example workloads and performance graphs) on the SQL Customer Advisory Team blog so I’m not going to duplicate all that here. Basically, the [...]
Inside the Storage Engine: Ghost cleanup in depth
Posted on: October 8, 2007 4:24 pm
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 [...]
Search Engine Q&A #6: Using fn_dblog to tell if a transaction is contained in a backup
Posted on: October 6, 2007 1:00 am
Here’s a really interesting question that was in my search engine logs yesterday - if I have a transaction that runs and completes while a backup is running, will the complete transaction be in the backup? The answer is…. it depends! In terms of what gets backed up, the way a full backup works is: Note [...]
EMERGENCY-mode repair: the very, very last resort
Posted on: October 2, 2007 11:26 pm
(Be sure to join our community to get our bi-weekly newsletter with exclusive content, demo videos, and other SQL Server goodies! Also check out our online training courses.) This is a follow-on from two posts: Corruption: Last resorts that people try first… where I discussed the two worst things you can do (in my opinion) [...]
Search Engine Q&A #4: Using EMERGENCY mode to access a RECOVERY PENDING or SUSPECT database
Posted on: October 2, 2007 5:20 pm
By far the most common search engine query leading to the blog is about fixing a suspect or unrecovered database. The very best way to do this is to use your backups, and to have a backup strategy that allows you to recover in the smallest amount of time and with no data loss. But [...]
Search Engine Q&A #1: Running out of transaction log space
Posted on: September 24, 2007 12:35 am
One of the great things about the blog engine we use is that it shows all the search engine queries that led to someone clicking through to the site. I’ve been looking through the logs to see what kind of problems people are having that end up here. In this occasional series, I’m going to [...]
Corruption: Last resorts that people try first…
Posted on: September 15, 2007 12:29 am
These next few posts are based off part of my Secrets of Fast Detection and Recovery from Database Corruptions session from TechEd and various user groups around the world (Edit: see here for a video recording from TechEd 2008). In this post I want to describe the two worst things I think you can do [...]
