Log File growth after re-index

  • Hi,

    I have maintenance plan to check integrity,re-organize index,re-build index,updata statistic which run every Sun @ 2am.

    however I notice that on monday my nav.log is growing really big up to 90Gb....

    backup DB is run daily @9pm and backup transaction log is run daily every 15 minutes.

    How do I solve this problem?

    Do you think because my schedule time is in correct.

    right the moment what I do is to shrink the log using

    backup log vgsmdb with no_log

    use [vgsmdb] DBCC SHRINKFILE (N'VGSMDB_Log', 4770)

    I don't use 'Truncate only' because my understanding that using Truncate is not shrinking the actual file in drive, am I correct?

    please help me

    Thanks,

    Susan

  • Being dealt with here - http://www.sqlservercentral.com/Forums/Topic408782-357-3.aspx

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • Hi Susan,

    switching to bulk just before dbreindex, then switching back will keep the transaction log file much smaller. It will increase the transaction log backup file size though. Probably it is a worth to take a full backup after switching back to full recovery mode.

    Have a look at http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx, particularly the last few paragraphs (Logging Considerations).

    Regards,

    Andras


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • Thanks Andras.

    I will do that.

    one more questions.

    when we swicth it, do we need to turn of the transaction log backup too?

    currently the transaction log back up I change it to every 5 minutes in maintenance plan.

    last time I try to db re-index during business hour and got complain from users cos the apps was slow so I cancelled the job.

    I thought I can do it during day time, obviously I can't.

    susan

  • Be careful - make sure you understand the ramifications of switching to bulk-logged recovery mode. For the duration of the bulk-logged operation in the log backup, you cannot do a point-in-time restore. If you have a very long-running index rebuild operation and something catastrophic happens before the index rebuild operation ends then you can't do a point in time restore to any time past the start of the index rebuild.

    Just something to be aware of for disaster recovery planning.

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • Thanks for the warning, Paul.:)

    I may do it during weekend or when we are going to upgrade our apps

  • Here's a query that I use to shrink the log file and the database:

    --Shrinks a database Manually

    sp_dboption 'temp', 'trunc. log on chkpt.', 'TRUE'--Set a checkpoint

    use tempdb checkpoint--Declare the checkpoint

    go

    --Shrink the log file and the Database

    DBCC SHRINKFILE ( 'TEMPLOG' , TRUNCATEONLY )

    GO

    DBCC SHRINKDATABASE (tempdb, 10) --The 10 is 10%

    GO

    sp_helpdb --view db stats...

  • Please - don't shrink tempdb - it's likely to just grow again and you'll end up with file system fragmentation.

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • in addition setting the 'trunc. log on chkpt.' to true is almost equivalent to change recovery mode to Simple wich also can have catastrofic implications at recovery time.

    -Noel


    * Noel

  • True, but that's why you backup the log file every so often during the day...

    Jim

  • Paul,

    You're right there's no need to shrink the tempdb. I was just using that as an example. Obviously you need to change the database and log file name to the ones that you actually want to shrink.

    Jim

  • James A. Schimmels (10/17/2007)


    True, but that's why you backup the log file every so often during the day...

    Jim

    You can not backup the log file on a database with SIMPLE recovery mode. You can truncate the log but that will kill the restore chain.


    * Noel

  • At the moment I will monitor it again Next mon.

    I have changed my transaction log backup from 15 min to 5 min

    Thanks guys

  • I wonder if your really need to re-index every week? Are your indexes so fragmented after a week (I'm not saying that can't happen, but wow that must be lots of writes and deletes), that a index defrag won't work?

  • we setup in maintenance plan that every Sun - it will reindex/re-orgnize etc...

    and also I notice this happens since we move from sql 2000 to sql 2005.

    has anyone using navision?

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply