automate rebuilding indexes

Hi,
I was wondering if someone had a method to rebuild indexes
automatically, based on the results of 'dbcc showcontig'.
I want to automate index rebuilding by using the results of 'dbcc
showcontig with tableresults' into a table.
Then i want to query the results which indexes have a logical and extent
scan fragmentation of >10%.
Finally execute a 'create index <indexname> on <tablename>(columnname)
with drop_existing'
Should i be doing this automatically or manually and have a general
maintenance plan where i do a rebuild with default fillfactor?You find just such an example in Books Online, DBCC SHOWCONTIG. Also see
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jason" <jasonlewis@.hotmail.com> wrote in message news:uQWgItQdGHA.4576@.TK2MSFTNGP05.phx.gb
l...
> Hi,
> I was wondering if someone had a method to rebuild indexes automatically,
based on the results of
> 'dbcc showcontig'.
> I want to automate index rebuilding by using the results of 'dbcc showcont
ig with tableresults'
> into a table.
> Then i want to query the results which indexes have a logical and extent s
can fragmentation of
> Finally execute a 'create index <indexname> on <tablename>(columnname) wit
h drop_existing'
> Should i be doing this automatically or manually and have a general mainte
nance plan where i do a
> rebuild with default fillfactor?

Automate Admin Activities

Hi,
I want to automate some administrative activities (Check Integrity, Shrink
DB Log Space after backup, rebuild indexes...).
I'm using Database Maintenance Plan, is this ok?
After I run these jobs, in the job history and in the log generated I just
see that
the activity succeded, but not what was done.
For example, I would like to know how big was the Transaction Log before the
Shrink and after.
Please help, thanks.Tarek,
For the most part Database Maintenance Plans are fine for most smaller to
medium sized databases. One limitation is the lack of support for
differential backups. MPs can be more difficult to troubleshoot at times as
well...so many DBAs will create their own custom jobs to mimick and extend
the capabilities of MPs. You can view the history of the job or the history
of the MP to get the "run" details. However, this information will not
provide you with detailed specifics as before/after. You might be able to
add additional job steps to the MP job to track additional before/after
data.
HTH
Jerry
"Tarek" <Tarek@.discussions.microsoft.com> wrote in message
news:41F68B47-0B1D-4F75-B513-C4325A9FE591@.microsoft.com...
> Hi,
> I want to automate some administrative activities (Check Integrity, Shrink
> DB Log Space after backup, rebuild indexes...).
> I'm using Database Maintenance Plan, is this ok?
> After I run these jobs, in the job history and in the log generated I just
> see that
> the activity succeded, but not what was done.
> For example, I would like to know how big was the Transaction Log before
> the
> Shrink and after.
> Please help, thanks.|||Thanks Jerry for your response.
Where can I find some templates on how to achieve these jobs:
- Check DB Integrity
- Rebuild Indexes
- Gather Statistics
For example on the help of the DBCC ShowConting I found a script to defrag
all indexes of the database, can this be ok?
I'm not very confident with sqlserver. I'm a dba but not on sql so I'm
trying to learn how to do dba activities here.
Thanks
"Jerry Spivey" wrote:

> Tarek,
> For the most part Database Maintenance Plans are fine for most smaller to
> medium sized databases. One limitation is the lack of support for
> differential backups. MPs can be more difficult to troubleshoot at times
as
> well...so many DBAs will create their own custom jobs to mimick and extend
> the capabilities of MPs. You can view the history of the job or the histo
ry
> of the MP to get the "run" details. However, this information will not
> provide you with detailed specifics as before/after. You might be able to
> add additional job steps to the MP job to track additional before/after
> data.
> HTH
> Jerry
> "Tarek" <Tarek@.discussions.microsoft.com> wrote in message
> news:41F68B47-0B1D-4F75-B513-C4325A9FE591@.microsoft.com...
>
>|||Tarek,
There are a variety of scripts out there to work with...just have to search
for the various ones. This site lists several valuable websites you might
start with. Google is a good place too. Be sure to fully test out any
downloaded scripts in a test environment first prior to introducing them
into a production environment.
SQL Server Communities
http://www.microsoft.com/sql/commun...ommunities.mspx
HTH
Jerry
"Tarek" <Tarek@.discussions.microsoft.com> wrote in message
news:5A84FD1D-B679-40C6-A2D6-D0D78F0DE9E1@.microsoft.com...
> Thanks Jerry for your response.
> Where can I find some templates on how to achieve these jobs:
> - Check DB Integrity
> - Rebuild Indexes
> - Gather Statistics
> For example on the help of the DBCC ShowConting I found a script to defrag
> all indexes of the database, can this be ok?
> I'm not very confident with sqlserver. I'm a dba but not on sql so I'm
> trying to learn how to do dba activities here.
> Thanks
>
>
> "Jerry Spivey" wrote:
>