How Many Objects Can Own Pages in a Mixed Extent

I saw an exchange on Twitter this past weekend on the #sqlhelp hash tag about how pages are allocated for a temp table. The replier (blog|@sqL_handLe) — whom you should be following if you’re not already, very smart guy — gave the classic response about mixed extents and how they can be owned by up to 8 objects. The link he provided, Understanding Pages and Extents, agrees with him and says, “Mixed extents are shared by up to eight objects”.
read more

Introduction to Integrity: Data Purity Errors

The final day of this six part series, Introduction to Integrity, sponsored by Idera and their new free tool SQL Integrity Checkhas finally reached its conclusion. Today is the final post and the final integrity issue that I am going to talk about: Data Purity Errors. Data purity issues are when the value stored in a column is out of range for the data type. This is a rare error for databases created in SQL Server 2005 or newer. The
read more

Introduction to Integrity: Chain Linkage Problems

We’re approaching the end of our six part series Introduction to Integrity, sponsored by Idera with our last two postings this week. One of the last two integrity issues, and the one I’ll cover today is chain linkage problems. Chain linkage problems are metadata corruption issues. It is possible in certain situations for the links from one page to another to get corrupted. All pages in an object or index have pointers to the previous and next pages in the
read more

New Whitepaper: 5 Most Common Critical SQL Integrity Issues

Have you been putting off running integrity checks on your databases? Are you unsire where to start or what to do? Stop hesitating and get started right now. If you are new to database integrity and corruption, we have a helping hand to get you started. A new whitepaper on the 5 Most Common Critical SQL Integrity Issues that DBAs will face in their career will get you started. A crucial part of managing SQL Server is performing regular integrity
read more

Introduction to Integrity: Corruption in Nonclustered Indexes

It’s day four of our six part series Introduction to Integrity, sponsored by Idera, and tonight’s topic is going to be an easy one. You won’t even need good backups to deal with this one. After you have dealt with several incidents of corruption, you will find yourself hoping for an index ID greater than 1 when you encounter corruption. Corruption in nonclustered indexes is the easiest form of corruption to fix as long as the underlying heap or clustered
read more

Introduction to Integrity: Corruption in Clustered Indexes and Heaps

For day three of this six part series Introduction to Integrity, sponsored by Idera, we will take a look at my number two integrity issue: corruption in clustered indexes or heaps. Unlike corruption in allocation pages, we have several options for dealing with this type of corruption. This particular corruption issue does require a lot more care than many of the others types we will look at in this series. If the corruption occurs in index ID of 0 or
read more

Introduction to Integrity: Corruption in Allocation Pages

Welcome back for day two of this six part series Introduction to Integrity, sponsored by Idera. In this post, I want to dig into the first integrity issue, corruption in allocation pages. If you run the DBCC CHECKDB command and get errors, I isolate the most important errors (Level 16) and look at the page IDs in the errors. one of the first things I want to know is what type of page is corrupted. As I said in day
read more

Introduction to Integrity: Interpreting DBCC CHECKDB Results

I want to start a short 6 post series to serve as an introduction to integrity in SQL Server. Most posts that I write on this subject are for intermediate or advanced users, so this is a break from the normal for me. I think it is a good break though because I think there are very few introductory posts or articles on this subject. I was inspired to write this introductory series when I alpha-tested a new free tool
read more

DBCC CHECKDB … Terminated Abnormally Due to Error State 6

I recently encountered a DBCC CHECKDB failure with a fairly non-specific failure message. The error message said it terminated abnormally with error state 6. I felt I knew what the underlying cause was, but I did some web searches trying to validate what I suspected. I could find nothing that told me what error state 6 meant. The actual text of the error message was: DBCC CHECKDB (<Database name>) WITH all_errormsgs, no_infomsgs, physical_only, tableresults executed by SQLLaptopSQLSoldier terminated abnormally due
read more

Day 24 of 31 Days of Disaster Recovery: Handling Corruption in a Clustered Index

Welcome to day 24 of my 31 Days of Disaster Recovery series. Previously, I’ve talked about several different forms of corruption: Nonclustered Index, Allocation Pages, and Tempdb. these were all fairly simple to fix. Today I’m going to dive into a scenario that is a little more complex, clustered indexes. Clustered indexes are the base data. This means we have to go to a restore scenario. Hopefully, the corruption isn’t wide spread. if it’s just 1 or a few pages,
read more