Very Large Transaction File

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Backups » Very Large Transaction File

Very Large Transaction File

Rate Topic

Display Mode

Topic Options

Author

Message

billh-508166

Posted Tuesday, October 16, 2012 11:10 AM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, May 18, 2013 11:43 AM
Points: 5, Visits: 22

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?

Post #1373419

GilaMonster

Posted Tuesday, October 16, 2012 11:12 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 4:08 PM
Points: 38,099, Visits: 30,392

Scheduled maintenance jobs, most likely index rebuilds

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1373420

e4d4

Posted Tuesday, October 16, 2012 12:21 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 12:33 AM
Points: 160, Visits: 1,147

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...

Post #1373457

mahesh.dasoni

Posted Tuesday, October 16, 2012 9:52 PM

SSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 9:25 AM
Points: 81, Visits: 864

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.

Post #1373614

« Prev Topic | Next Topic »

Permissions