after migrate database

• sqlfriend

  SSC Guru

  Points: 52781

  More actions

  May 23, 2012 at 1:44 pm

  #392330

  After migrate databases from SQL server 2005 to a new server with 2008 R2, is it a required steps to run the following:

  1. check database integrity task

  2. Update statistics.

  3. rebuild index.

  or 3 is not necessary?

  Thanks

• Orlando Colamatteo

  SSC Guru

  Points: 182279

  More actions

  May 23, 2012 at 1:55 pm

  #1492104

  None are technically required. All three are a good idea. I would do 3 before 2 and would use sp_updatestats or UPDATE STATISTICS WITH COLUMNS on all tables.

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato

• sqlfriend

  SSC Guru

  Points: 52781

  More actions

  May 23, 2012 at 2:14 pm

  #1492112

  Thanks, I know this is good to do all, what I am concerned is:

  we are going to migrate databases to new server, we want to pull off the current production server and replace with new server, and on the new server, before doing the swap, I need time to do the restore of most recent databases, but if I run the 3 steps it takes about total 5 hours.

  We can not afford so much time for this purpose for a production environment.

  So I kind of hesitate to do that.

  Can we run it maybe a week after the server swap?

  Thanks,

• SGT_squeequal

  SSCertifiable

  Points: 7167

  More actions

  May 23, 2012 at 2:14 pm

  #1492113

  Also if you want to make full use of the new functionality in SQL2008 you will need to update your database comparability level to 100, then do a complete test of your application.

  ***The first step is always the hardest *******

• sqlfriend

  SSC Guru

  Points: 52781

  More actions

  May 23, 2012 at 2:21 pm

  #1492122

  SGT_squeequal (5/23/2012)

  ---

  Also if you want to make full use of the new functionality in SQL2008 you will need to update your database comparability level to 100, then do a complete test of your application.

  Thanks, this one we have taken into our plan.

• Orlando Colamatteo

  SSC Guru

  Points: 182279

  More actions

  May 23, 2012 at 2:24 pm

  #1492126

  sqlfriends (5/23/2012)

  ---

  Can we run [index rebuild and checkdb] maybe a week after the server swap?

  Sure. If you have to pick and choose then skip running checkdb and skip rebuilding all indexes until your first maintenance window on the new system...but run them as soon as possible.

  Very important not to skip is to run dbcc updateusage (I didn't see you mention that), make sure the page_verify option is set to checksum and if skipping index rebuilds update all statistics with fullscan. It is optional pending your code being ready for it, but make sure you at least consider setting the compat mode to 100.

  USE master

  GO

  ALTER DATABASE [dbname] SET PAGE_VERIFY CHECKSUM;

  GO

  DBCC UPDATEUSAGE(dbname);

  GO

  EXEC [dbname].sys.sp_MSforeachtable

  @command1 = N'UPDATE STATISTICS ? WITH FULLSCAN;' ;

  GO

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato

• sqlfriend

  SSC Guru

  Points: 52781

  More actions

  May 23, 2012 at 2:43 pm

  #1492143

  Thanks, does udpateUsage need to be run after I changed the database compatiblity from 2005 to 2008?

  I see it uses statistics to calculate the counts, does it mean I have to run update statistics first which will take long?

  Thanks

• Orlando Colamatteo

  SSC Guru

  Points: 182279

  More actions

  May 23, 2012 at 2:59 pm

  #1492150

  sqlfriends (5/23/2012)

  ---

  Thanks, does udpateUsage need to be run after I changed the database compatiblity from 2005 to 2008?

  No, compat mode is not a concern for udpateUsage.

  I see it uses statistics to calculate the counts, does it mean I have to run update statistics first which will take long?

  No, different type of statistics.

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato

• sqlfriend

  SSC Guru

  Points: 52781

  More actions

  May 23, 2012 at 3:30 pm

  #1492169

  Thanks, so when in what situations we should run update usage?

• Orlando Colamatteo

  SSC Guru

  Points: 182279

  More actions

  May 23, 2012 at 3:37 pm

  #1492171

  sqlfriends (5/23/2012)

  ---

  Thanks, so when in what situations we should run update usage?

  Read the Remarks section here: DBCC UPDATEUSAGE

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato

• sqlfriend

  SSC Guru

  Points: 52781

  More actions

  May 23, 2012 at 3:53 pm

  #1492178

  Thanks.