May 21, 2013 at 2:04 am
Dear,
Currently my t-log size is 3 GB and 87% is available free space. I take regular log backup and full backup in full recovery model.
Now I wanna know that,
1.If I delete log backup files after taking a full backup, would it create any problem while data recovery?
2.If I wanna reduce the t-log size to 2 GB, what are the challenges in this decision?
Please help me.
Akbar
May 21, 2013 at 2:41 am
shohelr2003 (5/21/2013)
1.If I delete log backup files after taking a full backup, would it create any problem while data recovery?
Maybe. Means that if the latest full backup is corrupt or unrestorable, there's no option for restoring the previous full backup then all log backups.
2.If I wanna reduce the t-log size to 2 GB, what are the challenges in this decision?
Why? Is the log always 87% empty? Have you monitored during the heaviest transaction log usage (probably index maintenance)?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 21, 2013 at 4:00 am
GilaMonster (5/21/2013)
shohelr2003 (5/21/2013)
1.If I delete log backup files after taking a full backup, would it create any problem while data recovery?Maybe. Means that if the latest full backup is corrupt or unrestorable, there's no option for restoring the previous full backup then all log backups.
2.If I wanna reduce the t-log size to 2 GB, what are the challenges in this decision?
Why? Is the log always 87% empty? Have you monitored during the heaviest transaction log usage (probably index maintenance)?
1. I understood if the latest full backup is corrupt or unrestorable...
2. Nope, log is not always 87% empty. I monitor it regularly, it becomes like for example 58%, 35%, 12%, 1% free. Then I take log backup and becomes 87% is free.
May 21, 2013 at 4:23 am
shohelr2003 (5/21/2013)
2. Nope, log is not always 87% empty. I monitor it regularly, it becomes like for example 58%, 35%, 12%, 1% free. Then I take log backup and becomes 87% is free.
Do you have scheduled log backups or do you monitor and take the log backups manually?
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 21, 2013 at 4:25 am
shohelr2003 (5/21/2013)
2. Nope, log is not always 87% empty. I monitor it regularly, it becomes like for example 58%, 35%, 12%, 1% free. Then I take log backup and becomes 87% is free.
If SQL's using all of the log between the scheduled log backups, then shrinking it to 2GB is a bit pointless, it'll just grow back to 3.
Please read through this - Managing Transaction Logs[/url]
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 21, 2013 at 5:38 am
Kingston Dhasian (5/21/2013)
Do you have scheduled log backups or do you monitor and take the log backups manually?
I have not scheduled it but I do it manually.
May 21, 2013 at 5:42 am
GilaMonster (5/21/2013)
shohelr2003 (5/21/2013)
2. Nope, log is not always 87% empty. I monitor it regularly, it becomes like for example 58%, 35%, 12%, 1% free. Then I take log backup and becomes 87% is free.If SQL's using all of the log between the scheduled log backups, then shrinking it to 2GB is a bit pointless, it'll just grow back to 3.
I have gone through your link. Thats clear. I have not scheduled log backup rather I do it manually. You are right that shrinking it to 2GB is a bit pointless.
May 21, 2013 at 5:43 am
shohelr2003 (5/21/2013)
Kingston Dhasian (5/21/2013)
Do you have scheduled log backups or do you monitor and take the log backups manually?I have not scheduled it but I do it manually.
Err.. why? Doesn't sound like a good plan.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 21, 2013 at 5:47 am
As far as your log file size is concerned, I would say that it has found its optimal size. Only a SHRINKFILE will reduce the logfiles footprint to the operating system. The amount of data actually contained within the logfile will fluctuate dependent on transactional activity and regularity of transaction log backups.
I would say that if you find the size of the logfile is too big for your requirements, increase the amount of transaction log backups that you make and reduce the size of the .ldf file using SHRINKFILE.
May 21, 2013 at 6:26 am
shohelr2003 (5/21/2013)
Kingston Dhasian (5/21/2013)
Do you have scheduled log backups or do you monitor and take the log backups manually?I have not scheduled it but I do it manually.
It would be good if you schedule it and monitor the usage percentage
Depending on the maximum usage in that scenario, you can decide whether to shrink the file or not
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 25, 2013 at 11:14 pm
@kevaburg, I think so that my log file has got its optimal size.
Actually I check log space regularly and take a log backup when necessary because there are some processes in my application that cause log file to grow larger. Otherwise regular transactions do not affect log size noticeably. So I do not schedule it.
But in near future, I gonna schedule log backup and keep on monitoring.
Thanks all for sharing your time and knowledge.
May 26, 2013 at 4:05 am
shohelr2003 (5/25/2013)
Actually I check log space regularly and take a log backup when necessary because there are some processes in my application that cause log file to grow larger. Otherwise regular transactions do not affect log size noticeably. So I do not schedule it.
Except that the reason for running log backups is not to manage the size of the log file, it's for recoverability of the database and the interval between log backups should be based on the data loss allowance in the case of a disaster.
Please read through the article I referenced earlier.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 26, 2013 at 4:09 am
GilaMonster (5/26/2013)
shohelr2003 (5/25/2013)
Actually I check log space regularly and take a log backup when necessary because there are some processes in my application that cause log file to grow larger. Otherwise regular transactions do not affect log size noticeably. So I do not schedule it.Except that the reason for running log backups is not to manage the size of the log file, it's for recoverability of the database and the interval between log backups should be based on the data loss allowance in the case of a disaster.
Please read through the article I referenced earlier.
And that aside, if the only reason to perform a TLog backup is to reduce the size of the log itself, perhaps something to consider is running the database in the SIMPLE Recovery model.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply