(In this post I'm not going to name-and-shame, as I'm sure the problems will be fixed in time.) I want to warn you about unthinkingly acting on advice from 3rd-party tools around dropping nonclustered indexes. One of my long-term clients recently bought and installed a new tool and has been asking me about recommendations from [...]
Paul S. Randal
In Recovery...
Beware of advice from 3rd-party tools around dropping indexes
Posted on: April 4, 2011 1:12 pm
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 [...]
Why you should not shrink your data files
Posted on: June 24, 2009 10:02 am
(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.) One of my biggest hot-buttons is around shrinking data files. Although I used to own the shrink code while I was at Microsoft, I never had a [...]
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 [...]
Misconceptions around database repair
Posted on: May 15, 2009 5:02 pm
This week's been a busy one on the forums and Twitter, with lots of interesting problems people are hitting. One of the things I've noticed is that there are lot's of misconceptions about running repair, so to round out Friday I'm going to run through a list of them for you. Here are the misconceptions, [...]
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 [...]
Is running repair on msdb safe?
Posted on: March 25, 2009 9:02 am
Just saw this on a forum – running REPAIR_ALLOW_DATA_LOSS on msdb to cope with corruption. Yes, this will work but it's certainly not safe and something I'd only recommend as a last resort. Here's why: msdb stores your backup history and your Agent jobs. REPAIR_ALLOW_DATA_LOSS works by usually "delete what's broken and fix up all [...]
Mandatory SQL breathalyzer test
Posted on: March 4, 2009 6:38 pm
Today we've spent a lot of the day in discussions with some folks about developers vs. DBAs, and how it's often the case that the two don't work together. Developers need to know the effect of their design choices on the database, and DBAs need to educate the developers. There should be a close working [...]
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 [...]
Corruption survival techniques – useless?
Posted on: November 25, 2008 11:29 am
Now, I’m very thick-skinned and I know there are always some people in a conference session who don’t agree with everything I say (that’s human nature, and I’m totally cool with that) but this one I just couldn’t pass up mentioning here on the blog as I *utterly* disagree with the advice in that post, [...]
Sparse columns: misleading info in Books Online
Posted on: October 19, 2008 3:30 pm
The sparse columns feature in SQL Server 2008 is generating lots of interest from people looking to deploy extensible schemas. I've seen a few questions from people that are confused by some of the info in Books Online, particularly about adding and removing sparse columns from a table. There's a section in BOL that states [...]
Can the hack-the-suspect-database-into-the-server trick work for corrupt file headers?
Posted on: September 2, 2008 10:08 am
(Quickie post #3 while it's Kimberly's turn to lecture this morning – better pay attention now before I get into trouble) Gail asked a (paraphrased) question about the trick to hacking a detached suspect database into the server again – will it work for a detached database with multiple data files in the primary filegroup [...]
Better behavior with suspect databases in SQL Server 2008
Posted on: September 2, 2008 9:44 am
(Quickie post #1 while it’s Kimberly’s turn to lecture this morning…) I had a comment on my post dealing with suspect databases, saying that the 2008 behavior is much better. I hadn’t tried this so it was (really good) news to me. So, trying the same steps from that blog post on 2008 – everything’s [...]
Creating, detaching, re-attaching, and fixing a SUSPECT database
Posted on: August 29, 2008 7:55 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 post I’ve been trying to get to since I started blogging a couple of years ago: how to re-attach a detached SUSPECT database. This [...]
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 #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 [...]
Backups, trains, birds, and painfully contrived movie plots
Posted on: May 30, 2008 12:34 am
Totally off-topic blog post this time. I haven’t blogged in a week as we’ve been in Chicago and Illinois for the memorial for Kimberly’s Father (see here). Everything went really well at the memorial and then the ashes scattering in Lake Michigan – perfect sailing weather! The only fly in the ointment came back to [...]
How hard is it to pick the right non-clustered indexes?
Posted on: May 14, 2008 10:13 pm
Kimberly and I were presenting at our local (Redmond) .Net Developers Association on Monday and the following question came up while Kimberly was talking about missing and extra indexes (paraphrasing): What’s the best non-clustered index to use for the query with a predicate WHERE lastname = ‘Randal’ AND firstname = ‘Paul’ AND middleinitial = ‘S’? [...]
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 [...]
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 [...]
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 [...]
Common bad advice around disaster recovery
Posted on: September 13, 2007 8:05 pm
Now that I have a little more time on my hands I’ve been jumping back into some of the online forums. Last summer I posted on a few bits of bad advice I’ve seen in the forums but yesterday I was stunned by some of the terrible advice I saw being given out. So, I’d [...]
