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 […]
Paul S. Randal
In Recovery...
Finding a transaction in the log for a particular user
Posted on: June 3, 2015 11:45 am
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 […]
Real world story of DBCC PAGE saving the day
Posted on: September 19, 2013 1:20 pm
Last week I answered a question on Twitter about DBCC PAGE and asked if I could be sent more details about the problem. The person was gracious enough to send me a detailed explanation, and it’s such an interesting scenario that I asked for and received permission to share this cut-down, anonymous version of the […]
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 […]
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 […]
DBCC WRITEPAGE: an introduction
Posted on: February 5, 2013 11:45 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.) SQL Server’s undocumented commands, options, and trace flags are sometimes carefully-guarded secrets. I’ve been a major secret-keeper of lots of these, having written/re-written a bunch of the undocumented DBCC commands. I’ve been […]
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 […]
Why are so many DBCC commands undocumented?
Posted on: June 5, 2012 5:31 am
Last week on the MVP distribution alias there was a discussion on undocumented DBCC commands and someone asked why so many potentially useful DBCC commands are undocumented. I briefly explained why, and I’ve discussed this openly during classes and conferences too and don’t consider it an NDA explanation, so I thought I’d share it with […]
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 […]
New script: When were the sp_configure options last changed?
Posted on: April 26, 2012 11:30 am
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 […]
Disaster recovery 101: hack-attach a damaged database
Posted on: June 18, 2010 12:11 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.) As a follow-up to my previous post Disaster recovery 101: backing up the tail of the log, I want to describe how to re-attach a damaged database if someone’s detached it accidentally while […]
CHECKDB From Every Angle: How to tell if data purity checks will be run?
Posted on: August 19, 2009 11:04 am
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 […]
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 […]
Investigating locking and deadlocking with %%lockres%%
Posted on: May 30, 2009 3:41 pm
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 […]
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 […]
FILESTREAM directory structure – where do the GUIDs come from?
Posted on: March 17, 2009 4:19 pm
I’m teaching the Microsoft Certified Masters – Database qualification this week here in Redmond, and in part of day one I discuss the FILESTREAM directory structure. I was asked the question where do the directory name GUIDs come from? so I started digging around in the system tables while Kimberly was lecturing. Take a look […]
TechEd: 80 minute video of Corruption Survival Techniques presentation
Posted on: December 5, 2008 6:14 am
For those of you who couldn't make it to a conference this year where I presented my Corruption Survival Techniques session, the folks at TechEd EMEA have just posted an 80 minute long video of the presentation I did in Barcelona in early November. It walks through I/O errors, what CHECKDB does, how it works, […]
Conference corruption demo scripts and example corrupt databases
Posted on: November 24, 2008 11:27 am
This blog post explains the demo scripts and databases I've posted to cover all the Corruption Survival Techniques and DBCC CHECKDB sessions I've presented at conferences this year. There are two zip files you need to download: the example corrupt databases (36-MB zip) (and this one if you're on 2008) and the demo scripts. These […]
Search Engine Q&A #21: File header pages, and file header corruption
Posted on: July 25, 2008 2:10 pm
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
Posted on: July 11, 2008 12:01 am
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 […]
CHECKDB From Every Angle: Using DBCC PAGE to find what repair will delete
Posted on: June 9, 2008 3:54 pm
(I’m actually on-stage here at TechEd doing the DAT track pre-con with Kimberly – she’s on now until lunch so I’m catching up on forum problems…) Here’s a question that came up on of the SQLServerCentral.com corruption forums I monitor that I think is worth blogging about. To paraphrase: I have a bunch of corruptions in […]
New script: How much of the database has changed since the last full backup?
Posted on: April 8, 2008 4:49 pm
Over the weekend there was a question on one of the internal aliases at MS: how can I tell what percentage of a database has changed since the last full backup, so I can choose between a differential or full backup? No such code exists as far as I know – until now! I happened […]
SQL Server 2008: New (undocumented) physical row locator function
Posted on: March 20, 2008 12:25 am
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: sp_AllocationMetadata
Posted on: March 12, 2008 10:38 pm
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 […]
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 […]
Inside the Storage Engine: Proof that records are not always physically stored in index key order
Posted on: October 4, 2007 3:29 pm
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: Using DBCC PAGE and DBCC IND to find out if page splits ever roll back
Posted on: October 1, 2007 1:53 am
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, […]
