October 16, 2012 at 11:10 am
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?
October 16, 2012 at 11:12 am
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
October 16, 2012 at 12:21 pm
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...
October 16, 2012 at 9:52 pm
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