High rate of log file growth

We added several indexs to a DB.
We have not changed our number of transactions.
The rate of log file growth has moved from about 320MB per hour before the
indexes to 2GB per hour after the indexes were added.
How would we go about finding the operations that are causing the increase
in log file growth?
OwenC
"OwenC" <OwenC@.community.nospam> wrote in message
news:OW4IUQ8eHHA.1312@.TK2MSFTNGP06.phx.gbl...
> We added several indexs to a DB.
> We have not changed our number of transactions.
> The rate of log file growth has moved from about 320MB per hour before the
> indexes to 2GB per hour after the indexes were added.
> How would we go about finding the operations that are causing the increase
> in log file growth?
Are you doing and DBCC dbreindex, etc on your indexes?
Are your indexes finished building, or still building?

> OwenC
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||We are not doing a DBCC REINDEX. Once we applied the indexes we left them
alone.
The indexes have finished building.
OwenC
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:Olqu6J%23eHHA.1312@.TK2MSFTNGP06.phx.gbl...
> "OwenC" <OwenC@.community.nospam> wrote in message
> news:OW4IUQ8eHHA.1312@.TK2MSFTNGP06.phx.gbl...
> Are you doing and DBCC dbreindex, etc on your indexes?
> Are your indexes finished building, or still building?
>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>
|||Hi, OwenC,
I think that the data volume of your database is huge, right?
If you frequently perform database backup and the disk space is limited, I
recommend that set your database recovery model to simple to reduce logging
increasing speed;
If you want to leave database recovery model to FULL so that you can
restore your database to any time point that you want, I recommend that you
start a job to truncate your transaction log every day.
For more information, you may refer to "Recovery models" and "BACKUP LOG"
in SQL Server Books Online.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:APJ0YWMfHHA.6068@.TK2MSFTNGHUB02.phx.gbl...
> Hi, OwenC,
> I think that the data volume of your database is huge, right?
> If you frequently perform database backup and the disk space is limited, I
> recommend that set your database recovery model to simple to reduce
> logging
> increasing speed;
> If you want to leave database recovery model to FULL so that you can
> restore your database to any time point that you want, I recommend that
> you
> start a job to truncate your transaction log every day.
Umm... That's not great advice. That would invalidate his transaction log
backup chain.
And once truncated, he'd lose the ability to restore your database to any
time point you want.

> For more information, you may refer to "Recovery models" and "BACKUP LOG"
> in SQL Server Books Online.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ================================================== ===
> Get notification to my posts through email? Please refer to:
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply
> promptly.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ================================================== ====
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ================================================== ====
>
>
>
>
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Hi, Greg,
Thanks for your pointing out.
Yes, if the log file is truncated, he would lose the ability to restore his
database to any time point he want; however if their disk space is limited,
the truncate operation may be neccessary no matter in any recovery mode.
Simple recovery mode can produce less transaction logs than FULL recovery
mode. The logs increasing speed is slow, and this may be able to suit his
needs.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||I need to run the FULL recovery mode as this is the DB for an ERP
application. I can't afford to lose even any transactions!!!
We actually do transaction log backups and truncate them every 2 hours.
We use Backup Exec to do this.
It seems from further investigation that what caused the increased growth is
that the indexes that were added made some DB operations 20 times faster.
This caused some batch processes to run faster than they had been able to.
There was a backlog of these batches and so in clearing the backlog it
caused the growth rate of the log file to increase.
OwenC
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:YvPadJNfHHA.4368@.TK2MSFTNGHUB02.phx.gbl...
> Hi, Greg,
> Thanks for your pointing out.
> Yes, if the log file is truncated, he would lose the ability to restore
> his
> database to any time point he want; however if their disk space is
> limited,
> the truncate operation may be neccessary no matter in any recovery mode.
> Simple recovery mode can produce less transaction logs than FULL recovery
> mode. The logs increasing speed is slow, and this may be able to suit his
> needs.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ================================================== ===
> Get notification to my posts through email? Please refer to:
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply
> promptly.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ================================================== ====
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ================================================== ====
>
>
>
>
|||Hi, OwenC,
Thanks for your updating and response.
I am glad to hear that you found the real cause in your situation. What is
the kind of the backlog as you mentioned? Have you found a way to resolve
it now?
Please feel free to let me know if you need any assistance.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Hi, Owen
I am interested in this issue. Would you mind letting me know the result of
this issue? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Charles Wang
Microsoft Online Community Support
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
sql