Very Large Transaction File

  • I have a database on a SQL 2008R2 server running (virtual) Windows 2008R2 - all 64 bit - all current with service releases. The database is in full recovery. There is a full backup performed every evening at 9 PM. Transaction logs are backed-up every hour beginning at 8 AM and ending at 6 PM. There is generally no one using the accounting application that utilizes the database after 6 PM and definitely no one between the hours of 9 PM and 6 PM. There are a couple of stored procedures in another database that read data from this database during the evening - but no writes. With all this the log file routinely grows to more than two GB's during the evening so that the first transaction log backup at 8 AM is very large while the one's that are created each hour range between 10 and 150 mb.

    Unable to solve this riddle, I asked and received more disc space in the form of a second logical drive to store the backups so I would not consume all the space on the main disc inadvertently. Much to my surprise, when I did this the problem went away. None of this makes any sense. If there was some activity going on at night that I had missed I would still have the problem. Nothing else has changed. What am I missing?

  • Scheduled maintenance jobs, most likely index rebuilds

    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
  • GilaMonster probably has right. To check what is happening, run SQL Trace with destination to file on sql server, this quarante that 100% events will be captured, and you will see...

  • You need to check which all maintanence job are running during evening and night time by which u can conclude why log size is growing.

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

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