Corruption recovery using DBCC WRITEPAGE

A couple of week ago I kicked off a survey about the extent of your experience with the DBCC WRITEPAGE command. Here are the results: The “Other” values are: 9 x “Read your post on it, may practice it one day, but it’ll always be last resort to use.” 6 x “I read your post […]

Two new courses on Advanced Corruption Recovery Techniques and Maintenance Plans

I’ve just heard from our good friends at Pluralsight that our two latest online training courses are now live and available for watching! My latest course is SQL Server: Advanced Corruption Recovery Techniques which follows on from my earlier SQL Server: Detecting and Recovering from Database Corruption course. The course is 4 hours long and has […]

Bug: DBCC CHECKDB data purity checks are skipped for master and model

After going back and forth with someone with a corrupt master database, I’ve just discovered a bug. And unfortunately it goes back to SQL Server 2005 so the responsibility is mine for not realizing the problem at the time (but at least it’s not a bug in how my DBCC CHECKDB code works, it’s a […]

Easy automation of SQL Server database maintenance

A while back I kicked off a survey asking what mechanism you use for running your regular SQL Server database maintenance. Here are the results: The “Other” responses were: 7 x “A combination of maintenance plans generated from SSMS wizard and a home-grown index maintenance script” 5 x “Combo of own and modified scripts of […]

DBCC CHECKDB scalability and performance benchmarking on SSDs

Back in February I ran a bunch of performance tests of DBCC CHECKDB on SSDs, to evaluate the effects of degree-of-parallelism (DOP) and various options and traceflags, and now I’m finally getting around to presenting the results. Make sure to also read the recent post where I talk about the detrimental effect of computed-column indexes on […]

DBCC CHECKDB performance and computed-column indexes

It’s no secret that DBCC CHECKDB has some performance quirks based on the schema of the database being checked and various kinds of corruptions. I was recently doing some scalability testing of DBCC CHECKDB for a blog post and discovered quite a nasty performance issue that exists in all versions of SQL Server back to […]

Buffer pool disfavoring

Have you ever wondered why DBCC CHECKDB, for instance, doesn’t flush the buffer pool when it runs on a database that is larger than the memory capacity of the server? There’s cool mechanism that it uses called disfavoring. When a buffer is no longer required, the buffer can be marked as the least recently used of […]

DBCC WRITEPAGE: an introduction

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

Why are so many DBCC commands undocumented?

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

SQL Server Magazine: feature article on using database repair

The September SQL Server Magazine articles are now available on the web and include my latest feature article on Using Database Repair for Disaster Recovery. It includes a detailed walk-through of a disaster scenario where all backups include the corruption – showing you how to run repair and then try to recover some of the […]

(Not a) CHECKDB error “Unable to deallocate a kept page”

I've recently found out that there's a bug some people are hitting when running DBCC CHECKDB on SQL Server 2005 – you get an error like: Msg 831, Level 20, State 1, Line 1 Unable to deallocate a kept page. This is a bug in the database snapshot code, and shows up sometimes when running DBCC […]

Disaster recovery 101: fixing metadata corruption without a backup

(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.) Yesterday on the MVP newsgroup someone was asking how to fix some metadata corruption when their backups all had the corruption in too. There are two options: export everything out to a […]

A SQL Server DBA myth a day: (27/30) use BACKUP WITH CHECKSUM to replace DBCC CHECKDB

(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.) A short one today as I'm up to my eyeballs […]

A SQL Server DBA myth a day: (2/30) DBCC CHECKDB causes blocking

(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.) It's the last day of our class here in Boston […]

Sample corrupt databases to play with

The very worst piece of advice I ever saw on the Internet was in response to someone asking on a SQL newsgroup ‘how can I create a corrupt database?’. The first response was: When I want to corrupt a database to play with, I go into the data center, find a hard-drive and flick the […]

CHECKDB From Every Angle: How to tell if data purity checks will be run?

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

Weekly survey: how often do you run consistency checks?

In this week's survey I'd like to know how often you run consistency checks on your *most critical* production database, regardless of *how* you run them (we did that survery already – see Importance of how you run consistency checks). I'll report on the results around July 4th. I'd only like you to answer for […]

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

Issues around DBCC CHECKDB and the use of hidden database snapshots

There are a couple of issues that I’ve heard of in the last few weeks (one while onsite at a customer) and I think they might bite some people so I’d like to share them with you. DBCC CHECKDB in 2005 onwards uses a hidden database snapshot to create the transactionally-consistent point-in-time view of the database that […]

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

How to get all the corruption messages from CHECKDB

Hopefully all of you reading my blog already know to use the WITH ALL_ERRORMSGS option whenever you run DBCC CHECKDB (which is now the default in 2005 SP3, haven't checked if it made it into 2008 SP1). If you're doing some quick analysis of the results to see, for example, whether it's just nonclustered indexes […]

Misconceptions around corruptions: can they disappear?

This has come up a few times now, most recently in an email question this morning - subsequent runs of DBCC CHECKDB show varying numbers of corruptions, and sometimes no corruptions – what's going on? Even more strange – a maintenance job runs a DBCC CHECKDB, which shows errors, but then in the morning – no […]

Do transactions rollback when DBCC CHECKDB runs?

Recently there’s been a spate of people noticing strange behavior from active transactions when DBCC CHECKDB (or any of the other DBCC consistency checking commands run). For example, I’ve create a database call DbccTest with a single table. In one connection I do: BEGIN TRAN INSERT INTO t1 VALUES (1, 1); GO And in another […]

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

DBCC PINTABLE

(Continuing my habit of blogging while Kimberly's presenting – at least I'm not on stage this time…) In early versions of SQL Server, it was sometimes necessary to 'pin' the pages from a heavily used table in the buffer pool to help performance. In later versions, the buffer pool became very sophisticated and does an […]

IAM page corruption examples

A thread cropped up on SQLServerCentral involving IAM chain corruption (see Inside the Storage Engine: IAM pages, IAM chains, and allocation units for details of IAM chains). The error from DBCC CHECKDB was: Server: Msg 2576, Level 16, State 1, Line 1 IAM page (0:0) is pointed to by the previous pointer of IAM page (1:394336) […]

Our book has been published!

  Microsoft SQL Server 2008 Internals finally starts to roll off the presses at Microsoft Press today! For me this is the first time in print (in book form at least) and my consistency checking chapter (about 1/10th of the book) represents a complete brain-dump of everything I know about how DBCC CHECKDB works. Kimberly […]

TechNet Magazine: April 2009 SQL Q&A column

The April 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: Disappearing errors with DBCC CHECKDB Provisioning tempdb when moving from 2000 to 2008 Does fillfactor prevent fragmentation and should it be set instance-wide Avoiding FILESTREAM performance problems Check […]

CHECKDB From Every Angle: SQL and Windows bugs around database snapshots

As you may know, DBCC CHECKDB (and some of the other DBCC CHECK*) commands use an internal database snapshot to get a transactionally consistent view of the database (if you didn't, see my blog post CHECKDB From Every Angle: Complete description of all CHECKDB stages for an explanation). It's entirely possible that the database snapshot may […]

Corruption errors: Msg 5242, Level 22

In SQL Server 2000 and before, the symptoms of database corruption would occasionally manifest themselves as asserts, such as: SQL Server Assertion: File: <recbase.cpp>, line=1378 Failed Assertion = 'm_offBeginVar < m_sizeRec'. To reduce the number of assertions being fired by the SQL Engine, my team changed these asserts into real error messages for SQL Server […]

TechEd demo: nonclustered index corruption

This blog post describes the demo "2 – NC Indexes" from my Corruption Survival Techniques conference session from various conferences in 2008. The links to the scripts and databases to use are in this blog post. Edit 6/4/2012: Be aware that in versions of SQL Server from 2008 onwards, you may only be able to […]

TechEd: 80 minute video of Corruption Survival Techniques presentation

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

TechEd demo: corruptions fatal to DBCC CHECKDB

This blog post describes the demo “1 – Fatal Errors” from my Corruption Survival Techniques conference session from various conferences in 2008. The links to the scripts and databases to use are in this blog post. The aim of this demo is to show that sometimes a database is so corrupt that DBCC CHECKDB just […]

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

Conference corruption demo scripts and example corrupt databases

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

SQL Server 2008 Internals available to pre-order on Amazon.com

  Very exciting! The SQL Server 2008 Internals book we're working on with Kalen is now available to pre-order on Amazon, ahead of the planned publication date of February 18th next year. I'm neck-deep in the Consistency Checking Internals chapter right now – its shaping up to be about 100 pages on its own! There'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 #21: File header pages, and file header corruption

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

We’re writing a book – finally!

Well, almost… Kimberly and I have agreed to co-author the upcoming SQL Server 2008 Internals book with our good friend Kalen Delaney. This is the 2008 evolution of Kalen’s Inside SQL Server series of books and will be published by MS Press around February 2009. The other authors are (also our good friends) Adam Machanic […]

Using the Dedicated Admin Connection to fix Msg 8992: corrupt system tables

Today I presented my brand new session Surviving Corruption: From Detection to Recovery at TechEd. I had a lot of fun putting together the demos, presenting the session, and talking to people afterwards. During the session, I promised to blog each of the demos so that everyone can run through them – here's the first one. […]

CHECKDB From Every Angle: Using DBCC PAGE to find what repair will delete

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

CHECKDB bug that people are hitting – Msg 8967, Level 16, State 216

Before I start, I want to make it clear that you can only hit this bug if you ALREADY have corruption, that it's quite rare, and that there is a workaround. I've noticed a few more people in the forums having CHECKDB fail with this particular error in the last month Msg 8967, Level 16, […]

CHECKDB From Every Angle: When did DBCC CHECKDB last run successfully?

This came up several times during the week so I thought it was about time to blog about it. One of the new features we put into SQL Server 2005 was storing the last time that DBCC CHECKDB completed successfully (called the last-known good time). What does successfully mean? This is the catch – if […]

Patents and parties

In the mail today I received notice that my first software patent has been granted by the US Patent Office :-) (after being filed 3.5 years ago while I was still writing DBCC CHECKDB code!) It’s basically a way to run DBCC CHECKDB on a database stored in a backup without actually having to restore the […]

CHECKDB From Every Angle: Consistency Checking Options for a VLDB

This is a question that comes up a lot – how to run consistency checks on a VLDB? We’re talking hundreds of GBs or 1 TB or more. These databases are now common on SQL Server – there are many customers with multi-TB databases. Any experienced DBA knows the value of running consistency checks, even […]

CHECKDB From Every Angle: Can CHECKDB repair everything?

In my previous post on interpreting CHECKDB output, plus in my DBCC Internals session at TechEd IT Forum yesterday, I mentioned there are some things that CHECKDB can’t repair. In this post I want to go into a bit more detail – based on a post from my old Storage Engine blog. Before anyone takes […]

CHECKDB From Every Angle: How long will CHECKDB take to run?

This is a subject I posted about last year on my old blog but it came up at SQL Connections last week several times so I want to repost it for those who’ve just started following my blog. There’s only one time when you should be trying to work out how long a CHECKDB is […]

CHECKDB From Every Angle: Tips and tricks for interpreting CHECKDB output

Last week at SQL Connections someone said that CHECKDB’s output is ‘useless’.  Given that I wrote CHECKDB for SQL Server 2005 I was only mildly offended :-) But there’s a lot of truth in that statement – the error messages from CHECKDB do tell you exactly what’s corrupt in the database but they’re not exactly […]

Auto-shrink – turn it OFF!

I’m in the middle of a flight from Washington D.C. to Zurich on the way to Barcelona for TechEd IT Forum and I can’t sleep – Kimberly’s out like a light so what else is there to do except write another blog post? :-) OK – actually posting this from Barcelona on Tuesday before our […]

EMERGENCY-mode repair: the very, very last resort

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

Inside the Storage Engine: Using DBCC PAGE and DBCC IND to find out if page splits ever roll back

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

CHECKDB From Every Angle: Automate your database integrity checks with SMO

Quickie this morning – I was surfing some MVP blogs this morning and came across a series of posts by Allen White around using VB Script and SMO to automate regular maintenance jobs. His latest post contains code to regularly run consistency checks and more, and report any failures. Very nice – check it out!

CHECKDB From Every Angle: Complete description of all CHECKDB stages

On the Storage Engine blog last year I started two series that I got distracted from – one on CHECKDB and one on fragmentation. With the benefit of hindsight, I’m going to start 3 series on my new blog here – one on CHECKDB (‘CHECKDB from every angle’), one on indexes (‘Indexes from every angle’), […]

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