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 CHECKDB because I changed DBCC CHECKDB to use a hidden, automatically-created database snapshot to get a transactionally consistent view of the database from SQL Server 2005 onwards.

If you're seeing this problem, you need to install SQL Server 2005 SP2 CU13, SQL Server 2005 SP3 CU4, or SQL Server 2008/2008 R2.

More details are in KB article 919142, which was only recently made public.

Back in September I blogged about an old 2005 bug that prevented DBCC CHECKFILEGROUP checking the partitions of an object on the specified filegroup unless *all* partitions of the object are on the specified filegroup (not a smart way to set things up!). The bug was fixed ages ago in 2005 but has only just been fixed in 2008. The post which explains the bug in more detail is DBCC CHECKFILEGROUP bug on SQL Server 2008. This is an important feature to be able to split the consistency checks of a partitioned VLDB over a series of days - see CHECKDB From Every Angle: Consistency Checking Options for a VLDB for more details.

SQL Server 2008 SP1 Cumulative Update 6 (which you can get here) has the bug fix for 2008 finally. The KB article which describes the bug is 975991.

You're all running regular consistency checks, right?

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 no more about it until a few weeks ago.

I was settling down to watch a movie with my laptop nearby when I got involved helping fellow-MVP Jonathan Kehayias (blog|twitter). As things progressed it dawned on me that his CHECKDBs were fine using regular database snapshots, but failed using the automatically-created snapshots, which use NTFS alternate streams (see this blog post where I explain about them). The errors produced were as below (borrowed from Jonathan's blog post):

2009-12-10 01:35:44.04 spid75      Error: 17053, Severity: 16, State: 1.
2009-12-10 01:35:44.04 spid75      E:\SQLData\MSSQL.1\DatabaseName.mdf:MSSQL_DBCC16: Operating system error 1784(The supplied user buffer is not valid for the requested operation.) encountered.
2009-12-10 01:35:44.07 spid98      DBCC CHECKDB (DatabaseName) WITH all_errormsgs, no_infomsgs, data_purity executed by Domain\UserName found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 9 seconds.
2009-12-10 01:35:44.45 spid98      Error: 926, Severity: 21, State: 6.
2009-12-10 01:35:44.45 spid98      Database 'DatabaseName' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information. 

Lo-and-behold, it turned out to be a Diskeeper 10 filter driver that had a bug in it. You can read Jonathan's more in-depth recounting of the story on his blog here.

Diskeeper issued a fix a couple of weeks back, and Microsoft Product Support also put out a blog post with some details. Links are:

• http://www.diskeeper.com/blog/post/2009/12/28/New-Diskeeper-2010-update-%28140898%29.aspx (note there's a newer version even than this one, at time of writing)
• http://blogs.msdn.com/psssql/archive/2010/01/08/dbcc-check-commands-encounter-problems-and-reports-errors.aspx

Hope this doesn't happen to you!

This is an interesting performance bug concerning a broken query optimizer rule in 2008. Thanks to Dan Shargel (Twitter) for sending me info on this and letting me use some of the stuff he sent.

The scenario involves using MIN or MAX in a query against a partitioned view. In 2005 the query plan includes a TOP (1) operator which uses the right index, but in 2008 the optimizer rule was broken and the plan turned into a stream aggregate, much more expensive in this case.

Here's the 2005 query plan:

and here's the 2008 query plan (before the bug fix):

You can get the fix in CU4 for 2008 SP1 (or later) and read a bit more about it in KB 973255.

Note that you have to turn on trace flag 4199 to enable the fix - that requirement will be removed in SQL11.

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 and I thought it looked like a bug - so I advised them to call PSS, who told them they were the first to report it on 2005 and it was a bug. Cool! (if you see what I mean).

If you run a CHECKDB or a CHECKTABLE after rebuilding an XML index, you'll see 8964 errors like the one below:

DBCC results for 'sys.xml_index_nodes_980198542_32000'.
Msg 8964, Level 16, State 1, Line 2
Table error: Object ID 909246294, index ID 1, partition ID 72057594130530304, alloc unit ID 72057594149535744 (type LOB data). The off-row data node at page (1:621), slot 0, text ID 1302003712 is not referenced.

If the XML index contains XML elements that are larger than 8000 bytes, they will be pushed off-row as row overflow columns, stored in LOB pages. The XML index rebuild isn't cleaning up its LOB pages properly so leaves orphaned ones lying around. Not really nasty corruption, but enough to set off alarm bells in your monitoring.

You can get the CU at http://support.microsoft.com/kb/974648/, and a high-level description of the bug at http://support.microsoft.com/kb/974985.

Another quickie today. If you're using Activity Monitor in SQL Server 2008, or Performance Data Collection with the Server Activity system data collection set, then you may see a constant, and large number of 'Other' SQL Server Waits. When you drill into these, you see that the highest number of waits are for FSAgent. The FSAgent is part of the FILESTREAM subsystem, and it's what fools the rest of the Storage Engine into accepting the FILESTREAM data as if it was real varbinary(max) data. If you look in the Books Online entry for sys.dm_os_wait_stats, which defines all the different wait types, it explains that the wait should only show up when FILESTREAM I/Os are occuring. However, this wait type shows up *all* the time - clearly a bug.

Turns out that it's a known bug that's been fixed - see KB 958942.

Here's an interesting bug that was just recently fixed, and someone actually hit it today, so it's time to blog it.

Bottom line is that if you're doing NOLOCK scans of tables involving LOB data, the perfmance might suck. 2005 SP3 CU3 has the bug fix, but unfortunately the KB article was written by someone who didn't seem to know what they were talking about, so I suggested it was rewritten, which it has been (but not republished yet). The original KB article is at http://support.microsoft.com/kb/961049/. It's also fixed in 2008, but I don't know which build (I believe CU4 at least).

Here's my explanation of the problem.

LOB data can be stored in-row or off-row (my previous LOB post Importance of choosing the right LOB storage technique has more details). When it's stored off-row, it must be accessed by first reading the pointer to the LOB data from the data/index record, and then following the pointer (remember that 'record' is synonymous with 'row'). When a LOB data value is updated, the off-row value is updated first, and then if the off-row link changed, the data/index record is updated with the new link. There's obvioulsy a window here, where someone reading the data/index record might see the wrong/non-existent off-row pointer. This is exactly what can happen when a NOLOCK scan occurs.

To mitigate the possibility of a NOLOCK scan trying to follow a bad off-row link, the old behavior was to scan all the IAM pages (see Inside the Storage Engine: IAM pages, IAM chains, and allocation units) for the table/index to make sure that the off-row link actually pointed to a page allocated to the table/index. If there are lots of IAM pages, this means lots of logical IOs, and poorly performing SELECT queries. And it does the scan once for *every row*. The person that hit it today had a 500 row select of ~20KB per row taking 20 seconds - 10MB of physical IOs and 30MB of logical IOs!

The fix is to make further use of an already existing in-memory cache of IAM pages to do a quick lookup of the right IAM page covering the GAM interval of the LOB page being read, without having to scan the whole IAM chain.

Hope this helps explain things, and track down perf problems for some of you.

Finally! All those customers out there who are frustrated by not being able to turn on the 'lock pages in memory' option to protect the SQL working set on Standard Edition - the wait is nearly over. Bob Ward (Principal Escalation Engineer in CSS, and a good friend of mine) announced at PASS Europe that the option is soon to be supported - in the May CU for SQL 2008 and the June CU for SQL 2005. You can read a little more in his blog entry here.

If you don't know what this is, checkout this entry in Books Online - it basically prevents Windows paging out SQL memory to disk.

[Edit: the bits containing this fix have been released for 2008. See here for details.]

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 the original poster). The problem was that every night the maintenance job would run and end up producing a stack dump, from a 211 error (which always indicates metadata corruption causing the Engine to trip over). I guessed system table corruption but the consistency checking portion of the maintenance job was apparently running fine. So I asked for one of the stack dumps, the output from the maintenance plan, and the latest SQL error log to have a look at (I have a weakness for these things, as you well know).

