<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
Backups
»
Insane sized tran log backups are filling up...
17 posts, Page 1 of 2
1
2
»»
Insane sized tran log backups are filling up the disk.
Rate Topic
Display Mode
Topic Options
Author
Message
Minnesota - Viking
Minnesota - Viking
Posted Friday, August 17, 2012 9:04 AM
SSC Veteran
Group: General Forum Members
Last Login: Wednesday, April 03, 2013 9:54 AM
Points: 201,
Visits: 456
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
Lynn Pettis
Lynn Pettis
Posted Friday, August 17, 2012 9:18 AM
SSC-Insane
Group: General Forum Members
Last Login: Today @ 4:33 PM
Points: 21,832,
Visits: 27,860
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
Minnesota - Viking
Minnesota - Viking
Posted Friday, August 17, 2012 9:32 AM
SSC Veteran
Group: General Forum Members
Last Login: Wednesday, April 03, 2013 9:54 AM
Points: 201,
Visits: 456
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
Lynn Pettis
Lynn Pettis
Posted Friday, August 17, 2012 9:39 AM
SSC-Insane
Group: General Forum Members
Last Login: Today @ 4:33 PM
Points: 21,832,
Visits: 27,860
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
durai nagarajan
durai nagarajan
Posted Tuesday, August 21, 2012 3:33 AM
SSC Eights!
Group: General Forum Members
Last Login: 2 days ago @ 10:16 AM
Points: 863,
Visits: 2,135
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
Lynn Pettis
Lynn Pettis
Posted Tuesday, August 21, 2012 6:04 AM
SSC-Insane
Group: General Forum Members
Last Login: Today @ 4:33 PM
Points: 21,832,
Visits: 27,860
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
durai nagarajan
durai nagarajan
Posted Tuesday, August 21, 2012 7:03 AM
SSC Eights!
Group: General Forum Members
Last Login: 2 days ago @ 10:16 AM
Points: 863,
Visits: 2,135
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
durai nagarajan
durai nagarajan
Posted Tuesday, August 21, 2012 7:11 AM
SSC Eights!
Group: General Forum Members
Last Login: 2 days ago @ 10:16 AM
Points: 863,
Visits: 2,135
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
Minnesota - Viking
Minnesota - Viking
Posted Tuesday, August 21, 2012 11:39 AM
SSC Veteran
Group: General Forum Members
Last Login: Wednesday, April 03, 2013 9:54 AM
Points: 201,
Visits: 456
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
Scott D. Jacobson
Scott D. Jacobson
Posted Tuesday, August 21, 2012 12:21 PM
Old Hand
Group: General Forum Members
Last Login: Yesterday @ 4:28 PM
Points: 335,
Visits: 844
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 »
17 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
