Post reply

VIM_VCDB transaction log growing hudge

  • July 1, 2010 at 7:10 am

    #220657

    We have a VCC server witch just updated from 3.5 to 4. Before that VCC database became larger than 4Go so we installed Entreprise edition instead of Express edition.

    The recovery mode of database is SIMPLE and Log file of this database take maximum size allowed (500 Mo at beginning, 4 Go after)

    A perfstat(logspace) give 99% of free space on log file so we reduce it with a shrink operation.

    Log file grow from 1Mo to 4Go in less than 1 hour so, at the moment, we make a shrinkfile every 30 minutes to reduce logfile.

    Is somebody have a idea of wath the problem, I will be very happy !;-)

    frederic.jofes@fr.ibm.com

  • July 1, 2010 at 7:19 am

    #1187648

    What version of SQL?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 1, 2010 at 7:20 am

    #1187649

    If the logfile keeps growing that fast, you must have a lot of activity on the database. If it is growing to that size, it needs the space, you shouldn't be shrinking it. You're slowing down your database because it needs to keep growing the logfile. If your concern is that it is taking up too much diskspace, set the maximum size of the logfile to something that better fits your existing space and then look seriously at buying new, larger hard drives.

    If you can, put your log file on a different drive from your database.

    --------------------------------------
    When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
    --------------------------------------
    It’s unpleasantly like being drunk.
    What’s so unpleasant about being drunk?
    You ask a glass of water. -- Douglas Adams

  • July 1, 2010 at 8:12 am

    #1187675

    Version : SQL Server 2005 SP2

  • July 1, 2010 at 8:18 am

    #1187679

    Check the value of log_reuse_wait_desc in sys.databases. What values (other than NOTHING) do you see for that DB?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 5 posts - 1 through 4 (of 4 total)

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