Common questions from newsgroups and forums

If you're new to the blog then you may not have seen my Search Engine Q&A series (or seen it and not realized what it is). It started out that I was watching the incoming search engine queries that hit the blog and worked out what common questions people were searching for, but then I turned it into a 'what are common problems on the forums that I see'. Anyway, there are a bunch of questions that I've answered that make some interesting reading and touch on some pretty diverse topics. Here they are in chronological order, with links. Don't forget that I fastidiously categorize all posts and you can use the category links on the left hand side of the blog.

  1. Running out of transaction log space – how to tell why your log is growing and two common cases – no log backups and a long-running transaction
  2. Mirroring – how long does it really take to detect a failure with database mirroring and how to combine mirroring and clustering
  3. EMERGENCY mode – how to access a RECOVERY_PENDING or SUSPECT database
  4. Using the undocumented fn_dblog log analysis tool – how to see whether a transaction is contained in a backup
  5. Multi-file backups – how to restore from a backup file containing multiple backups
  6. Updating constraints – how to update the constraints in the partitioning sliding-window scenario
  7. Multi-file databases to avoid contention – for tempdb and for user databases – should you? And the follow-on about the dangers of sweeping generalizations
  8. Backing up a VLDB – whether to split the VLDB into filegroups or smaller databases


Search Engine Q&A #27: How does the storage engine find variable-length columns?

This question came up in a class I was teaching a few weeks ago: given that the variable-length column offset array in a record only stores offsets, how does the storage engine find a particular variable-length column?

The question comes because there's nothing in the record to say which variable-length column is which – so how does it work? The answer is a combination of the null bitmap in the record, plus the metadata for the table/index stored in the system tables. All variable-length columns have a fixed 'location' within the variable-length portion of the record when they are non-null. Let's see what I mean.

First off I'll create a test table with a single record, with all variable-length columns null, and dump out the record using DBCC PAGE:

CREATE TABLE vartest (c1 INT, c2 VARCHAR (100), c3 VARCHAR (100), c4 varchar (100));

DBCC PAGE (test, 1, 152, 1);

Slot 0, Offset 0x60, Length 11, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x66F4C060

00000000: 10000800 01000000 0400fe†††††††††††††………..

