Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Transaction log files had grown to five times the database size. Expand / Collapse
Author
Message
Posted Monday, November 26, 2012 4:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 22, 2013 12:13 AM
Points: 19, 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
Post #1388512
Posted Monday, November 26, 2012 4:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:25 AM
Points: 5,306, Visits: 9,686
You're very fortunate to have this problem on the day the latest article in the transaction log stairway is published.

John
Post #1388516
Posted Monday, November 26, 2012 4:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:15 AM
Points: 6,279, Visits: 13,520
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"
Post #1388526
Posted Monday, November 26, 2012 4:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 22, 2013 12:13 AM
Points: 19, 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.
Post #1388540
Posted Monday, November 26, 2012 5:05 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:25 AM
Points: 5,306, Visits: 9,686
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
Post #1388542
Posted Monday, November 26, 2012 5:25 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 22, 2013 12:13 AM
Points: 19, 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
Post #1388550
Posted Monday, November 26, 2012 6:46 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:31 AM
Points: 42,756, Visits: 35,848
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 2008, MVP
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

Post #1388587
Posted Monday, November 26, 2012 7:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:15 AM
Points: 6,279, Visits: 13,520
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"
Post #1388638
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse