다음을 통해 공유

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

Comments

  • Anonymous
    January 01, 2003
    Kyle Schoonover, a former colleague in MSIT, posed the following questions in a comment on October’s

  • Anonymous
    October 24, 2008
    Ward, I'm currently working in MySQL and writing conversion scripts to convert a MySQL database to a SQL 2005 database.  I have also been using OpenQuery to query through a linked sterver from SQL 2005 to MySQL.  I was wondering if you could comment on a way to possibly use DTS to transfer data from a database that is not readily supported like MySQL. Also is there something better than using OpenQuery to create dynamic SQL to run against a linked server database that runs on MySQL. Also, I hope you are liking Florida.  I hope to catch you for lunch sometime with Carl if you make it back here.