Best way to reindex/rebuild on multiple dbs?

Question

Post reply

Best way to reindex/rebuild on multiple dbs?

JuncTheMayl

Hall of Fame

Points: 3912
More actions
September 18, 2008 at 10:11 am

#382724

i need to rebuild certain indexes and recompile certain stored procs as part of a recommended db maint plan for a vendor's database. the script they gave me works, but it only works against the current db. for example:
declare @table varchar(255)
declare curCursor cursor for
select name from sysobjects
open curCursor
fetch next from curCursor into @table
while @@fetch_status = 0
begin
exec ('DBCC REINDEX ([' + @table + '], '''', 0')
fetch next from curCursor into @table
end
close curCursor
deallocate curCursor
i have multiple databases (live, test, other companies, etc) and i need to modify the script to allow for that. i tried wrapping another cursor around the current script to get a list of database names, then execute 'USE DBNAME', but while it works, the context is switched 'inside' the exec statement's context; when the DBCC REINDEX part of the script runs, it runs against whatever database the entire script was run in (typically master).
any advice is appreciated... i hope i'm just overlooking something small.
lenny

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

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

Jason Wisdom-380639 SSCrazy Points: 2083 More actions · Answer 1

Create a new Maintenance Plan on each server.

Use the Rebuild Index feature.

You get free logging thrown in.

Very easy to set up.

JuncTheMayl Hall of Fame Points: 3912 More actions · Answer 2

there's only 1 server... multiple databases... even so, i don't want 1 rebuild task per database because:

1. they could create a new database without telling me

2. i don't want to have to create a new task if they DID tell me... it would get REALLY messy really fast

Jason Wisdom-380639 SSCrazy Points: 2083 More actions · Answer 3

There is an option to select "All Databases" on a server.

So, if new databases are added later, your Rebuild Indexes task will automatically cover it.

JuncTheMayl Hall of Fame Points: 3912 More actions · Answer 4

but i don't want to rebuild ALL indexes in all databases, just certain indexes in certain databases. i know, i know, but those are the rules i have to play by.

JuncTheMayl Hall of Fame Points: 3912 More actions · Answer 5

so i've come up with a script to do what i want... not the prettiest thing in the world, but hey, it works. my next challenge is error trapping. the DBCC DBREINDEX command sends its output to the messages tab at the bottom of the query window. is there any way to capture that information? or is there any way to trap an error on an EXEC statement ("EXEC ('DBCC DBREINDEX(table, '', 0)')")?

or put another way, if you had something like:

EXEC ('use database;dbcc dbreindex(table, '''', 0);')

if all goes well, you'll have "DBCC execution completed. If DBCC printed error messages, contact your system administrator." in the messages window. if something goes wrong, how would you capture that?

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 6

You should use ALTER INDEX with 2005 and above as DBCC REINDEX is deprecated.

I'm not sure what you can grab here, though checking @@ERROR and related variables after the command will help.

JuncTheMayl Hall of Fame Points: 3912 More actions · Answer 7

i couldn't get the @@ERROR thing to work... once the EXEC statement failed, the entire batch stopped. but one idea leads to another... try/catch DID work.

and i'm with you on the ALTER thing, but these are the recommended procedures supplied by our vendor, so i have to go with that for now...