November 26, 2012 at 4:16 am
Hi All,
The log file is grown to five times the database size.
Transaction Log file size 3.1GB
Database file size is 700 MB
Recovery Model : Full
backup strategy : Daily full database backup, transaction log backup for every 1 hour.
Even after this the transaction log had grown upto 3.1 GB. So could you please suggest me other than shrinking the log files regularly can i set any other option.
Note:
a)The recovery model of the database to be FUll cannot be changed to Simple
b) Log backup freuency was 4hours so i had changed it to 1 hour.
verified the log_Reuse_stats.... on query got Log_Backup
I am not sure what other options i need to set, so that i can stop shrinking the file regularly.
Regards,
Sanath Kumar
November 26, 2012 at 4:23 am
November 26, 2012 at 4:37 am
sanath.kumar.kura (11/26/2012)
Hi All,The log file is grown to five times the database size.
Transaction Log file size 3.1GB
Database file size is 700 MB
Recovery Model : Full
backup strategy : Daily full database backup, transaction log backup for every 1 hour.
Even after this the transaction log had grown upto 3.1 GB. So could you please suggest me other than shrinking the log files regularly can i set any other option.
Note:
a)The recovery model of the database to be FUll cannot be changed to Simple
b) Log backup freuency was 4hours so i had changed it to 1 hour.
verified the log_Reuse_stats.... on query got Log_Backup
I am not sure what other options i need to set, so that i can stop shrinking the file regularly.
Regards,
Sanath Kumar
i would increase the frequency of your transaction log backups from 1 hour to every 30 mins.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 26, 2012 at 4:59 am
Thanks for the reply. I will go through the link
I had already modifed the frequency from 4 hours to 1 hour. Still need to change to 30mins
If i changed the frequency it would solve the issue.
November 26, 2012 at 5:05 am
If it's a single transaction that's causing the log growth, increasing backup frequency won't help. You'll need to rewrite the transaction or ensure you have enough disk space to accommodate it as it is.
John
November 26, 2012 at 5:25 am
it's not single transaction file. But in period of time the log file is going on increasing.
My concern is even after backup strategy i need to shrink the file monthly twice which i won't want to do very regularly.
As said if it increasing for single transaction then it would better option to resize the log file.
So need your help where i can set any option where the log file will re-use the inactive portions. so that i will not run any shrink cmmd.
-Sanath
November 26, 2012 at 6:46 am
Please read through this - Managing Transaction Logs[/url]
No, you do not need to shrink the log file, it's counter-productive. If the log needs to be that size it will just grow again and you'll have wasted the time taken to shrink and regrow.
The log file will reuse inactive portions, that's how it's designed. Only if there are no inactive portions will it grow.
If the log continues to grow, please read through this: http://www.sqlservercentral.com/articles/Transaction+Log/72488/
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
November 26, 2012 at 7:48 am
sanath.kumar.kura (11/26/2012)
If i changed the frequency it would solve the issue.
Not on its own but it's a start!
The links posted will guide you further
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply