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 12»»

Insane sized tran log backups are filling up the disk. Expand / Collapse
Author
Message
Posted Friday, August 17, 2012 9:04 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 3:54 PM
Points: 211, Visits: 520
I have a particular DB on a SS2005 Enterprise. It is scheduled to be backed up in FULL every day night @ 11 PM. And hourly tranlog backups are being taken between 6AM & 6PM. Every time a tranlog backup is taken old tran logs that are older than 24 hours are being cleaned up.

The database in FULL recovery model.

The Problem: The full backup size is 40 MB whereas tranlog backups are like 6GB, 9GB etc please look at the attached images.

Please advise, what is wrong here ?



Today is the tomorrow you worried about yesterday


  Post Attachments 
Backup FOlder.JPG (28 views, 107.97 KB)
sizeCapture.JPG (18 views, 17.56 KB)
Post #1346602
Posted Friday, August 17, 2012 9:18 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:48 PM
Points: 20,734, Visits: 32,499
Looks to me like you have a very active database even for its small size. I can understand the initial tlog being large, as it is all the activity between 6:00 PM and 6:00 AM.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1346607
Posted Friday, August 17, 2012 9:32 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 3:54 PM
Points: 211, Visits: 520
Yeah. Its too surprising to believe that there is such huge activity going on in the DB. Is there a way we can determine what caused the trnlog to be so huge ?


Today is the tomorrow you worried about yesterday
Post #1346615
Posted Friday, August 17, 2012 9:39 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:48 PM
Points: 20,734, Visits: 32,499
Updates, deletes, inserts, index rebuilds/reorganizations. Those are just the few things that come to my mind that is going to be logged.


Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1346621
Posted Tuesday, August 21, 2012 3:33 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 30, 2014 8:15 AM
Points: 1,056, Visits: 2,687
Lynn Pettis (8/17/2012)
Looks to me like you have a very active database even for its small size. I can understand the initial tlog being large, as it is all the activity between 6:00 PM and 6:00 AM.


lynn will the tlog backup at 6:00 AM will have the activities after full backup or from the last tlog backup.



Regards
Durai Nagarajan
Post #1347657
Posted Tuesday, August 21, 2012 6:04 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:48 PM
Points: 20,734, Visits: 32,499
durai nagarajan (8/21/2012)
Lynn Pettis (8/17/2012)
Looks to me like you have a very active database even for its small size. I can understand the initial tlog being large, as it is all the activity between 6:00 PM and 6:00 AM.


lynn will the tlog backup at 6:00 AM will have the activities after full backup or from the last tlog backup.



From the last t-log backup.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1347735
Posted Tuesday, August 21, 2012 7:03 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 30, 2014 8:15 AM
Points: 1,056, Visits: 2,687
In that case he has not scheduled Tlog backup from 6:00PM to 10:00PM which in turn might leave him in data loss incase of disaster.

with full backup and next day TLog are you saying that sql engine will restore only the required details from log and not all the details from the TLog if it is restored?.



Regards
Durai Nagarajan
Post #1347762
Posted Tuesday, August 21, 2012 7:11 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 30, 2014 8:15 AM
Points: 1,056, Visits: 2,687
lynn,

My company is asking for the Tlog retention period in tapes which is happening daily.

we have TLOg backup every hour and full backup once in a day.

what is your suggestion?




Regards
Durai Nagarajan
Post #1347768
Posted Tuesday, August 21, 2012 11:39 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 3:54 PM
Points: 211, Visits: 520
durai nagarajan (8/21/2012)
In that case he has not scheduled Tlog backup from 6:00PM to 10:00PM which in turn might leave him in data loss incase of disaster.

with full backup and next day TLog are you saying that sql engine will restore only the required details from log and not all the details from the TLog if it is restored?.



The plan was to have TLOGS only from 6AM - 6PM. We are okay with the transactions in b/w.



Today is the tomorrow you worried about yesterday
Post #1347988
Posted Tuesday, August 21, 2012 12:21 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 5:41 PM
Points: 356, Visits: 918
I can't help but notice you're growing the TLOG by a percentage unlike the data file which is grown 1MB at a time. Any reason for this? This will cause the log file to grow exponentially over time.
Post #1348015
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse