Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Very Large Transaction File Expand / Collapse
Author
Message
Posted Tuesday, October 16, 2012 11:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 13, 2013 10:34 AM
Points: 5, Visits: 23
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
Posted Tuesday, October 16, 2012 11:12 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 4:08 PM
Points: 40,177, Visits: 36,580
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
Posted Tuesday, October 16, 2012 12:21 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 11:22 AM
Points: 223, Visits: 1,731
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
Posted Tuesday, October 16, 2012 9:52 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 8:49 PM
Points: 81, Visits: 930
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 »

Add to briefcase

Permissions Expand / Collapse