Finding a table name from a page ID

This is a question that I was sent over email that I thought would make a good post, and I’m waiting to do a user group presentation in Australia at 2am, so this will keep me busy :-) Imagine you come to work in the morning and notice that some new rows have been entered into […]

Online index rebuild corruption bug in SQL Server 2012 SP1

This is a quick blog post to let you know about a bug in SQL Server 2012 SP1 that can cause data loss when performing index maintenance. The data loss issue can happen in some circumstances when you do a parallel online rebuild of a clustered index while there are concurrent data modifications happening on the table […]

Nasty day-1 bug causing page checksums to miss corruptions on 2008 R2/2012/2014

Over the last few weeks I’ve been investigating a series of customer corruption issues (on one customer’s system) where it appears that the page checksum algorithm is missing a combination of DWORD bit flips, caused by another (Query Processor) bug that’s been introduced in recent builds of SQL Server 2008 R2, SQL Server 2012, and […]

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

New online course: Detecting and Correcting Database Corruption

My latest Pluralsight online training course went live today: SQL Server: Detecting and Correcting Database Corruption It’s 4 hours long and is the first in a two-part series, with a more advanced course coming in a few weeks. The modules in this course are: Introduction Causes of Database Corruption Detecting Page Corruption Consistency Checking DBCC […]

Corruption demo databases and scripts

I originally blogged a series of corruption demos and associated databases back in 2008, for use with SQL Server 2005 and 2008. Since then the releases have changed which databases and corruptions work and I’ve had to rework some of the databases for you. This is an update that takes into account SQL Server 2008R2 […]

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

Unbelievable tale of disaster and recovery

