Adventures with DBCC and Database Upgrades

We have (had?) an application that has been retired (Hurrah!).  However, even though it has been retired, there is still the occasional request to extract data from this database, which was hosted on a SQL2000 SP3a server.  So what I’m doing is migrating the database up to a SQL 2008 R2 instance so that I can then develop a suitable SSRS report against it away from the main production environment.

Additional notes: the databases in question total around 33GB of data, 9 GB of TLogs.  We’re using Quest Software‘s LiteSpeed for SQL Server, and as a result, “only” have 6GB of compressed database backups to move across the networks.  Just as well, as even that took the best part of an hour to shift from our production data center into our non-production data center.

Step 1 – SQL 2000 to SQL 2005

I took the backups from the SQL 2000 system and restored them into a SQL 2005 instance.  So far, no problem (barring a little aggravation trying to find the password with which the backups had been created…  That’s now been put into the password safe!)

For the remainder of the work, given that we’re migrating into what’s really a development environment, I’m going to change the databases so that they are in SIMPLE recovery mode:

ALTER DATABASE <DBName> SET RECOVERY SIMPLE

As you can see from the MSDN documentation for DBCC UPDATEUSAGE:

Reports and corrects pages and row count inaccuracies in the catalog views. These inaccuracies may cause incorrect space usage reports returned by the sp_spaceused system stored procedure. In SQL Server 2005, these values are always maintained correctly. Databases created on SQL Server 2005 should never experience incorrect counts, however, databases upgraded to SQL Server 2005 may contain invalid counts. We recommend running DBCC UPDATEUSAGE after upgrading to SQL Server 2005 to correct any invalid counts.

However, before doing this, I decided it might be an idea to run DBCC CheckDB – this, as you should all know by now, combines DBCC CheckAlloc, DBCC CheckTable and DBCC CheckCatalog with a couple of other checks. This is something that’s done in our production SQL Server 2000 SP3a environment every weekend, and hasn’t been throwing any errors. Unfortunately, though, SQL Server 2005’s version of DBCC had other ideas about what was acceptable, and gave me the following:

Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3853, State 1: Attribute (referenced_major_id=1242499933,referenced_minor_id=136) of row (class=0,object_id=356131200,column_id=0,referenced_major_id=1242499933,referenced_minor_id=136) in sys.sql_dependencies does not have a matching row (object_id=1242499933,column_id=136) in sys.columns

This is not what I wanted to see. Fortunately, it occurred high up the output list, and was in red, so easy to see.

MSDN documentation for SQL Server Message 8992 says that this is due to CHECKCATALOG finding errors. Fortunately for me, this time it only affected sys.sql_dependencies, which implied that we might be able to fix this relatively simply.

Running the following query, lifted from someone else’s experiences of this, returns a list of affected objects:

SELECT object_id, name, type_desc FROM sys.objects
WHERE object_id IN (356131200, 1242499933)

The IDs used are those from the error message.

This identified a Stored Procedure and a View. Recreating the Stored Procedure fixed the problem, as it reset the dependencies. I re-ran DBCC CheckDB, and it came back clean. I was lucky this time. If this hadn’t worked, or had occurred elsewhere, then the fix wouldn’t have been so straightforward – it might have involved manually hacking the system objects, or recreating more complex objects and data structures.

Why does this happen? Well, in this discussion of someone else’s experience of this problem on the SQL Team forum, Paul “Mr DBCC” Randal writes:

…My guess as to the cause is that someone manually changed a system table in 2000 and didn’t remove the entries from syscolumns.

He goes on to provide a handy link to an article he wrote about, erm, manually changing system tables in order to fix things.

Next, as recommended by MS, I ran DBCC UpdateUsage ('<DBName>') against the databases, with no problems.

My colleague at this point also recommends rebuilding all indexes at this point. Also, just because I’m feeling particularly paranoid, and again at his suggestion, I reran

DBCC CheckDB ('<DBName>') WITH DATA_PURITY

Finally, I shrank the Transaction Log segments of the databases – there’s no point in leaving them at their current over-inflated sizes for this.

Step 2 – SQL 2005 to SQL 2008 R2

First thing to do is, once again, backup the databases. This time, however, I’m hamstrung by the fact that I don’t have Quest’s Litespeed fully licensed in the non-production environment – it’s there in “restore only” mode, so the database backups total around 29GB. Fortunately, the SQL 2008 R2 server is in the same data center and the same virtualised environment as the SQL 2005 server, so copying the files this time only took ten minutes.

And so we restore from these backups into the SQL 2008 R2 server. This time, after the usual “10 percent processed…” messages, I see a whole heap of new messages:

Converting database '<DBName>' from version 611 to the current version 661.
Database '<DBName>' running the upgrade step from version 611 to version 621.
Database '<DBName>' running the upgrade step from version 621 to version 622.
...
Database '<DBName>' running the upgrade step from version 660 to version 661.
RESTORE DATABASE successfully processed 1654033 pages in 146.420 seconds (88.253 MB/sec).

Now what’s all that about? I’m guessing that what’s happening is that the various versions of SQL Server have made alterations to the storage engine, and these in turn have made alterations to the layout of data within the database device. For more information on this, see Jonathan Kehayias’s blog post which touches on the subject, and then read the first Q&A in Paul Randal’s TechNet Magazine “SQL Q&A” article from August 2008.

Re-run the various DBCC things mentioned above, just in case, and job’s a good ‘un, I reckon, unless you can think of anything I’ve missed – in which case leave a comment below.

I now have the databases from SQL 2000 running in SQL 2008 R2. They’re currently at compatibility level 80, which is understandable. I’m going to leave it there for the moment, just in case.

Now all I have to do is the actual work – y’know, creating users and the SSRS report(s) needed.

Advertisements
This entry was posted in SQLServerPedia Syndication and tagged , , , , . Bookmark the permalink.

3 Responses to Adventures with DBCC and Database Upgrades

  1. Perry Whittle says:

    That’s what happens when some monkey hacks the system tables in a SQL Server 2000 system, grrrr.
    lol

  2. Pingback: Non-Production Servers – SIMPLE recovery? | The Lone DBA

  3. Hi!
    Interesting story.
    One question: Why not migrating the 2000 database directly to 2008 R2?
    Thanks!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s