Cross-Version Database Attachments, DBCC UPDATEUSAGE, and UPDATE STATISTICS

One of the great things about working at Microsoft is one’s ability to eavesdrop on interesting conversations among smart, knowledgeable people via our technically focused internal discussion groups. Material adapted from these conversations has been posted here on more than one occasion, and so it is again this evening thanks to the kind consent of Sunil Agarwal and Campbell Fraser.

SQL Server users are frequently interested in porting databases from one platform to another via either sp_attach_db (deprecated in SQL Server 2005) or CREATE DATABASE.. FOR ATTACH. With database backups from SQL Server 2000 and earlier, there was a known issue with page and rowcount data in catalog views occasionally being inaccurate. The workaround for this issue is to run DBCC UPDATEUSAGE and update table statistics after the database is attached to the new SQL Server 2005 or SQL Server 2008 platform.

SQL Server 2005 databases don’t suffer from this limitation. So, if you’re attaching a SQL Server 2005 database to a SQL Server 2008 instance, there’s no need to run DBCC UPDATEUSAGE, or to update table statistics, after the attach operation is complete.

Thanks to Sunil and Campbell for sharing this information and consenting to its publication here..

     -wp