August 17, 2012 at 9:04 am
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 ?
[font="Verdana"]
Today is the tomorrow you worried about yesterday:-)[/font]
August 17, 2012 at 9:18 am
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.
August 17, 2012 at 9:32 am
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 ?
[font="Verdana"]
Today is the tomorrow you worried about yesterday:-)[/font]
August 17, 2012 at 9:39 am
Updates, deletes, inserts, index rebuilds/reorganizations. Those are just the few things that come to my mind that is going to be logged.
August 21, 2012 at 3:33 am
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
August 21, 2012 at 6:04 am
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.
August 21, 2012 at 7:03 am
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
August 21, 2012 at 7:11 am
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
August 21, 2012 at 11:39 am
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.
[font="Verdana"]
Today is the tomorrow you worried about yesterday:-)[/font]
August 21, 2012 at 12:21 pm
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.
August 21, 2012 at 12:32 pm
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?.
Not sure what you are trying to say here.
August 21, 2012 at 12:34 pm
Minnesota - Viking (8/21/2012)
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.
Not sure what you mean by this: We are okay with the transactions in b/w.
August 21, 2012 at 12:39 pm
Lynn Pettis (8/21/2012)
Minnesota - Viking (8/21/2012)
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.
Not sure what you mean by this: We are okay with the transactions in b/w.
I meant, we will not need a point -in time recovery for a time range that will be after 6PM and before 6AM. Only the transactions after 6AM and before 6PM are important to us.
Am I making sense ?
[font="Verdana"]
Today is the tomorrow you worried about yesterday:-)[/font]
August 21, 2012 at 12:58 pm
I would increase the time you take a log backup to let's say every 30 mins. And as Scott said, you should set the tLogs to grow in MB as oppossed to a percentage.
Aurora
August 21, 2012 at 1:03 pm
MegaDBA (8/21/2012)
I would increase the time you take a log backup to let's say every 30 mins. And as Scott said, you should set the tLogs to grow in MB as oppossed to a percentage.
I will agree with changing the t-log to grow at a set size, but I would also size the t-log to a size that holds all the normal transaction activity plus a safety margin for unusual activity. Every time you wait for the log file to grow and initialize you are impacting server performance.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply