Database maintenance using DBCC DBREINDEX

Hi,
Currently, I use sp_msForEachTable to reindex every table in one of my
databases. However I did not find any information regarding this stored
procedure in BOL. Is it safe to use, or why isn't it documented?
EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
Yours sincerely,
Jo Segers.Hi,
The procedure sp_MSforeachtable executes a set of given commands against all
the user tables in the current database.
Have a look into this link for detailed information and usage.
http://www.bstsoftware.com/tsug/Nov...reachtable.html
Thanks
Hari
MCDBA
"Jo Segers" <segers_jo@.hotmail.com> wrote in message
news:uh22kT4JEHA.4052@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Currently, I use sp_msForEachTable to reindex every table in one of my
> databases. However I did not find any information regarding this stored
> procedure in BOL. Is it safe to use, or why isn't it documented?
> EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
> Yours sincerely,
> Jo Segers.
>|||Adding to the other posts:
That procedure only contains a cursor which loops all tables in the database
and executes the specified
command against the tables. You can easily write such a cursor yourself. The
procedure is likely to work the
same way in future service packs of SQL2K, and when Yukon comes, you want to
look at these things anyhow.
Still, it is not documented, not supported, use at own "risk", which only yo
u can assess.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jo Segers" <segers_jo@.hotmail.com> wrote in message news:uh22kT4JEHA.4052@.TK2MSFTNGP11.phx.
gbl...
> Hi,
> Currently, I use sp_msForEachTable to reindex every table in one of my
> databases. However I did not find any information regarding this stored
> procedure in BOL. Is it safe to use, or why isn't it documented?
> EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
> Yours sincerely,
> Jo Segers.
>|||Hi Jo,
If you want a way to run a command against all fragmented indexes in a
databases, check out example E in the Books Online for DBCC SHOWCONTIG. You
should not use sp_msForEachTable.
I'd like to go to the root of your problem, which is wanting to reindex all
the tables in one of your database. I take it you're doing this for
performance reasons. The only way you'll increase performance by doing this
is if the index (clustered or non-clustered) has logical fragmentation and
is used for a range scan. I'm guessing that not all the indexes you're
rebuilding are used in this fashion. Also, sometimes it can be better to run
DBCC INDEXDEFRAG instead of DBCC DBREINDEX.
I recommend you read the whitepaper on this topic at
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Let me know if you have any more questions.
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jo Segers" <segers_jo@.hotmail.com> wrote in message
news:uh22kT4JEHA.4052@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Currently, I use sp_msForEachTable to reindex every table in one of my
> databases. However I did not find any information regarding this stored
> procedure in BOL. Is it safe to use, or why isn't it documented?
> EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
> Yours sincerely,
> Jo Segers.
>|||Hi Paul,
I'm still reading the whitepaper, but the example in BOL helped a lot. I am
going to adjust the code in the example for use in my produktion database.
I am just curious: why isn't sp_msForEachTable supported anymore?
Yours sincerely,
Jo Segers.
"Paul S Randal [MS]" <prandal@.online.microsoft.com> schreef in bericht
news:O2t8tk5JEHA.2376@.tk2msftngp13.phx.gbl...
> Hi Jo,
> If you want a way to run a command against all fragmented indexes in a
> databases, check out example E in the Books Online for DBCC SHOWCONTIG.
You
> should not use sp_msForEachTable.
> I'd like to go to the root of your problem, which is wanting to reindex
all
> the tables in one of your database. I take it you're doing this for
> performance reasons. The only way you'll increase performance by doing
this
> is if the index (clustered or non-clustered) has logical fragmentation and
> is used for a range scan. I'm guessing that not all the indexes you're
> rebuilding are used in this fashion. Also, sometimes it can be better to
run
> DBCC INDEXDEFRAG instead of DBCC DBREINDEX.
> I recommend you read the whitepaper on this topic at
>
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
> Let me know if you have any more questions.
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Jo Segers" <segers_jo@.hotmail.com> wrote in message
> news:uh22kT4JEHA.4052@.TK2MSFTNGP11.phx.gbl...
>|||Jo - It never was supported. It's always been an undocumented SP and so
liable to change or removal with no notice.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jo Segers" <segers_jo@.hotmail.com> wrote in message
news:edRau75JEHA.628@.TK2MSFTNGP11.phx.gbl...
> Hi Paul,
> I'm still reading the whitepaper, but the example in BOL helped a lot. I
am
> going to adjust the code in the example for use in my produktion database.
> I am just curious: why isn't sp_msForEachTable supported anymore?
> Yours sincerely,
> Jo Segers.
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> schreef in bericht
> news:O2t8tk5JEHA.2376@.tk2msftngp13.phx.gbl...
> You
> all
> this
and[vbcol=seagreen]
> run
>
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
> rights.
stored[vbcol=seagreen]
>|||Thanks for your help and time Paul, I appreciate it very much.
"Paul S Randal [MS]" <prandal@.online.microsoft.com> schreef in bericht
news:uI%23gsb9JEHA.204@.TK2MSFTNGP10.phx.gbl...
> Jo - It never was supported. It's always been an undocumented SP and so
> liable to change or removal with no notice.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Jo Segers" <segers_jo@.hotmail.com> wrote in message
> news:edRau75JEHA.628@.TK2MSFTNGP11.phx.gbl...
> am
database.[vbcol=seagreen]
SHOWCONTIG.[vbcol=seagreen]
reindex[vbcol=seagreen]
> and
to[vbcol=seagreen]
>
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
my[vbcol=seagreen]
> stored
>