InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 19:59:44 GMT20http://www.sqlservercentral.com/Forums/Topic1408306-146-1.aspxTEST, TEST, TEST... hope you have a test environment and just aren't upgrading production.Biggest things are:Rebuild Indexes so optimizer sees the indexesUpdate Stats 100%CHECK Integrity, this makes sure there is no corruptionFri, 18 Jan 2013 06:12:33 GMTMarkushttp://www.sqlservercentral.com/Forums/Topic1408306-146-1.aspx-- set compatibility level-- change dbowner to same as it was under SQL 2000, it is usually 'sa', but check-- update all statistics, needed for performance, SQL 2005 interprets these differently-- update usage stats (correct sp_spaceused values) can be incorrect after SQL 2005 upgradedbcc updateusage(0)-- ensure torn page detection set. alter database dbname set page_verify checksum -- upgrade creates a schema for all users and sets this as default, use following to create SQL to-- modify users default schema to dbo and then run in a new window-- if SQL 2000 objects were not owned by dbo modify script accordingly-- SQL2000 always checks objects by username.object first, then checks dbo.object if not found,-- so this check can be avoided and improve performance by setting default_schema to dbo so it is checked first,-- however if all objects are owned by the user or all or some are owned by user and object not qualified with-- owner in SQL then default_schema should be left at user. Only testing may prove this.set nocount onselect 'alter user '+ name+ ' with default_schema = dbo' from sys.sysusers where uid > 4 and isntgroup = 0 and issqlrole = 0 and isapprole = 0order by name-- now drop all the user schemas createdselect 'drop schema ['+ name+ '] ' from sys.sysusers where uid > 4 and issqlrole = 0 and isapprole = 0order by name-- if not done before backup used in migration, check database integrity, if comes up clean, data_purity will be enabled for all future checkdbsdbcc checkdb with data_purity-- if database reasonable size, take this opportunity to reindex it (run dbcc dbreindex maint plan job)-- if a large no of ad-hoc queries run on this database, consider forced parameterization option-- could reduce cpu usage significantly--alter database dbname set parameterization forced-- now back the database upThu, 17 Jan 2013 07:18:18 GMTgeorge sibbaldhttp://www.sqlservercentral.com/Forums/Topic1408306-146-1.aspxDo we need to run Data Purity check?Thu, 17 Jan 2013 04:20:32 GMTbalasach82http://www.sqlservercentral.com/Forums/Topic1408306-146-1.aspxDBCC UPDATEUSAGEThu, 17 Jan 2013 04:03:08 GMTSuresh B.http://www.sqlservercentral.com/Forums/Topic1408306-146-1.aspxFor SQL 2000 to 2005 Inplace Upgrade, after upgrade, what should we do as next steps. Some arerun checkdbchange compatility backup dbThu, 17 Jan 2013 04:00:06 GMTbalasach82