The 0x0400fe in bold-underlined is the null bitmap. The 0x0400 reverses to 0x0004 – which is the count of columns in the record. The 0xfe is the actual bitmap, which is 11111110 in binary. So all columns in the record except the first are null (although the null bitmap only needs to store bits for 4 columns, all bits in the null bitmap that aren't used are set to indicate a null). You can also see that there's no variable-length column offset array as *all* the variable-length columns are null.

Now I'll make the middle variable-length column non-null and dump out the record using DBCC PAGE:

UPDATE vartest SET c3 = 'c3c3c3c3';
DBCC PAGE (test, 1, 152, 1);

Slot 0, Offset 0x60, Length 25, DumpStyle BYTE
Memory Dump @0x67FEC060

00000000: 30000800 01000000 0400fa02 00110019 †0……………
00000010: 00633363 33633363 33†††††††††††††††††.c3c3c3c3

We can see the null bitmap (bold-underlined) has changed to 0xfa in hex, or 11111010 in binary, to reflect that the first and third columns are non-null. As soon as a single variable-length column in the record is non-null, the variable-length column offset array is populated for all variable-length columns up to and including the last non-null column. The array has the count of entries in the array (the 0200, which reverses to 0x0002), and then for each column it stores the offset to the start of the following column, to avoid storing the length too. The difference between successive start-of-column offsets is the length, with the first length calculated using the end of the array itself as the starting point of the first column. In the record above, the array has two entries, ending at offsets 0x0011-1 and 0x0019-1 in the record. The array itself finishes at 0x0010, so the first entry in the array is essentially pointing at an empty value, which we know to be actually a null value (instead of a non-null empty value) using the null bitmap.

Now if I make the first variable-length column non-null, watch what happens to the record:

UPDATE vartest SET c2 = 'c2c2c2c2';
DBCC PAGE (test, 1, 152, 1);

Slot 0, Offset 0x60, Length 33, DumpStyle BYTE
Memory Dump @0x6714C060

00000000: 30000800 01000000 0400f802 00190021 †0…………..!
00000010: 00633263 32633263 32633363 33633363 †.c2c2c2c2c3c3c3c
00000020: 33†††††††††††††††††††††††††††††††††††3

The null bitmap has changed from 0xfa to 0xf8 to reflect the newly non-null column. The variable-length portion is re-ordered so that the newly non-null column is in its correct place (you can see the c2c2c2c2c2 comes before the c3c3c3c3 in the hex dump of the record) and the offsets have been updated accordingly. The array still only hold two values though. Now if I set the last column to be non-null:

UPDATE vartest SET c4 = 'c4c4c4c4';
DBCC PAGE (test, 1, 152, 1);

Slot 0, Offset 0x81, Length 43, DumpStyle BYTE
Memory Dump @0x66FCC081

00000000: 30000800 01000000 0400f003 001b0023 †0…………..#
00000010: 002b0063 32633263 32633263 33633363 †.+.c2c2c2c2c3c3c
00000020: 33633363 34633463 346334†††††††††††††3c3c4c4c4c4

The null bitmap now indicates all 4 columns are non-null. The count of columns in the variable-length column offset array has increased to 3 and the new entry is added on the end, as it is the last variable-length column.

You can conceptually think of the algorithm to obtain a variable-length column value as merging the relevant parts of the null bitmap with the variable-length column offset array, and then returning the value if it's non-null.

Hope this explains things!

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 the contents of a data or log file sector with no regard for what is being stored there. Possible causes of physical corruption are:

  • Problem with the I/O subsystem (99.8% of all cases I’ve ever seen – only 3 nines as I’d estimate I’ve seen around about a thousand corruption cases). Remember the I/O subsystem is everything underneath SQL Server in the I/O stack – including the OS, 3rd-party file system filter drivers, device drivers, RAID controllers, SAN controllers, network hardware, drives themselves, and so on. Millions of lines of code and lots of moving parts spinning very fast, very close to very fragile pieces of metal oxide (I once heard Jim Gray liken a disk drive head to a 747 jumbo jet flying at 500 mph at a height of 1/4 inch from the ground…)
  • Problem with the host machine hardware (0.1% of cases). Most of the time this is a memory error.
  • SQL Server bugs (0.1% of cases). Yes, there have been corruption bugs. Every piece of software has bugs. There are KB articles describing bugs.
  • Deliberate introduction of corruption using a hex editor or other means.

Physical corruption is what DBCC CHECKDB usually reports and the majority of cases are caused by a physical failures of some kind, with the minority caused by humans – software bugs.

Logical corruption

This is where something has altered some data so that a data relationship is broken. Possible causes of logical corruption are:

  • Humans

:-) Okay…

  • Application bug. The application deletes one part of an inherent data relationship but not the other. Or the application designer doesn’t implement a constraint properly. Or the application designer doesn’t cope with a transaction roll-back properly. You get the idea.
  • Accidental update/delete. Someone deletes or updates some data incorrectly.
  • SQL Server bug. See above.
  • DBCC CHECKDB when using the REPAIR_ALLOW_DATA_LOSS option. As is documented in Books Online, and I’ve blogged about and mentioned when lecturing, if you run repair, it doesn’t take into account any inherent or explicit constraints on the data.

The point here is that a physical failure of a component does not cause logical corruption, it causes physical corruption. Conversely, application errors cause logical corruption, not physical corruption. DBCC CHECKDB errors are about physical corruption (okay, with the inclusion of DBCC CHECKCATALOG code in 2005, it will find cases where the DBA has manually altered the system tables, causing logical corruption) and applications cannot cause physical corruption as they can only manipulate data through SQL Server. If an application hits a SQL Server bug which causes physical corruption, that’s still not the application causing physical corruption, it’s SQL Server.

So – on to the myths.

  • Can an application cause physical corruption? No.
  • Can stopping a shrink operation cause corruption of any kind? No.
  • Can stopping an index rebuild cause corruption of any kind? No.
  • Can running DBCC CHECKDB without repair cause corruption of any kind? No.
  • Can creating a database snapshot cause corruption of any kind? No.

Hope this helps.