Automate Admin Activities

21 Oct 2005 3:13 PM

Tarek

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.

21 Oct 2005 3:38 PM

Jerry Spivey

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
Show quote

"Tarek" <Ta***@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.

21 Oct 2005 4:16 PM

Tarek

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

Show quote

"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 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" <Ta***@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.
>
>
>

21 Oct 2005 4:24 PM

Jerry Spivey

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/community/relcommunities.mspx

HTH

Jerry
Show quote

"Tarek" <Ta***@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:
>
>> 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" <Ta***@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.
>>
>>
>>