May 14, 2008 at 10:12 am
we have a Database in 2000 and accessing it using Management Studio.
I detached this DB and attached to a Test Server.
I changed the Compatibility Level from 80 to 90.
Can anyone please tell me what are the validations for this scenario.
What are the others thing that i need to look for for my DB to perform well.
Aspirant DBA
May 14, 2008 at 10:32 am
After changing the compatibility level to 90, do the following:
(1) At the very least run sp_updatestats on all user databases, OR preferably run:
UPDATE STATISTICS WITH FULLSCAN on all user databases.
(2) DBCC UPDATEUSAGE (0); (on all user databases)
(this will correct any rowcount inconsistencies caused by the upgrade
In addition to these steps, consider switching the page_verify option of each migrated database from TORN_PAGE_DETECTION to CHECKSUM. While switching to CHECKSUM mode will not boost performance (it may actually slightly inhibit it in fact), it is the default mode for checking db-page integrity in SQL Server 2005, and can help detect some hardware failures that TORN_PAGE_DETECTION cannot:
ALTER DATABASE yourdb
SET PAGE_VERIFY CHECKSUM;
See "Pro SQL Server 2005" by Thomas Rizzo et al. for more info
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 14, 2008 at 10:38 am
Thanks for your reply.
But at present there is only one Database in this server.
May 14, 2008 at 10:41 am
At the very least, you must update the stats.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 14, 2008 at 10:52 am
I had Executed both the Queries against the User DB
sp_updatestats
-DBCC UPDATEUSAGE (0);
The following was part of Result.
DBCC UPDATEUSAGE: counts updated for table 'ConsumerOrdersMivaDetail' (index 'ConsumerOrdersMivaDetail', partition 1):
DATA pages (In-row Data): changed from (2168) to (2170) pages.
USED pages (In-row Data): changed from (2200) to (2194) pages.
RSVD pages (In-row Data): changed from (5885) to (6248) pages.
May 14, 2008 at 10:55 am
Hey Friend,
Do i need to do anything else apart from those mentioned.
What could be Validations for this upgradation from 80 to 90 for testing.
May 14, 2008 at 11:03 am
The only extra thing I can think of right now is tempdb usage. In SQL 2005 tempdb usage is greatly increased. Make sure tempdb in the new SQL 2005 instance is properly sized for your typical workload.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 14, 2008 at 11:04 am
You should run all queries that you expect to see if they work correctly. Execute all stored procedures, and grab queries or a load from Profiler on the existing server and replay it to see if there are any problems.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 14, 2008 at 11:12 am
Thanks for your reply. I'll get back to you if any issues further.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply