Rebuilding all indexes on all databases after SQL 2000 to 2005 migration.

Question

Rebuilding all indexes on all databases after SQL 2000 to 2005 migration.

armintucakovic

Old Hand

Points: 338
More actions
August 25, 2008 at 10:18 am

#123195

I am working on a plan to migrate all the databases from SQL 2000 to 2005 using the copy db wizard.
After the migration as sugested I need to update ( or in my case since its better option rebuild ) all the indexes.
As I understand it rebuilding indexes will update stats automaticly equal to full scan and so I dont have to do that? correct?
What I am looking for is a way to rebuild all indexes on the server (all users databases, all indexes ) using a script.
I am not concerned with down time as much as doing it right and making sure the performance and everyhting else is good afterwards.
I am fairly new DBA, read many books on SQL but still didnt get into T-SQL (just starting)
I must have seen tons of different statements COMPLETLY yet all doing same thing?
Does anyone have a good script or command I can run to rebuild all the indexes after the migration before puting the server online? I would rather not do it database by database if posible as there is 20+ of them. I want to make sure the indexes and stats are good after migration and performance is not hit in anyways. AGAIN I WOULD RATHER HAVE DOWN TIME durring migration that latter when $hit hits the fan!
Thanks for your help!
P.S. If there is anything else I should do post-migration by all means share with me. Very open to suggestions.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

armintucakovic (8/25/2008)
As I understand it rebuilding indexes will update stats automaticly equal to full scan and so I dont have to do that? correct?

Correct. There are a number of scripts in the script library here to rebuild all indexes. Otherwise a simple cursor over sys.indexes with an ALTER INDEX ... REBUILD should do the job

P.S. If there is anything else I should do post-migration by all means share with me. Very open to suggestions.

Run a CheckDB. CheckDB in 2005 checks more things that it did in 2000, so it;s a good idea to make sure you don't have any lingering issues. Also people will suggest running DBCC updateusage, as some of the dpace-usage metadata could be wrong. SQL 2000 was 'lazy' about that sometimes.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ola Hallengren Hall of Fame Points: 3970 More actions · Answer 2

I have a stored procedure that could help you with this. The stored procedure has been designed to only rebuild or reorganize the indexes with fragmentation, but it it can also be used to rebuild all indexes.

http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

EXECUTE dbo.IndexOptimize @databases = 'USER_DATABASES',

@FragmentationHigh_LOB = 'INDEX_REBUILD_OFFLINE',

@FragmentationHigh_NonLOB = 'INDEX_REBUILD_OFFLINE',

@FragmentationMedium_LOB = 'INDEX_REBUILD_OFFLINE',

@FragmentationMedium_NonLOB = 'INDEX_REBUILD_OFFLINE',

@FragmentationLow_LOB = 'INDEX_REBUILD_OFFLINE',

@FragmentationLow_NonLOB = 'INDEX_REBUILD_OFFLINE'

Another thing that I think is important is to do a DBCC CHECKDB with the DATA_PURITY option on all databases after the migration. I have seen numerous databases where DBCC CHECKDB has run fine on SQL Server 2000, but not on SQL Server 2005 (because of the more thorough checks).

Ola Hallengren

http://ola.hallengren.com

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 3

I almost hate posting this but.... After you are done with all your maintenance, indexes, dbcc, make sure you take a backup before you release the converted databases to your users. A nice point to go back to post conversion is always handy. 😀

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

armintucakovic Old Hand Points: 338 More actions · Answer 4

DavidB (8/25/2008)
I almost hate posting this but.... After you are done with all your maintenance, indexes, dbcc, make sure you take a backup before you release the converted databases to your users. A nice point to go back to post conversion is always handy. 😀

haha, i can see why you would be hesitant to post it but i have to agree with you. Smart thing to do. Do all your work and then something goes wrong and back to square one... 🙂 ... defenitly not a bad idea to back it up after everyhting 😛

armintucakovic Old Hand Points: 338 More actions · Answer 5

Ola Hallengren (8/25/2008)
I have a stored procedure that could help you with this. The stored procedure has been designed to only rebuild or reorganize the indexes with fragmentation, but it it can also be used to rebuild all indexes.
http://blog.ola.hallengren.com/blog/_archives/2008/1/1/3440068.html
EXECUTE dbo.IndexOptimize @databases = 'USER_DATABASES',
@FragmentationHigh_LOB = 'INDEX_REBUILD_OFFLINE',
@FragmentationHigh_NonLOB = 'INDEX_REBUILD_OFFLINE',
@FragmentationMedium_LOB = 'INDEX_REBUILD_OFFLINE',
@FragmentationMedium_NonLOB = 'INDEX_REBUILD_OFFLINE',
@FragmentationLow_LOB = 'INDEX_REBUILD_OFFLINE',
@FragmentationLow_NonLOB = 'INDEX_REBUILD_OFFLINE'
Another thing that I think is important is to do a DBCC CHECKDB with the DATA_PURITY option on all databases after the migration. I have seen numerous databases where DBCC CHECKDB has run fine on SQL Server 2000, but not on SQL Server 2005 (because of the more thorough checks). I have a stored procedure that you could use for this.
http://blog.ola.hallengren.com/blog/_archives/2008/1/1/3440068.html
Ola Hallengren
http://ola.hallengren.com

Hey I was looking at that link and on the bottom there is broken into sections scripts.

http://blog.ola.hallengren.com/blog/_archives/2008/1/1/3440068.html

CommandExecute.sql (8KB)

DatabaseBackup.sql (26KB)

DatabaseIntegrityCheck.sql (10KB)

DatabaseSelect.sql (6KB)

IndexOptimize.sql (44KB)

Documentation.html (21KB)

MaintenancePlans.html (11KB)

MaintenanceSolution.sql (116KB)

Can I run the

DatabaseIntegrityCheck.sql and

IndexOptimize.sql

after the migration. Would these two perform the functions I need?? If so does the order i run them in matter??

Also do I have to modify the script in any way, and will this run all the databases?

I ask because as mentioned earlier I cant read T-SQL good enough to know exatclyu what its doing

Ola Hallengren Hall of Fame Points: 3970 More actions · Answer 6

You could use MaintenanceSolution.sql that creates all objects.

You could also just create the objects that you need. That would be DatabaseIntegrityCheck.sql, IndexOptimize.sql, CommandExecute.sql and DatabaseSelect.sql.

Then you just execute DatabaseIntegrityCheck and IndexOptimize like this.

EXECUTE dbo.DatabaseIntegrityCheck @databases = 'USER_DATABASES'