December 29, 2009 at 5:51 am
When migrating databases from SQL Server 2000 to 2005 using backup and restore, How do I verify that auto update statistics is turned on AND it is necessary to update statistics once the restore to 2005 is complete?? Wondering what the best practices for this are???
December 29, 2009 at 6:17 am
Using SP_configure u can find the options set in 2005 sever.
And it is always recommended to use dbcc checkdb after migration.
December 29, 2009 at 7:14 am
We always perform full maintenance after any migration.
i.e.
rebuild all indexes (explicit)
exec sp_updatestats or UPDATE STATISTICS table / view WITH FULLSCAN
dbcc updateusage(0) with count_rows
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 29, 2009 at 7:22 am
After upgrading:
Set the page verify option to CheckSum
Run DBCC UpdateUsage
Update all statistics WITH FULLSCAN
Rebuild all indexes (writes the checksum on all index pages)
Run a DBCC CheckDB with the Data_Purity option
Set the db into compatibility mode 90 (unless you have a good reason not to do so)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 29, 2009 at 7:46 am
Thank you all!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply