Beware of advice from 3rd-party tools around dropping indexes

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

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

Why you should not shrink your data files

(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

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

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)

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?

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

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

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?

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

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?

(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

(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

(Check out my online training course: SQL Server: Detecting and Correcting Database Corruption.) 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 is a pretty common scenario I see on the forums – a database goes SUSPECT […]

Search Engine Q&A #26: Myths around causing corruption

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)

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

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?

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

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

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…

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

Common bad advice around disaster recovery

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