I have a maintenance plan that has a step which rebuilds indexes.
While doing this the transaction log bloats to a huge size - 20gb.
As a result, available disk space is used up and the job fails.

Is this expected behaviour? (the log growth I mean).
Is there a way to prevent this or would doing so be dangerous?
Do I just allocate more disk space to this partition?
I have a vague knowledge of what the rebuilding means but can someone
explain how the transaction log is involved during this process please.

TIA

Check your recovery model. If it is set to full and you don't need
full recovery, set it to simple, back it up with truncate only then
shrink it. This should then let it grow to a "normal" size.

This might you out. It will give you a good understanding of
what goes on under the hood so to speak.

http://www.microsoft.com/technet/pro.../ss2kidbp.mspx

When you start getting into issue with "maintenance plans" you will
soon come to realize it's better to get your own set of scripts. Not
having full control leaves you vulnerable to the whims of the GUI.
There are many many versions on a number of sites. I offer this one
as the basis for what I do with my servers. I have tweaked it over
the past year or so to fit our environment, but it's a great base!


http://download.microsoft.com/downlo...AITScripts.zip

Another useful read, where I initially found the scripts

http://www.sqlmag.com/Article/Articl...ver_96463.html

Good luck!

David Hay

Are you doing rebuild or reorg? reorg has the capability to actually log
FAR more stuff than the size of the indexes it rebuilds because it does page
swaps, and the same page can get swapped more than once.

Do you have any free space in your database, or were you a bad boy/girl and
left the default database growth settings in place? If no free space,
consider doubling the size of your database then try maintenance. Also, if
doing a reorg, try a rebuild instead after creating sufficient free space.

Likewise, find/develop a script to control indexing (there is a good start
of one in BOL) instead of using maint plan. That will get you doing only
stuff that actually needs to be defragged, instead of everything.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"Jay" <noreply@here.orhere> wrote in message
news:OahcItK6IHA.2348@TK2MSFTNGP06.phx.gbl...
>I have a maintenance plan that has a step which rebuilds indexes.
> While doing this the transaction log bloats to a huge size - 20gb.
> As a result, available disk space is used up and the job fails.
>
> Is this expected behaviour? (the log growth I mean).
> Is there a way to prevent this or would doing so be dangerous?
> Do I just allocate more disk space to this partition?
> I have a vague knowledge of what the rebuilding means but can someone
> explain how the transaction log is involved during this process please.
>
> TIA

Thanks guys, I will look into both your recommendations when I return to
work.
The DB is a vendors back end and was installed and configged by them. As was
the maintenance plan.
This is a requirement of them fully supporting the application. I will need
to check with them with any changes I make to the plan.

The DB itself is set to autogrow and is around 18gb in size.

"TheSQLGuru" <kgboles@earthlink.net> wrote in message
news:EY-dnf6XSddGBR3VnZ2dnUVZ_obinZ2d@earthlink.com...
> Are you doing rebuild or reorg? reorg has the capability to actually log
> FAR more stuff than the size of the indexes it rebuilds because it does
> page swaps, and the same page can get swapped more than once.
>
> Do you have any free space in your database, or were you a bad boy/girl
> and left the default database growth settings in place? If no free space,
> consider doubling the size of your database then try maintenance. Also,
> if doing a reorg, try a rebuild instead after creating sufficient free
> space.
>
> Likewise, find/develop a script to control indexing (there is a good start
> of one in BOL) instead of using maint plan. That will get you doing only
> stuff that actually needs to be defragged, instead of everything.
>
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
>
> "Jay" <noreply@here.orhere> wrote in message
> news:OahcItK6IHA.2348@TK2MSFTNGP06.phx.gbl...
>>I have a maintenance plan that has a step which rebuilds indexes.
>> While doing this the transaction log bloats to a huge size - 20gb.
>> As a result, available disk space is used up and the job fails.
>>
>> Is this expected behaviour? (the log growth I mean).
>> Is there a way to prevent this or would doing so be dangerous?
>> Do I just allocate more disk space to this partition?
>> I have a vague knowledge of what the rebuilding means but can someone
>> explain how the transaction log is involved during this process please.
>>
>> TIA
>
>

I've been having a read up on indexing and specifically the options in 2005.
Given that this maintenance plan is 99.99% guaranteed to only run when there
is zero user access, should I set the online = off option when doing the
index rebuild?

If I'm reading correctly then this should greatly reduce the amount of
logging.
Is there any dangers involved with doing this?



"Jay" <noreply@here.orhere> wrote in message
news:OU3acrS6IHA.5116@TK2MSFTNGP06.phx.gbl...
> Thanks guys, I will look into both your recommendations when I return to
> work.
> The DB is a vendors back end and was installed and configged by them. As
> was the maintenance plan.
> This is a requirement of them fully supporting the application. I will
> need to check with them with any changes I make to the plan.
>
> The DB itself is set to autogrow and is around 18gb in size.
>
> "TheSQLGuru" <kgboles@earthlink.net> wrote in message
> news:EY-dnf6XSddGBR3VnZ2dnUVZ_obinZ2d@earthlink.com...
>> Are you doing rebuild or reorg? reorg has the capability to actually log
>> FAR more stuff than the size of the indexes it rebuilds because it does
>> page swaps, and the same page can get swapped more than once.
>>
>> Do you have any free space in your database, or were you a bad boy/girl
>> and left the default database growth settings in place? If no free
>> space, consider doubling the size of your database then try maintenance.
>> Also, if doing a reorg, try a rebuild instead after creating sufficient
>> free space.
>>
>> Likewise, find/develop a script to control indexing (there is a good
>> start of one in BOL) instead of using maint plan. That will get you
>> doing only stuff that actually needs to be defragged, instead of
>> everything.
>>
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>>
>> "Jay" <noreply@here.orhere> wrote in message
>> news:OahcItK6IHA.2348@TK2MSFTNGP06.phx.gbl...
>>>I have a maintenance plan that has a step which rebuilds indexes.
>>> While doing this the transaction log bloats to a huge size - 20gb.
>>> As a result, available disk space is used up and the job fails.
>>>
>>> Is this expected behaviour? (the log growth I mean).
>>> Is there a way to prevent this or would doing so be dangerous?
>>> Do I just allocate more disk space to this partition?
>>> I have a vague knowledge of what the rebuilding means but can someone
>>> explain how the transaction log is involved during this process please.
>>>
>>> TIA
>>
>>
>
>

The default is to rebuild indexes off line any way. While this may reduce
some of the logging activity that will not be the deciding factor in how
much gets logged. If you rebuild then indexes you can change the db to
Simple recovery mode beforehand to minimize the logging. But you will need
to set it back when done and issue another FULL backup to restart the log
chain before doing log backups again. You should ensure there is plenty of
space on the drive to do what needs to be done. If you are rebuilding every
index in every table you can do one of two things. One is to run several log
backups during the rebuild process to allow the space to be reused and keep
the log file size in check. But You should really not be rebuilding indexes
that don't need rebuilding in the first place. Stop using the maintenance
plan and create your own job to only reindex indexes that have above a
certain amount of fragmentation. That will greatly reduce the number of
indexes that get rebuilt each night and thus less logging. You can find a
sample script in BooksOnLine under the sys.dm_db_index_physical_stats entry
to rebuild only ones that are above a certain fragmentation. Or have a look
at this article in SQL Magazine with a sp to do that and more as well.

http://www.sqlmag.com/Article/Articl...ver_99019.html


