Rebuilding indexes on all DB with isp_AlterIndex

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Rebuilding indexes on all DB with isp_AlterIndex

Author

Topic

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2009-02-02 : 06:39:16

Hi,

Can anyone tell me if Tara's script, isp_AlterIndex, can be used on more than one database or generally if there is a simple method to use "Alter Index" on more than database/all user databases etc? The maint plans allow for this. I've never had a problem with maint plans, I disagree with most by thinking they're absolutely great. Unfortunately though, using scripts will be the only way of keeping the monkeys in my organisation from making changings to my plans.

Thanks

Drew
---------------------
"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK."

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2009-02-03 : 03:55:51

Do all that use scripts have an indexing job for every database?

Drew
---------------------
"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK."

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-02-03 : 04:03:22

I run the reindexing script as a job and have a separate job step for each database. I think you can also use the undocumented ms_foreachdb (google it for usage) but I would prefer doing it manually...

- Lumbago

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2009-02-03 : 04:07:35

We have so many databases on any one server. I don't think ms_foreachdb can be used with Alter Index (unlike DBCC).

Drew
---------------------
"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK."

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-02-03 : 05:24:05

Then I guess a cursor can be of use:


DECLARE 
  @DBname sysname,
  @Cur cursor

SET @Cur = CURSOR FOR SELECT [name] FROM sys.databases WHERE ismsshipped = 0
OPEN @Cur
FETCH NEXT FROM @Cur INTO @DBname
WHILE (@@FETCH_STATUS == 0)
  BEGIN
    EXEC isp_AlterIndex @DBName, ...

    FETCH NEXT FROM @Cur INTO @DBname
  END
CLOSE @Cur
DEALLOCATE @Cur

I'm a bit rusty on the cursor syntax and don't have sql server available now for testing but it shhouldn't be too far off..

- Lumbago

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-03 : 09:06:19

quote:
Originally posted by drewsalem

We have so many databases on any one server. I don't think ms_foreachdb can be used with Alter Index (unlike DBCC).

Drew
---------------------
"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK."

Yes you can use Ms_foreachdb as well.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources