If you're new to the blog then you may not have seen my Search Engine Q&A series (or seen it and not realized what it is). It started out that I was watching the incoming search engine queries that hit the blog and worked out what common questions people were searching for, but then I [...]
Paul S. Randal
In Recovery...
Common questions from newsgroups and forums
Posted on: March 3, 2009 4:54 pm
Search Engine Q&A #27: How does the storage engine find variable-length columns?
Posted on: March 1, 2009 10:42 am
This question came up in a class I was teaching a few weeks ago: given that the variable-length column offset array in a record only stores offsets, how does the storage engine find a particular variable-length column? The question comes because there's nothing in the record to say which variable-length column is which – so [...]
Search Engine Q&A #26: Myths around causing corruption
Posted on: August 27, 2008 5:11 pm
Every so often I’ll see posts on the various data corruption forums discussing causes of corruption. In this post I want to debunk some of the myths around what can cause corruption. There are really two types of corruption to deal with, physical corruption and logical corruption. Physical corruption This is where something has altered [...]
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 #22: Can all page types be single-page restored?
Posted on: August 11, 2008 7:32 pm
I've had a few follow-ups on my two posts about boot page and file header page corruption – asking if its possible to do single-page restore operations for these pages. Let's try: CREATE DATABASE BootPageTest; GO – Single page restore is only possible using the FULL recovery model ALTER DATABASE BootPageTest SET RECOVERY FULL; GO [...]
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 about [...]
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 [...]
Search Engine Q&A #18: What’s the current uptime of SQL Server?
Posted on: April 17, 2008 1:27 am
Here's a quickie just before we head off to SQL Connections in Orlando. On one of the internal MS forums was the question – how can I tell through T-SQL the last time SQL Server restarted (i.e. the current 'uptime')? The answer relies on the fact that all the background tasks that start when SQL [...]
Search Engine Q&A #17: Split a VLDB into filegroups or smaller databases for backups?
Posted on: March 13, 2008 5:41 pm
Here’s an interesting question that came in to our questions line (questions@SQLskills.com - no guarantee of an answer – I check it every so often): I have seen demonstrations where a large database being broken down into smaller ones using synonym names. I think it was used on a data warehouse and allowed smaller database backups [...]
Search Engine Q&A #16: Concurrent log and full backups
Posted on: February 21, 2008 2:23 am
Here’s a question that came in – what changed in SQL Server 2005 that allows concurrent log and full backups? First a little background, in case you didn’t know about the change in behavior. In SQL Server 2000, a concurrent log backup with either a full or diff backup (I’ll just say ‘full’ from now [...]
Search Engine Q&A #15: Mirrored backups
Posted on: February 5, 2008 5:39 pm
This post is based on one from my old MSDN blog but the topic has come up a few times in recent days so I want to revamp it and re-post. There are two things that confuse people about mirrored backups – can you mix-n-match backup devices from the mirrors, and what exactly do the [...]
Search Engine Q&A #14: Beware 3rd party file-system drivers with DBCC CHECKDB
Posted on: February 4, 2008 6:36 am
Here’s an issue that I thought was a one-off but it just popped up again over the weekend so I want to publicize it. DBA runs a DBCC CHECKDB and gets output like the following: Msg 5172, Level 16, State 15, Line 1The header for file ‘E:\Data\namechanged.mdf:MSSQL_DBCC14′ is not a valid database file header. The [...]
Search Engine Q&A #13: Difference between database version and database compatibility level
Posted on: January 29, 2008 11:51 pm
This has been causing some problems on the various groups and forums over the last few days so I thought I’d repost this from my old Storage Engine blog. The questions have been around attaching 2005 databases to 2000 servers – even databases that are in 80 compat mode – and it doesn’t work. Why? [...]
The frustration of sweeping generalizations – follow on from Search Engine Q&A #12
Posted on: January 29, 2008 5:35 am
A bit more traffic on the thread (see previous post here) prompted me to give my thoughts on the many sweeping generalizations that plague the computer industry and make it difficult sometimes to give advice in forums and blogs. I’d like to repost here (with a few tweaks for clarity). Some examples of questions that [...]
Search Engine Q&A #12: Should you create multiple files for a user DB on a multi-core box?
Posted on: January 29, 2008 2:10 am
There’s been a very interesting discussion going on over at SQLServerCentral.com about whether to create multiple files for a user database because the server has multiple CPUs – see the thread here. I wrote a couple of long responses during the thread that I wanted to duplicate here as I think it’s of broad interest. My [...]
Search Engine Q&A #11: How to design a global-ready database?
Posted on: January 28, 2008 8:33 am
This kind of follows on from my previous post about making sure you have character column lengths that can handle data from different countries (e.g. city names that may be longer in one country than another). A question on the forums today asked what info there is available to help in designing a global-ready database. It [...]
Search Engine Q&A #10: Rebuilding Indexes and Updating Statistics
Posted on: January 27, 2008 12:01 am
It seems like all I’ve been talking about on the forums the last couple of days is the correct order of operations in a maintenance plan. The biggest confusion seems to be about the effect of rebuilding an index on statistics, with some incorrect advice being given out on what to do. Rebuilding an index [...]
Search Engine Q&A #9: How to update constraints?
Posted on: December 5, 2007 12:51 am
A quickie today to get back into the swing of things. In Kimberly's whitepaper on partitioning she discusses the 'sliding window' scenario (where you switch in and out partitions of data into an existing table – see this previous post for a few more details). She recommends that the constraints are extended rather than dropped [...]
Search Engine Q&A #8: How can defragging an index break synchronous mirroring? And what happens?
Posted on: October 25, 2007 5:04 pm
(Been a few days since I posted – had some real work to do :-) Today I'll post a few things from the queue that's been building up) This is part Q&A and part follow-on from my last post about running index maintenance when a database is mirrored. A customer has a maintenance plan that involves [...]
Search Engine Q&A #7: Restoring from a backup file containing multiple database backups
Posted on: October 10, 2007 1:35 am
This is another question that came up on the Disaster Recovery forum on MSDN. Paraphrasing – ‘ I have a backup file containing full backups for 45 databases. How can I restore them all using a script?’ The answer is pretty straightforward. Let’s create the situation described, using 3 databases for clarity rather than 45: [...]
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 [...]
Search Engine Q&A #5: Do multi-core CPUs perform better than single-core CPUs?
Posted on: October 5, 2007 7:43 pm
Here’s an interesting question I was sent by my friend Steve Jones over at SQL Server Central – will a single CPU with dual-cores perform better than two single-core CPUs? Both have two processing cores but the hardware architecture is different – which one will make SQL Server perform better? Well, there’s no hard and [...]
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 #3: Database mirroring failover types and partner timeouts
Posted on: October 1, 2007 11:31 pm
Ok – I’m on a roll today so to finish off I’d like to repost some info about database mirroring failover types (including how you may not actually get a failover when you expect it) and a tip for how to avoid unwanted failovers when combining clustering and mirroring. Mirroring failovers have cropped up several [...]
Search Engine Q&A #2: Moving a database while Database Mirroring is running
Posted on: September 28, 2007 3:27 pm
This was a question from the MSDN Disaster Recovery forum I started while I was at Microsoft. I have a 600 gig database that has a mirror. I need to move the databases from local drives to a SAN. Can anyone recommend a document that lists the steps to go through to move both the [...]
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 [...]