After having a look, I saw some strange behavior in the error log - every night there 10 or so DBCC CHECKDBs of master within about 2 minutes - corresponding to the consistency checking part of the maintenance job that was clearing running DBCC CHECKDB against all the system and user databases. Then I remembered that vanilla 2005 SP2 has two nasty maintenance plan bugs - one of which caused the consistency checking part of the maintenance plan to malfunction when run, and run DBCC CHECKDB against master every time instead of the database specified. Fixing that would then lead to the being able to properly run consistency checks and find the corrupt database.

So - moral of the story is to make sure you're not running vanilla 2005 SP2 (or one of the builds close to it, 3150 to 3158) otherwise you could get into problems with maintenance plans - worse still, you may think you've been running consistency checks all this time on your user databases, but if no-one's checking the SQL error logs, you could just be repeatedly consistency checking master.

The KB that describes the bug is 934459 (available at http://support.microsoft.com/kb/934459), or you can install SP3 (available at http://support.microsoft.com/?kbid=955706).

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 may happen when you update an out-of-row LOB value in a database with snapshot isolation enabled - described in KB 962209. Either of these problems will result in a 7105 error:

Msg 7105, Level 22, State 11, Line 1
The Database ID Database ID, Page (N:N), slot N for LOB data type node does not exist. This is usually caused by transactions that can read uncommitted data on a data page. Run DBCC CHECKTABLE.

You can get the fixes at 2005 SP3 CU2 and 2005 SP2 CU12.

While trawling through the latest 2008 Books Online this morning to answer a question, I noticed a new section that I hadn't seen before, which explains in detail how to perform a rolling upgrade with database mirroring. The link to the MSDN page is http://msdn.microsoft.com/en-us/library/bb677181.aspx and below I've linked to the flowchart from that page.  

Books Online also has a lot of other "how-to" topics around database mirroring - here are some links:

• 2005 mirroring how-to topics: http://msdn.microsoft.com/en-us/library/ms175059(SQL.90).aspx
• 2008 mirroring deployment how-to topics: http://msdn.microsoft.com/en-us/library/bb510479.aspx
• 2008 mirroring administration how-to topics: http://msdn.microsoft.com/en-us/library/ms175059.aspx
• 2008 mirroring monitoring how-to topics: http://msdn.microsoft.com/en-us/library/cc280423.aspx

Kevin Cox of the SQLCAT team also just blogged about a customer upgrade from 2005 to 2008 and some of the issues they faced. Btw - if you're not subscribed to their blog, you definitely should be - lots of cool stuff.

And now the bug. In 2008 RTM, if your database contains full-text then mirroring will not work when you perform a rolling upgrade. This is explained in KB 956017 (with a trace-flag workaround) and KB 957816, which points at 2008 RTM CU1 that has the fix in.

Cumulative Update 1 for SQL 2008 RTM contains fixes for two nasty FILESTREAM bugs (among a lot of other bug fixes).

The first one concerns restoring a 2008 database from a series of log backups when the database contains FILESTREAM info. It's possible that a race condition can cause one of the log backups to miss backing up a FILESTREAM file - resulting in a corrupt database after a restore operation. See KB 957809 for more details.

The second bug occurs when a clustered index is rebuilt using ALTER INDEX ... REBUILD and the table contains FILESTREAM data. In this case, it's possible that all the FILESTREAM files are copied, meaning the rebuild operation can take a very long time if there's lots of large FILESTREAM data values in the table. There's no need for the FILESTREAM data to be copied during an index rebuild as the FILESTREAM locations and filenames remain the same. See KB 957823 for more details.

You can get the fixes for these in CU1 - see KB 956717 for the download (right at the top of the page) and the list of all other fixes included in the update.