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 […]
Paul S. Randal
In Recovery...
SQLskills SQL101: Switching recovery models
Posted on: March 29, 2017 12:44 am
Code to show rolled back transactions after a crash
Posted on: January 18, 2017 5:27 pm
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 […]
Getting a history of database snapshot creation
Posted on: October 20, 2016 5:24 pm
Earlier today someone asked on the #sqlhelp Twitter alias if there is a history of database snapshot creation anywhere, apart from scouring the error logs. There isn’t, unfortunately, but you can dig around the transaction log of the master database to find some information. When a database snapshot is created, a bunch of entries are […]
Code to analyze the transaction hierarchy in the log
Posted on: May 1, 2016 9:45 am
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 […]
T-SQL Tuesday #67 – monitoring log activity with Extended Events
Posted on: June 9, 2015 10:02 am
On the second Tuesday of each month, many people in the SQL Server community join together to all blog on the same topic – a cool idea from Adam Machanic many years ago. This month’s topic is Extended Events, hosted by Jes Borland (b | t) – you can see her original post here. Yesterday […]
Finding a transaction in the log for a particular user
Posted on: June 3, 2015 11:45 am
In the last IEHADR class we just had in Chicago, I was doing a demo of looking in the transaction log to find the point at which a table was dropped so a restore could be performed (as described in this blog post). One of the students asked how to find a transaction for a […]
REPLICATION preventing log reuse but no replication configured
Posted on: April 6, 2015 2:10 pm
Last week, for the second time in as many weeks, I was sent a question in email from someone who had a transaction log that was growing out of control. He’d already queried log_reuse_wait_desc for the database (see this post for some more background) and the result was REPLICATION. The problem was, there was no […]
Incomplete checkpoints and recovery
Posted on: March 18, 2015 12:13 pm
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
Posted on: January 6, 2015 3:27 pm
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 […]
Delayed Durability in SQL Server 2014
Posted on: November 20, 2014 11:13 am
One of the cool new features in SQL Server 2014 is delayed durability (available in all Editions), which is described in detail in Books Online here. I think I’m going to see a lot of people turn this on, as you can get a profound increase in transaction throughput with the right workload. However, I also […]
When is fast recovery used?
Posted on: September 15, 2014 1:40 pm
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)?
Posted on: March 17, 2014 7:58 pm
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
Posted on: February 11, 2014 6:54 am
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?
Posted on: November 14, 2013 9:02 am
(Check out our Pluralsight online training course: SQL Server: Improving Storage Subsystem 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 […]
Why is log_reuse_wait_desc saying LOG_BACKUP after doing a log backup?
Posted on: November 13, 2013 12:33 pm
(Check out my Pluralsight online training course: SQL Server: Logging, Recovery, and the Transaction Log.) Here’s an interesting situation that sometimes crops up: you’re performing log backups, your transaction log isn’t growing, and yet the value of log_reuse_wait_desc in sys.databases for your database says LOG_BACKUP. What’s going on? I was actually just teaching this stuff […]
How to tell who changed a log file characteristic?
Posted on: September 18, 2013 2:10 pm
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
Posted on: June 30, 2013 8:00 am
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. You can find all the other posts in […]
Tracking page splits using the transaction log
Posted on: February 7, 2013 11:43 am
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
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
(Check out my Pluralsight online training course: SQL Server: Logging, Recovery, and the Transaction Log.) 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 […]
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
(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
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
(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 […]
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 […]
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
(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
Posted on: October 2, 2007 5:20 pm
(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
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
(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 […]
