DBREINDEX - SQL Server Forums

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 2000 Forums

SQL Server Administration (2000)

DBREINDEX

Author

Topic

amy
Starting Member

30 Posts

Posted - 2002-05-01 : 11:41:39

What is the best way to do the re-indexes all tables for my database 1 or 2?

1) exec sp_MSforeachtable @command1="dbcc dbreindex ('?') WITH NO_INFOMSGS"

2)DBCC DBREINDEX ('dbo.tablename', '', 70)

thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-01 : 11:45:51

The first one.

amy
Starting Member

30 Posts

Posted - 2002-05-01 : 11:57:22

Can you explain please, what is the other advantages for the first one and disadvantage for the seconde one?

quote:

The first one.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-01 : 13:07:57

Well, you want ALL of your tables to be reindexed, the sp_MSforeachtable procedure will do that for you. Otherwise you'd have to manually run DBREINDEX for each table explicitly, or write a stored procedure that has a DBCC DBREINDEX command for each table in your database:

DBCC DBREINDEX('TableA', '')
DBCC DBREINDEX('TableB', '')
DBCC DBREINDEX('TableC', '')
DBCC DBREINDEX('TableD', '')

It's not a matter of advantage/disadvantage, both methods work. There is one advantage to using the 2nd method, and that is it won't DBREINDEX a table unless you list that table. Let's say you DIDN'T want to reindex TableD, you could simply remove that line of code. sp_MSforeachtable will do every table regardless.

amy
Starting Member

30 Posts

Posted - 2002-05-01 : 13:41:08

Thanks for your respond

quote:

Well, you want ALL of your tables to be reindexed, the sp_MSforeachtable procedure will do that for you. Otherwise you'd have to manually run DBREINDEX for each table explicitly, or write a stored procedure that has a DBCC DBREINDEX command for each table in your database:

DBCC DBREINDEX('TableA', '')
DBCC DBREINDEX('TableB', '')
DBCC DBREINDEX('TableC', '')
DBCC DBREINDEX('TableD', '')

It's not a matter of advantage/disadvantage, both methods work. There is one advantage to using the 2nd method, and that is it won't DBREINDEX a table unless you list that table. Let's say you DIDN'T want to reindex TableD, you could simply remove that line of code. sp_MSforeachtable will do every table regardless.

amy
Starting Member

30 Posts

Posted - 2002-05-02 : 09:40:31

If you choose the first method then how can you specify a fillfactor value?

Thanks

quote:

The first one.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-02 : 09:51:13

Just add the fillfactor to the command:

exec sp_MSforeachtable @command1='dbcc dbreindex (?, ''''', 70) WITH NO_INFOMSGS'

If you want to preserve the original fillfactor, use 0.

amy
Starting Member

30 Posts

Posted - 2002-05-02 : 10:22:47

Thanks alots

One more question:

After I re-index these tables, how can I confirm the work I did has been successfully worked or not even I got the message"DBCC execution completed" but what the other place for me to check to see the indexes has been rebuild successfully?
Thanks

quote:

Just add the fillfactor to the command:

exec sp_MSforeachtable @command1='dbcc dbreindex (?, ''''', 70) WITH NO_INFOMSGS'

If you want to preserve the original fillfactor, use 0.

Nazim
A custom title

1408 Posts

Posted - 2002-05-02 : 10:36:26

if you want to see messages , do Away with NO_INFOMSGS option

exec sp_MSforeachtable @command1='dbcc dbreindex (?, ''''', 70)'

this will give you summary of each execution.

--------------------------------------------------------------

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources