January 10, 2013 at 2:25 am
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 !!!!! :-D
January 10, 2013 at 2:41 am
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/
January 10, 2013 at 2:50 am
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 !!!!! :-D
January 10, 2013 at 2:56 am
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.
January 10, 2013 at 3:04 am
Anthony yup i was talking abt LDF Files.
************************************
Every Dog has a Tail !!!!! :-D
January 10, 2013 at 3:07 am
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.
January 10, 2013 at 3:46 am
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[/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
January 10, 2013 at 4:18 am
Thanks Anthony & Gail 🙂
************************************
Every Dog has a Tail !!!!! :-D
January 10, 2013 at 7:34 am
runal_jagtap (1/10/2013)
yup i was talking abt LDF Files.
Yup???
You've been listening to Gail and Paul too much 😀
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 11, 2013 at 1:40 am
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----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply