SQL Server 2005 Practical Troubleshooting

Database consistency errors

At this point, we have covered three major areas of data recovery: issues with accessing system databases, problems accessing user databases, and backup/restore failures. Now let's talk about detecting database consistency problems during the normal database operation. I classify database consistency detection (errors) into two types:

• Runtime error, errors detected by the engine during standard usage of the database (SELECT, UPDATE, background thread such as lazywrite, and so on)
• Errors detected by DBCC CHECKDB

Handling Database Consistency Runtime Errors

The SQL Server engine contains code to perform some validation of the data (and transaction) log as you execute commands such as SELECT, INSERT, and so on against a database. The following is not a complete list of these errors, but these are the ones I anticipate you may encounter the most.

The SQL Server engine has two methods to report problems reading database pages (and transaction log blocks):

• If the Windows API calls return an error when reading the page, Msg 823 is raised.
• If the Windows API call is successful, the engine performs a series of logical checks on the page (based on database options configured). If any of these checks fails, an Msg 824 is raised.

The following is an example of a Msg 823 error:

Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

As you can see from the message, the SQL Server development team included the Windows error returned from the Windows API call to read the page from disk. Almost every 823 error is caused by a system problem. In this case, SQL Server reports only the error the operating system reported when reading from the file. Look at preceding the example. The text of the message even indicates there is a problem with a disk storage device. The one exception to this guideline that I've seen is error 6, "Invalid handle." In this situation, it is possible SQL Server passed an invalid file handle to the Windows API call, so this should be investigated with technical support.

If the Windows API call to read the page is successful, the following logical checks are applied in this order:

1. Is the page torn (torn page detection)?
2. Did a checksum fail when reading the page (checksum error)?
3. Is the pageid on the page different from the page we expected to read from disk (bad pageid)?

The following is an example of a Msg 824 due to a database checksum validation check failure:

Should you see an 824 error in the ERRORLOG, take immediate action, because this means the engine has detected a consistency problem in the database. The damage may be isolated to the page reported in the log, but it could go beyond that. Therefore, I recommend you run DBCC CHECKDB on the database as soon as possible. But which database? The server cannot provide you database context for this error, but it does provide you the filename associated with the physical filename it was reading for this page. If you can't recognize the database associated with the filename in the error, take the filename from the message and use it in the catalog view query:

At minimum, I expect DBCC CHECKDB to report an error for the page associated with Msg 824. At this point, if only a single page is damaged, you could restore from a backup (remember page-level restore here), or you can repair the database WITH REPAIR_ALLOW_DATA_LOSS. If more errors exist, your options may be more limited, or your data loss may be more severe. The point is, don't ignore 824 errors. It could be the tip of the iceberg of larger damage to your database.

While investigating a series of Exchange storage consistency problems, the Exchange development team discovered that if they were to retry failed read operations from disk, in some cases the second, third, or even fourth read attempt would succeed. So, instead of taking a beating from their customers for Exchange store corruption, they decided to add in logic to retry database read failures.

The SQL Server development team decided to adopt this same strategy for SQL Server 2005. Any read failure (823 or 824) will first be retried up to four times by the SQL Engine, before reporting the failure. If at any point the read succeeds, the following message is written to the ERRORLOG:

Remember that because the retry succeeded, the user will not see any error. The only difference will be a slight delay in performance (which may or may not truly be seen by the application user). But this does not mean you do not need to address this problem. Think of this message the same as you would an 823 or 824 error, but perhaps at a slightly less-urgent pace. The engine avoided a failure, but this doesn't mean this retry will work all the time. Some disk system issue caused at least one read to fail and therefore should be investigated.

The SQL Server engine also has logical checks to detect consistency problems for a row within a database page. In previous versions of SQL Server, this check would result in an assertion (a fatal error that results in a stack dump) with the expression containing the word RecBase. RecBase is the name of a class used to logically read the elements of a row on a page. See Microsoft Knowledge Base article 828337 for more information.

In SQL Server 2005, two errors were introduced to report all row consistency errors detected while reading a row as part of a standard SELECT, UPDATE, and so on, Msg 5242 and 5243. The following is an example of an ERRORLOG containing a Msg 5242 error (with some sections omitted for brevity):

The error message says to contact technical support (I sure hate when our errors say that), but in reality you need to first run a DBCC CHECKDB in the database listed in the message. If it returns errors indicating row damage, you need to address those problems as a true database corruption problem. If CHECKDB does not report errors, it may be a good idea to contact technical support. Have the stack dump and ERRORLOG available for them to review.

Msg 605

Msg 605 is a legacy carryover from the original SYBASE code for SQL Server. In fact, it was pretty much the only way to know about a database consistency problem aside from DBCC CHECKDB. This error check still remains in the SQL Server 2005 code. You should treat this error much like the row-level consistency errors 5242 and 5243. If CHECKDB reports errors, treat the problem as database corruption. If not, consider contacting technical support if the errors occur frequently. It could indicate some problem in the SQL Server code that should be addressed.

Access Violations

For performance reasons, the SQL Server development team cannot put in validation checks for every bit on the database page at every place in the engine code. Therefore, it is possible the engine will run into an access violation because of database corruption. How can you recognize this case? Well, because an access violation could occur at so many different places due to corruption, there is one stack dump signature I could show you to say the problem is corruption. My advice is that if you are getting access violations frequently, you should always ensure DBCC CHECKDB is run for all databases before pursuing the cause of the access violation as a SQL Server bug.

Failed Rollback

Another example is online rollback recovery. If the rollback of a user transaction fails, this is a critical problem. So, the engine reacts by taking the user database offline and restarting it. This allows recovery to run on the database with the hopeful effect of rolling back the transaction that couldn't roll back online. This technique actually is quite successful, because in some cases the online rollback failure is an interim problem that won't reoccur when the transactions are rolled back from the log on disk. Again, in this situation, SQL Server attempts to isolate the failure. Only the database associated with the failed rollback is affected. The rest of the engine remains intact, and other databases are not affected. As with any reliability feature, there are always exceptions. Any failure to roll back a transaction in tempdb results in a server shutdown because there is no method to run recovery on tempdb, and it is a critical shared resource for all users.

Handling DBCC CHECKDB Errors

I've recommended that you run DBCC CHECKDB if you encounter some of the errors described here. You may also decide to run DBCC CHECKDB at any point in time to check the consistency of your database. I presented earlier in the chapter some nice enhancements to CHECKDB to help provide more consistency checks (for example, DATA_PURITY and CHECKCATALOG). Let's talk about a strategy to address errors that you can encounter with DBCC CHECKDB. In this section, I do not go over every error that CHECKDB can report. I talk later about how you can look for information on the web about each of the errors (documented by the authors of DBCC CHECKDB, Ryan Stonecipher and Paul Randal).

Look at Summaries and Recommendation Firs

When you run DBCC CHECKDB and it reports errors, I recommend you do two things first:

• Look at what I call the summary messages.
• Look at the recommendation messages.

Let's look at some example output that contains errors so that I can show you what I mean:

Go to the bottom of the output and look at the summary and recommendation messages for the database:

You know from this message that 13 errors related to the consistency of pages or indexes have been detected (as opposed to allocation errors that are specific to problems with allocation structures found by the CHECKALLOC phase). You also know that in order to repair all 13 errors, you must use the REPAIR_ALLOW_DATA_LOSS repair option. You could just move forward with this recommendation and check the results. However, if you want to know what the repair recommendation is for each table found to have problems, you could check each one. Which tables do I check? Look at the summary messages for each table:

First, you should know that CHECKDB won't produce this summary message for a table unless it detects problems with that table. Second, notice the first summary message says "not associated with any single object." This message indicates that CHECKDB found errors associated with pages that don't appear to belong to a known table.

In this situation, what is happening is that CHECKDB is in a way "double-reporting" errors, but that is to your advantage in this situation. Let's look at the errors "not associated with any single object."

This message indicates a problem with the page ID as found on the page header. The page ID was expected to be 153, but on the page it was found to be 0:0. The routine that detects this problem is used throughout the CHECKDB code to read a page. In this routine, the table associated with the page is found by looking up the allocation unit ID on the page header. As you can see from the error message, that value is 0, and therefore CHECKDB doesn't know what table this page belongs to.

Now look at the errors associated with table1:

If you look closely, you will notice this error is for the same page as the one I just talked about for 8909. In fact, all the pages in errors 8909 and 8928 are the same. Why does the se