DB Maintenenace Plan, Optimizations

Hi All,
I have a database that has not recieved any type of maintenance apart from
daily backups. It has grown to over 30GB. I created some jobs to reindex
tables that I thought were high activity. Query performance increased
substantially but I believe it could be better. Database has also shruck
about 4GB.
After running a showcontig on the table, I could still see lots of
fragmentation on tables that were not included in the job above. So I
created a DB Maintenance Plan to for only optimizations and chose to
reorganize data and index pages, and keep original fillfactor that the index
was created with.
The job ran last night but did not finish because it failed on an unknown
step after 27 mins. I ran this job manually and it ran for 50 mins. before
I
manually stopped the job. The actual database space decreased another 7GB.
The transaction log backup could not be performed becuase there was not
enough disk space, the trans log was 23GB.
My questions are:
How can I enure this optimization job will successfully run in the future?
Is there a way to make the trans log smaller after the optimization job is
run?
Should I be doing a Full DB Backup AFTER the optimization job?
Can I shrink the allocated size of the database by adding to the
optimization job to "remove unused space from database files" safely? I wan
t
to ensure the database will still grow automatically if space is required.
Cheers!First off you should undo the shrink part of the maintenance plan. When you
reindex the tables you need lots of free space in the data and log files in
order for it to do it's job properly. If there isn't it has to grow the
files which is resource intensive. That process will leave free space which
the shrink part of the maintenance plan will not like and then it will
shrink the files. The shrinking process will fragment your tables and
indexes all over again. See here:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
You should leave the size at what ever it needs to be to do what it has to
do. The exception would be the log file. If it just keeps growing you
probably aren't backing up the log files on a regular basis. Full backups
are not enough if you are in the full recovery mode.
Andrew J. Kelly SQL MVP
"AKing" <AKing@.discussions.microsoft.com> wrote in message
news:F9CBB7CC-A53C-498A-A678-E974558EF4C9@.microsoft.com...
> Hi All,
> I have a database that has not recieved any type of maintenance apart from
> daily backups. It has grown to over 30GB. I created some jobs to reindex
> tables that I thought were high activity. Query performance increased
> substantially but I believe it could be better. Database has also shruck
> about 4GB.
> After running a showcontig on the table, I could still see lots of
> fragmentation on tables that were not included in the job above. So I
> created a DB Maintenance Plan to for only optimizations and chose to
> reorganize data and index pages, and keep original fillfactor that the
> index
> was created with.
> The job ran last night but did not finish because it failed on an unknown
> step after 27 mins. I ran this job manually and it ran for 50 mins.
> before I
> manually stopped the job. The actual database space decreased another
> 7GB.
> The transaction log backup could not be performed becuase there was not
> enough disk space, the trans log was 23GB.
> My questions are:
> How can I enure this optimization job will successfully run in the future?
> Is there a way to make the trans log smaller after the optimization job
> is
> run?
> Should I be doing a Full DB Backup AFTER the optimization job?
> Can I shrink the allocated size of the database by adding to the
> optimization job to "remove unused space from database files" safely? I
> want
> to ensure the database will still grow automatically if space is required.
> Cheers!|||Thank you for your reply. You have provided some helpful insight.
I tried to run the optimization job (w/out shrinking db) and I got the same
behaviour. I had to cancel the job after it had been running for 50 minutes
.
Once again the translog was 21GB.
I understand that the reindex job can be intensive, this wil only multiply
if the translog grows from its usual size (~1gb) to 21GB.
Is there a way to run this job w/out the side effect above?
Will the translogs always be this big when running the optimization?
Maybe the only way is to have nightly reindex of portions of the database
until it has reindexed the entire database.
Cheers
"Andrew J. Kelly" wrote:

> First off you should undo the shrink part of the maintenance plan. When y
ou
> reindex the tables you need lots of free space in the data and log files i
n
> order for it to do it's job properly. If there isn't it has to grow the
> files which is resource intensive. That process will leave free space whi
ch
> the shrink part of the maintenance plan will not like and then it will
> shrink the files. The shrinking process will fragment your tables and
> indexes all over again. See here:
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> You should leave the size at what ever it needs to be to do what it has to
> do. The exception would be the log file. If it just keeps growing you
> probably aren't backing up the log files on a regular basis. Full backups
> are not enough if you are in the full recovery mode.
> --
> Andrew J. Kelly SQL MVP
>
> "AKing" <AKing@.discussions.microsoft.com> wrote in message
> news:F9CBB7CC-A53C-498A-A678-E974558EF4C9@.microsoft.com...
>
>|||How large are the tables in the db? You might want to skip the actual
maintenance plan and just create a job with DBCC DBREINDEX instead to just
rebuild the ones that are fragmented above a certain level. Or just do a
portion of them each night. There is a script under DBCC SHOWCONTIG in
BooksOnLine to allow you to choose which tables will be reindexed or even
defragged.
Andrew J. Kelly SQL MVP
"AKing" <AKing@.discussions.microsoft.com> wrote in message
news:F06D716A-0ECA-4EDE-8EBD-547715C543CC@.microsoft.com...
> Thank you for your reply. You have provided some helpful insight.
> I tried to run the optimization job (w/out shrinking db) and I got the
> same
> behaviour. I had to cancel the job after it had been running for 50
> minutes.
> Once again the translog was 21GB.
> I understand that the reindex job can be intensive, this wil only multiply
> if the translog grows from its usual size (~1gb) to 21GB.
> Is there a way to run this job w/out the side effect above?
> Will the translogs always be this big when running the optimization?
> Maybe the only way is to have nightly reindex of portions of the database
> until it has reindexed the entire database.
> Cheers
>
> "Andrew J. Kelly" wrote:
>|||Hi Andrew,
The database allocation is ~36GB and at one time only had 50MB of free
space. Although through reindexing some high activity tables I have managed
to reduce the actual size of the database to ~25GB. I plan to shrink some o
f
the free space as I believe it is space wasted.
"Andrew J. Kelly" wrote:

> How large are the tables in the db? You might want to skip the actual
> maintenance plan and just create a job with DBCC DBREINDEX instead to just
> rebuild the ones that are fragmented above a certain level. Or just do a
> portion of them each night. There is a script under DBCC SHOWCONTIG in
> BooksOnLine to allow you to choose which tables will be reindexed or even
> defragged.
> --
> Andrew J. Kelly SQL MVP
>
> "AKing" <AKing@.discussions.microsoft.com> wrote in message
> news:F06D716A-0ECA-4EDE-8EBD-547715C543CC@.microsoft.com...
>
>