A few days ago one of my new blog readers (a pretty smart cookie, as you'll see) sent me a tale of database catastrophe and an excellent recovery that I’d like to share with you. The story’s been made anonymous and is published with full permission of the author (highlights in bold are mine). Hey […]

TechNet Magazine: May 2010 SQL Q&A column

The May 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: How to change the file locations for a user database The difference between page latches and page I/O latches Why database snapshots are not a good substitute for […]

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: (21/30) corruption can be fixed by restarting SQL Server

(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.) This myth (and derivatives) are very common among non-DBAs as […]

A SQL Server DBA myth a day: (17/30) page checksums

(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 few people have suggested some of the myths around […]

A SQL Server DBA myth a day: (16/30) corruptions and repairs

(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.) Short and quicky today as in the dictionary under 'busy' […]

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

Diskeeper 10 Intelliwrite corruption bug

A couple of years ago I blogged about 3rd-party file system filter drivers and how if they're not coded correctly to cope with NTFS alternate streams they will cause all kinds of weird corruption errors to be reported when DBCC CHECKDB is running. At the time I'd only seen the issue a couple of times so thought […]

2005 corruption bug with XML index rebuild fixed in latest CU

2005 SP3 CU6 contains a fix for a corruption bug that can happen after rebuilding an XML index that contains XML elements greater than 8000 bytes. The bug was fixed in 2008 but hadn't made it back to 2005 until the last CU was released. I had email from someone back in August experiencing corruption […]

Don’t confuse error 823 and error 832

I was reading a thread on SQL Server Central today where someone replied to a question and confused 823 with 832. Now, 823 is a nasty error to get – it says that an I/O operation failed at the OS level and the I/O subsystem is causing corruption – SQL Server didn't even get a […]

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

TechNet Magazine: August 2009 SQL Q&A column

The August 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: Proactive page checksum failure detection Why GUIDs make for bad clustered index keys  Possible problems using a log shipping secondary for reporting  Recovery model choice for log size […]

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

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

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

SQL 2005 SP2 maintenance plan bug masking corruption

Not much to do here in Tokyo while waiting for the Bangkok flight except read and blog, and I've already done enough reading for one day on the flight here – so that leaves blogging! Here's an interesting case that I got involved in on SQLServerCentral (here's the original thread, linked here with permission of […]

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

Msg 602, Level 21, State 50, Line 1

(Yes, Kimberly's lecturing again…) Here's something that I've seen crop up a lot recently on corruption forums: Server: Msg 602, Level 21, State 50, Line 1 Could not find row in sysindexes for database ID 10, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes. This isn't corruption - it comes from trying to […]

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

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

Couple of LOB corruption bugs fixed in the latest CUs for 2005

Both 2005 SP3 CU2 and 2005 SP2 CU12 contain fixes for two bugs that cause corruption in LOB data. The first bug may happen when a column type is converted to varbinary(max), change the large-value-types-out-of-row option to true, insert a row, and then try to update it – described in KB 961648. The second bug […]

How can a log backup fail but a full backup succeed?

This is an interesting case that cropped up yesterday – the transaction log is damaged so a log backup doesn't work (with the error below): Backup detected log corruption in database FakeDBName. Context is FirstSector. LogFile: 2 'F:\SQLLOGS\XYZ\FakeDBName_Log.ldf' VLF SeqNo: x502e VLFBase: x2ce40000 LogBlockOffset: x2d0a9000 SectorStatus: 2 LogBlock.StartLsn.SeqNo: x4ee3 LogBlock.StartLsn.Blk: x1348 Size: x200 PrevSize: x400 […]

A little-known sign of impending doom: error 825

There are two pretty well-known I/O errors – 823, and 824 – but there's also one called 825 which most DBAs do*not* know about, and definitely should. From SQL Server 2005 onwards, if you ever see an 823 or 824, SQL Server has actually tried that I/O a total of 4 times before it finally […]

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

TechNet Radio interview on database corruption

Well, we're back from vacation finally (only for 10 days and then off to India and Thailand for 3 weeks… phew) and I've got a bunch of blogs posts to catch up on. First up – I did an interview with TechNet Radio in mid-December where I talked about database corruption and things to do […]

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

Corruption bug that people are hitting – Msg 2511 using DBCC DBREINDEX

Here's another corruption bug that people are hitting on 2005 SP2 – something I didn't know about until today. The situation is this: a table with a non-unique clustered index (i.e. so a hidden uniquifier column is created), and then rebuilt using DBCC DBREINDEX. Sometimes an incorrect query plan is chosen so the rows in […]

Corruption bug that people are hitting: Msg 8914 – PFS free space

(Posted with permission of the dev team) Here's an interesting bug that people are hitting. I found out about this while here in Barcelona at TechEd when I got roped into a discussion with a couple of Microsoft colleagues. Their customer was seeing errors like the following: Msg 8914, Level 16, State 1, Line 1 […]

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

Why does the buffer pool contain so many dirty tempdb pages?

(Quickie post #2 while it's Kimberly's turn to lecture this morning…) Greg asked a question regarding the script I posted to examine buffer pool contents (paraphrased) – why does the buffer pool seem to contain such a high proportion of dirty tempdb pages on busy production systems? The answer is to do with the recoverability of […]

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 #22: Can all page types be single-page restored?

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

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

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

TechNet Magazine: Effective Database Maintenance article and August SQL Q&A column

Well, we’re just back from vacation (photo blog post to follow) and I’ve heard that the feature article on Effective Database Maintenance I wrote for the August issue of TechNet Magazine is live on the web. It also includes a 5 minute long screencast I recorded where I demo the effect of database shrink on index […]

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

In Print: TechNet Magazine June 2008 SQL Q&A Column

My first magazine article is in print! I’ve taken over the bi-monthly SQL Q&A column for TechNet Magazine and I just received the June magazine in the mail today with my first column in it. Topics covered are: Creating corruption and using page checksums The shrink-grow-shrink-grow trap How many databases can be mirrored per instance […]

Conference Questions Pot-Pourri #7: How to create Agent alerts

Many times I've been asked to do a blog post about creating Agent alerts, and given that today I demo'd it as part of our Accidental DBA workshop at Connections, it seemed a good time to do the blog post too! I demo this in the context of alerting a DBA when an 823 or […]

Inside The Storage Engine: Does turning on page checksums discard any torn-page protection?

This is a really interesting question that came up in the Microsoft Certified Architect class I’m teaching at present – if a database has torn-page protection enabled, and page checksums are enabled, is all the existing torn-page detection lost? This is an important question, because enabling page checksums doesn’t suddenly make all allocated pages be […]

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

Search Engine Q&A #14: Beware 3rd party file-system drivers with DBCC CHECKDB

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: Wow! Looks like something’s seriously wrong with that database. Until you find that this happens on *all* the databases on […]

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

Conference Questions Pot-Pourri #1: Indexes, stats, corruption, and Enterprise-only features

As promised, here’s the first of the grab-bag of questions we were asked during conferences. I’m blogging a selection of the stuff I noted down. These are some of the questions we were asked during our pre-con at SQL Connections on Database Maintenance: From Planning to Practice to Post-Mortem. It was cool that people came prepared […]

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

Search Engine Q&A #4: Using EMERGENCY mode to access a RECOVERY PENDING or SUSPECT database

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

SQL Server 2008: Automatic Page Repair with Database Mirroring

One of the hottest features in SQL Server 2005 is database mirroring, and it's helped many companies implement successful and relatively inexpensive high-availability strategies. In SQL Server 2008, Database Mirroring has been enhanced in several ways – one of which is the ability to automatically repair corrupt pages! This feature is based on the fact that the […]

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

How to tell if the IO subsystem is causing corruptions?

  One of the comments I received recently is below: Hi Paul, If the corruption happens to be related to I/O Erros and there is nothing in the Event log or anywhere that points to I/O related issues, is there any Trace flag that we can enable when performing checkdb or checktable operations that can […]

Example 2000/2005 corrupt databases and some more info on backup, restore, page checksums and IO errors

This was originally posted as two posts on the SQL Server Storage Engine site. It was very popular so I’ve combined the two posts together and added a bunch more commentary – especially on page checksums and IO errors. You may also notice some color differences between the scripts from earlier in the year and today’s post […]