SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Insane sized tran log backups are filling up the disk.


Insane sized tran log backups are filling up the disk.

Author
Message
Minnesota - Viking
Minnesota - Viking
SSChasing Mays
SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)

Group: General Forum Members
Points: 641 Visits: 564
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:-)

Attachments
Backup FOlder.JPG (32 views, 107.00 KB)
sizeCapture.JPG (22 views, 17.00 KB)
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)

Group: General Forum Members
Points: 98336 Visits: 38996
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.

Cool
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)
Minnesota - Viking
Minnesota - Viking
SSChasing Mays
SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)

Group: General Forum Members
Points: 641 Visits: 564
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:-)

Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)

Group: General Forum Members
Points: 98336 Visits: 38996
Updates, deletes, inserts, index rebuilds/reorganizations. Those are just the few things that come to my mind that is going to be logged.

Cool
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)
durai nagarajan
durai nagarajan
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3619 Visits: 2784
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
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)

Group: General Forum Members
Points: 98336 Visits: 38996
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.

Cool
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)
durai nagarajan
durai nagarajan
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3619 Visits: 2784
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
durai nagarajan
durai nagarajan
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3619 Visits: 2784
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
Minnesota - Viking
Minnesota - Viking
SSChasing Mays
SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)

Group: General Forum Members
Points: 641 Visits: 564
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:-)

Scott D. Jacobson
Scott D. Jacobson
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1408 Visits: 1020
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search