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


Transaction log files had grown to five times the database size.


Transaction log files had grown to five times the database size.

Author
Message
sanath.kumar.kura
sanath.kumar.kura
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 128
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
John Mitchell-245523
John Mitchell-245523
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14348 Visits: 15980
You're very fortunate to have this problem on the day the latest article in the transaction log stairway is published.

John
Perry Whittle
Perry Whittle
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20204 Visits: 17244
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" ;-)
sanath.kumar.kura
sanath.kumar.kura
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 128
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.
John Mitchell-245523
John Mitchell-245523
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14348 Visits: 15980
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
sanath.kumar.kura
sanath.kumar.kura
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 128
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88429 Visits: 45284
Please read through this - Managing Transaction Logs

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Perry Whittle
Perry Whittle
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20204 Visits: 17244
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" ;-)
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