Growing log file issue

8 Jul 2009 9:55 PM

sacgar

we have indexing jobs which keeps on increasing the log file for online index
maintenance for our sql server 2005 instance , is there any way we can
minimize the growth with out taking db in simple recovery mode , currently it
is in full , does sort in tempdb option helps?

9 Jul 2009 12:54 AM

bass_player

One way to minimize the log file size during index maintenance is to not do
maintenance on all of the indexes at all. Create a script that checks for
index fragmentation and only defragment those that have fragmentation
greater than, say 30%. Don't use the out-of-the-box index maintenance
provided by the Database maintenance plan

Show quoteHide quote

"sacgar" <sac***@discussions.microsoft.com> wrote in message
news:E597396C-CBF3-4F07-8328-720DA72A4DC0@microsoft.com...
> we have indexing jobs which keeps on increasing the log file for online
> index
> maintenance for our sql server 2005 instance , is there any way we can
> minimize the growth with out taking db in simple recovery mode , currently
> it
> is in full , does sort in tempdb option helps?
>
>

9 Jul 2009 4:42 AM

sacgar

yeah i am doing with over 30% and pages over 2000 filter but the issue is as
we have couple of big tables and that is causing issues...

Show quoteHide quote

"bass_player" wrote:

> One way to minimize the log file size during index maintenance is to not do
> maintenance on all of the indexes at all. Create a script that checks for
> index fragmentation and only defragment those that have fragmentation
> greater than, say 30%. Don't use the out-of-the-box index maintenance
> provided by the Database maintenance plan
>
> "sacgar" <sac***@discussions.microsoft.com> wrote in message
> news:E597396C-CBF3-4F07-8328-720DA72A4DC0@microsoft.com...
> > we have indexing jobs which keeps on increasing the log file for online
> > index
> > maintenance for our sql server 2005 instance , is there any way we can
> > minimize the growth with out taking db in simple recovery mode , currently
> > it
> > is in full , does sort in tempdb option helps?
> >
> >
>
>
>

9 Jul 2009 5:13 AM

Uri Dimant

sacgar
Yes, specifying SORT IN TEMDB could help as well as allocating more space to
log files for re-indexes

Show quoteHide quote

"sacgar" <sac***@discussions.microsoft.com> wrote in message
news:F394A702-78E7-4210-A143-A49A6C4F5B8B@microsoft.com...
> yeah i am doing with over 30% and pages over 2000 filter but the issue is
> as
> we have couple of big tables and that is causing issues...
>
> "bass_player" wrote:
>
>> One way to minimize the log file size during index maintenance is to not
>> do
>> maintenance on all of the indexes at all. Create a script that checks for
>> index fragmentation and only defragment those that have fragmentation
>> greater than, say 30%. Don't use the out-of-the-box index maintenance
>> provided by the Database maintenance plan
>>
>> "sacgar" <sac***@discussions.microsoft.com> wrote in message
>> news:E597396C-CBF3-4F07-8328-720DA72A4DC0@microsoft.com...
>> > we have indexing jobs which keeps on increasing the log file for online
>> > index
>> > maintenance for our sql server 2005 instance , is there any way we can
>> > minimize the growth with out taking db in simple recovery mode ,
>> > currently
>> > it
>> > is in full , does sort in tempdb option helps?
>> >
>> >
>>
>>
>>

9 Jul 2009 5:47 AM

Tibor Karaszi

Here's a quote regarding ONLINE and STORT_IN_TEMPDB:
"â€¢ When both SORT_IN_TEMPDB and ONLINE are set to ON, the index
transactions are stored in the tempdb transaction log, and the
concurrent user transactions are stored in the transaction log of the
user database. This allows you to truncate the transaction log of the
user database during the index operation if needed. Additionally, if
the tempdb log is not on the same disk as the user database log, the
two logs are not competing for the same disk space."
Above is from
http://download.microsoft.com/download/8/5/e/85eea4fa-b3bb-4426-97d0-7f7151b2011c/OnlineIndex.doc,
where's there's also a section named "Manage Transaction Log Space".

Also see http://msdn.microsoft.com/en-us/library/ms184246.aspx.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Show quoteHide quote

"sacgar" <sac***@discussions.microsoft.com> wrote in message
news:E597396C-CBF3-4F07-8328-720DA72A4DC0@microsoft.com...
> we have indexing jobs which keeps on increasing the log file for
> online index
> maintenance for our sql server 2005 instance , is there any way we
> can
> minimize the growth with out taking db in simple recovery mode ,
> currently it
> is in full , does sort in tempdb option helps?
>
>

9 Jul 2009 9:25 AM

Erland Sommarskog

sacgar (sac***@discussions.microsoft.com) writes:

> we have indexing jobs which keeps on increasing the log file for online
> index maintenance for our sql server 2005 instance , is there any way we
> can minimize the growth with out taking db in simple recovery mode ,
> currently it is in full , does sort in tempdb option helps?

In addition to the other suggestions, it may help to change recovery
mode to bulk_logged during the index maintenance. Bulk_logged mode is
quite like full in that you can restore transaction logs. However, some
operations like index (re)creation are minimally logged in this mode.

If a minimally logged operation has occurred after a log backup, you
cannot restore to a point in time after the minimlally logged operation up
to the next log backup.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx