Log Size Issue

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 High Availability » Log Size Issue

Log Size Issue

Rate Topic

Display Mode

Topic Options

Author

Message

OnlyOneRJ

Posted Thursday, January 10, 2013 2:25 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 09, 2013 9:02 AM
Points: 158, Visits: 443

How do i set the size of Log file so that it overwrites and we do not do manual tasks.
Currently it is growing like anything which impacts the performance... any best practices you have? please suggest

************************************
Every Dog has a Tail !!!!!

Post #1405283

anthony.green

Posted Thursday, January 10, 2013 2:41 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

Large log files do not impact performance.
What is the recovery model of the database?
What is you recovery point objective for the database?
Do you do transaction log backups?

Managing Transaction Logs - http://www.sqlservercentral.com/articles/Administration/64582/
Stairway to Transaction Log Management - http://www.sqlservercentral.com/stairway/73776/
Accidental DBA Guide - Chapter 8 - Large or Full Transaction Logs -http://www.sqlservercentral.com/articles/books/76296/

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1405288

OnlyOneRJ

Posted Thursday, January 10, 2013 2:50 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 09, 2013 9:02 AM
Points: 158, Visits: 443

Full Recovery model
yes, backup of Transacvtion Log is been taken.

I just want to set the log file size to 500 MB.. so that incase it exceeds it will overwrite the existing one...

************************************
Every Dog has a Tail !!!!!

Post #1405293

anthony.green

Posted Thursday, January 10, 2013 2:56 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

What?

Want to set the actual LDF to 500MB or the TRN backup to 500MB.

Log files are cyclical and will use the same log file until it maxs out the drive space, then it will move on to the next if more than 1 LDF file is given to the DB.
There is no performance benefit to having multiple LDF files, 1 LDF should be enough and ensure you manage it properly.

Post #1405297

OnlyOneRJ

Posted Thursday, January 10, 2013 3:04 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 09, 2013 9:02 AM
Points: 158, Visits: 443

Anthony yup i was talking abt LDF Files.

************************************
Every Dog has a Tail !!!!!

Post #1405299

anthony.green

Posted Thursday, January 10, 2013 3:07 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

As said, you will not get any performance benefit from limiting a LDF at 500MB and having it cycle to another LDF once that file is full.

Large LDF's do not cause a performance issue.

Post #1405300

GilaMonster

Posted Thursday, January 10, 2013 3:46 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:11 AM
Points: 37,724, Visits: 29,980

The log file is automatically reused. There are no settings or options you need to change to make this the case. If you're in full recovery model, a log backup is what marks a portion of the log as reusable, so if the log is not being reused as much as you like, make your log backups more frequent.

Please read through this - Managing Transaction Logs

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 #1405322

OnlyOneRJ

Posted Thursday, January 10, 2013 4:18 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 09, 2013 9:02 AM
Points: 158, Visits: 443

Thanks Anthony & Gail Smile

************************************
Every Dog has a Tail !!!!!

Post #1405334

Perry Whittle

Posted Thursday, January 10, 2013 7:34 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:46 PM
Points: 5,202, Visits: 11,156

runal_jagtap (1/10/2013)

yup i was talking abt LDF Files.

Yup???

You've been listening to Gail and Paul too much BigGrin

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" Wink

Post #1405445

Bhuvnesh

Posted Friday, January 11, 2013 1:40 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

anthony.green (1/10/2013)

Large log files do not impact performance.

A side question .
Does DML operations or index rebuild/reorg (or any other operations which grow the log file ) get impacted DURING the log file expansion(adding space to it original size) ?

-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1405817

« Prev Topic | Next Topic »

Permissions