--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Jay" <noreply@here.orhere> wrote in message
news:eQrK5Bh6IHA.324@TK2MSFTNGP06.phx.gbl...
> I've been having a read up on indexing and specifically the options in
> 2005.
> Given that this maintenance plan is 99.99% guaranteed to only run when
> there is zero user access, should I set the online = off option when doing
> the index rebuild?
>
> If I'm reading correctly then this should greatly reduce the amount of
> logging.
> Is there any dangers involved with doing this?
>
>
>
> "Jay" <noreply@here.orhere> wrote in message
> news:OU3acrS6IHA.5116@TK2MSFTNGP06.phx.gbl...
>> Thanks guys, I will look into both your recommendations when I return to
>> work.
>> The DB is a vendors back end and was installed and configged by them. As
>> was the maintenance plan.
>> This is a requirement of them fully supporting the application. I will
>> need to check with them with any changes I make to the plan.
>>
>> The DB itself is set to autogrow and is around 18gb in size.
>>
>> "TheSQLGuru" <kgboles@earthlink.net> wrote in message
>> news:EY-dnf6XSddGBR3VnZ2dnUVZ_obinZ2d@earthlink.com...
>>> Are you doing rebuild or reorg? reorg has the capability to actually
>>> log FAR more stuff than the size of the indexes it rebuilds because it
>>> does page swaps, and the same page can get swapped more than once.
>>>
>>> Do you have any free space in your database, or were you a bad boy/girl
>>> and left the default database growth settings in place? If no free
>>> space, consider doubling the size of your database then try maintenance.
>>> Also, if doing a reorg, try a rebuild instead after creating sufficient
>>> free space.
>>>
>>> Likewise, find/develop a script to control indexing (there is a good
>>> start of one in BOL) instead of using maint plan. That will get you
>>> doing only stuff that actually needs to be defragged, instead of
>>> everything.
>>>
>>> --
>>> Kevin G. Boles
>>> Indicium Resources, Inc.
>>> SQL Server MVP
>>> kgboles a earthlink dt net
>>>
>>>
>>> "Jay" <noreply@here.orhere> wrote in message
>>> news:OahcItK6IHA.2348@TK2MSFTNGP06.phx.gbl...
>>>>I have a maintenance plan that has a step which rebuilds indexes.
>>>> While doing this the transaction log bloats to a huge size - 20gb.
>>>> As a result, available disk space is used up and the job fails.
>>>>
>>>> Is this expected behaviour? (the log growth I mean).
>>>> Is there a way to prevent this or would doing so be dangerous?
>>>> Do I just allocate more disk space to this partition?
>>>> I have a vague knowledge of what the rebuilding means but can someone
>>>> explain how the transaction log is involved during this process please.
>>>>
>>>> TIA
>>>
>>>
>>
>>
>
>

"TheSQLGuru" <kgboles@earthlink.net> wrote in message
news:EY-dnf6XSddGBR3VnZ2dnUVZ_obinZ2d@earthlink.com...
> Are you doing rebuild or reorg? reorg has the capability to actually log
> FAR more stuff than the size of the indexes it rebuilds because it does
> page swaps, and the same page can get swapped more than once.
>
> Do you have any free space in your database, or were you a bad boy/girl
> and left the default database growth settings in place? If no free space,
> consider doubling the size of your database then try maintenance. Also,
> if doing a reorg, try a rebuild instead after creating sufficient free
> space.
>
> Likewise, find/develop a script to control indexing (there is a good start
> of one in BOL) instead of using maint plan. That will get you doing only
> stuff that actually needs to be defragged, instead of everything.

Is a rebuild.
DB is set to autogrow. Have looked at the report and the data file seems to
grow either by 100 or 250mb as and when needed.
Log file grows by 100mb jumps.
I'll have to check with the app's vendor to ensure doing this (ditch maint
plan and develop scripts) doesn't void our SLA.

Thanks.
Jay


> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
>
> "Jay" <noreply@here.orhere> wrote in message
> news:OahcItK6IHA.2348@TK2MSFTNGP06.phx.gbl...
>>I have a maintenance plan that has a step which rebuilds indexes.
>> While doing this the transaction log bloats to a huge size - 20gb.
>> As a result, available disk space is used up and the job fails.
>>
>> Is this expected behaviour? (the log growth I mean).
>> Is there a way to prevent this or would doing so be dangerous?
>> Do I just allocate more disk space to this partition?
>> I have a vague knowledge of what the rebuilding means but can someone
>> explain how the transaction log is involved during this process please.
>>
>> TIA
>
>