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 […]

What is the most worrying cause of log growth (log_reuse_wait_desc)?

Two weeks ago I kicked off a survey that presented a scenario and asked you to vote for the log_reuse_wait_desc value you’d be most worried to see on a critical database with a 24×7 workload. Here are the results: Another very interesting spread of responses – as always, thanks to everyone who took the time to […]

More on using Transaction SID from the transaction log

Back in 2012 I blogged about using fn_dblog and fn_dump_dblog to figure out the point at which something occurred that you’d like to restore to just before (e.g. a table drop). I also mentioned that you can use the SUSER_SNAME () function on on the [Transaction SID] column for the LOP_BEGIN_XACT log record of the […]

Are I/O latencies killing your performance?

In this post I explain some methods for investigating and reducing high tempdb and transaction log I/O latencies that can severely hamper the performance of your workload. Back at the end of August I kicked off a survey asking you to run some code to calculate average I/O latencies and send me the results. I […]

How to tell who changed a log file characteristic?

My good friend Orson Weston (@weston12) posted a question to #sqlhelp on Twitter earlier: Is there a way out of the box to find when and who changed the max file size for a log file? You can’t tell this from the default trace as that just logs that the database was altered, not the log […]

The Accidental DBA (Day 30 of 30): Troubleshooting: Transaction Log Growth

This month the SQLskills team is presenting a series of blog posts aimed at helping Accidental/Junior DBAs ‘keep the SQL Server lights on’. It’s a little taster to let you know what we cover in our Immersion Event for The Accidental/Junior DBA, which we present several times each year. If you know someone who would benefit from […]

Tracking page splits using the transaction log

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 […]

New 7.5 hour online course on logging, recovery, and the transaction log

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

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

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)

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

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

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

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

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

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

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

(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

(Check out my online training courses: SQL Server: Detecting and Correcting Database Corruption and SQL Server: Advanced Corruption Recovery Techniques. We can also help you with disaster recovery.) 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 […]

Bug: log file growth broken for multiples of 4GB

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

(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

(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

(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?

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

(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

(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

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)

(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

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

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

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?

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?

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

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

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

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

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?

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)

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?

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?

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

  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

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

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

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?

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

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

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

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

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

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

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

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?

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?

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?

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?

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)

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

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

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

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

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

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

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

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

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

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

(Check out my online training courses: SQL Server: Detecting and Correcting Database Corruption and SQL Server: Advanced Corruption Recovery Techniques. We can also help you with disaster recovery.) 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) to a database – rebuilding […]

Search Engine Q&A #4: Using EMERGENCY mode to access a RECOVERY PENDING or SUSPECT database

(Check out my online training courses: SQL Server: Detecting and Correcting Database Corruption and SQL Server: Advanced Corruption Recovery Techniques. We can also help you with disaster recovery.) 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 […]

Search Engine Q&A #1: Running out of transaction log space

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…

(Check out my online training course: SQL Server: Detecting and Correcting Database Corruption.) In this post I want to describe the two worst things I think you can do to your database – rebuilding a transaction log and running REPAIR_ALLOW_DATA_LOSS – that people often try doing instead of restoring from their backups. Rebuilding a transaction […]