April 6, 2009 at 10:47 am
i did read Gail's article on recovery modes. That was helpfull..But i think im still lil confused..from my understanding..if you keep database in full recovery mode you can take log backups...when the tlog file bcomes huge u can shrink it...when in simple recovery mode the log backups cannot be taken ..i read somewhere that" when in simple recovery mode the tlog file gets reduced after checkpoint"...i thought when it is in simple recovery mode the tlod will be shrunk after the checkpoint..but i think i am wrong..bcoz my db is in simple recovery mode and i still see log file increased to 65 gb..so my question irrespective of db mode if the log file bcomes huge the only way to reduce is through shrking right?...i know i cld add some more disk space for log file..thanks in advance..
April 6, 2009 at 11:18 am
Basically, in any recovery model Tlog can be reused. Tlog is divided into number of VLF's when one VLF becomes full it uses another VLF. For the sql server to use theses VLF's again, the records in the VLF's should be written out to disk so that, the tlog space can be reused what we call it as truncation.
In full recovery model these VLF's gets truncated only when you take a log backup
In simple recovery model theses VLF's gets truncated only when a checkpoint occurs.
So, if you don't take regular log backups these VLF's gets added to the tlog file and it grows and grows. That's why if your database is in full recovery model you should always take tlog backups. If you don't then the size of the log file is out of your control.
iqtedar (4/6/2009)
if you keep database in full recovery mode you can take log backups...when the tlog file bcomes huge u can shrink it...
It's not "you can" it is "you must", if you take regular backups the log file will not become huge. yes, only in some abrupt situations you should shrink the log file but not as a general practice.
..i read somewhere that" when in simple recovery mode the tlog file gets reduced after checkpoint"
No, it doesn't get reduced physically, only truncation happens. the VLF's in the log file are marked as Truncated to reuse them
...i thought when it is in simple recovery mode the tlod will be shrunk after the checkpoint..
No, sql server will never shrink the log file on its own, you need to shrink it manually(not recommended though)
bcoz my db is in simple recovery mode and i still see log file increased to 65 gb..
Why is it? What operations are you running against it? It shouldn't be if you manage it efficiently!!
so my question irrespective of db mode if the log file bcomes huge the only way to reduce is through shrking right?
Yes, but you need to be proactive. Tlog file will grow because it needs space, if it has grown it means that it needs that much of space. You can't do anything about it unless committing more frequently in your large transactions. you need to prepare yourself for more disk space.
...i know i cld add some more disk space for log file..thanks in advance..
There you go you found solution for yourself.
Shrink the log file to as much as you can. Monitor the growth and sit at some final value so you dont have to grow it again. Frequent shrinking and growing leads to fragmentation which will be a performance hit on your production server.
April 6, 2009 at 9:07 pm
thanks Krishna..things look much better now...krishna tday i changed the recovery mode to bulk logged mode and will monitor if that helps..thanks..
April 7, 2009 at 4:27 am
iqtedar (4/6/2009)
thanks Krishna..things look much better now...krishna tday i changed the recovery mode to bulk logged mode and will monitor if that helps..thanks..
No Problem.
Was your databases in Full Recovery model?, If it is not you dont have to change it to Bulk logged, Simple model would be suffice.
April 7, 2009 at 7:36 pm
krishna...this is my plan to manage the space for log files..there are couple of dbs in simple recovery mode and there log files are huge..i want to see wat size are they really capable of..this is wat i planned..first i will shrink the files to some size..like if a log file is 16 gb i will bring it down to 2 gb..monitor for 2-3 days...if it again goes like 16 gb..then there is no point in shrinking further... i would say then ok we will need 16 gb for this file..is this the right way to assign space?..Krishna is this called as capacity management?..thanks,,
April 8, 2009 at 3:47 am
iqtedar (4/7/2009)
there are couple of dbs in simple recovery mode and there log files are huge..
Did you identify why they are huge? I mean what operations are running against this database? any reindexing, batch loading etc.. keep a note of them..identify, isolate and troubleshoot
i want to see wat size are they really capable of..this is wat i planned..first i will shrink the files to some size..like if a log file is 16 gb i will bring it down to 2 gb..monitor for 2-3 days...if it again goes like 16 gb..then there is no point in shrinking further... i would say then ok we will need 16 gb for this file..is this the right way to assign space?
Yes as long as you know cons of doing it that's fine. squeeze it to as small as you can and then monitor the growth, make a graph, you should monitor it for coupld of days may be for a month depending no how busy your database is and then basing on those you settle at a figure.
..Krishna is this called as capacity management?..thanks,,
Yes part of. It is the process of preparing and forecasting future hardware needs, it is basically predicting future hardware needs so that's what you are doing now..
April 8, 2009 at 12:17 pm
thanks again..
April 8, 2009 at 12:21 pm
iqtedar (4/8/2009)
thanks again..
you are welcome, any problems give us a shout
April 9, 2009 at 8:48 am
krishna..im back with another question..krishna now i did shrink the log file from 49 gb to 2 gb...it is in simple mode..logs will be truncated after the checkpoint...i see the log file back to 9 gb..why?..if logs are truncate after the check point why is the size increasing?..thanks,,
April 9, 2009 at 9:22 am
Hi Iqtedar,
If your database is in Simple recovery model it doesn't mean that log should not grow it only means that the tlog will not fully record the transactions that are run against the database and the committed transactions are flushed whenever a checkpoint occurs.
What I would suggest is to monitor the activities that are run against this database. Any bulk loads, heavy jobs and also while inserting data, commit frequently which sql server will flush them at check point. If you have many transactions that are not committing yet then even at check point they sql server wouldn't be able to flush them as they are uncommitted.
You asked question for yourself? Why is my tlog large? so identify what are running against the database, once you identify isolate the problem in the sense the heaviest operation and then troubleshoot it.
Let us know
thanks,
April 9, 2009 at 11:11 am
krishna to better understand it..can i say that truncation means only flushing the committed transactions..so is it that truncation doesnt makes the log file empty?.i understand truncation will not resize..does it only flush the committed transactions..if yes how do i clear the uncommitted transactions...thanks for explaining me ..
April 9, 2009 at 11:05 pm
iqtedar (4/9/2009)
can i say that truncation means only flushing the committed transactions
No, truncating tlogs means marking VLF's in the log files that they can be reused.
..so is it that truncation doesnt makes the log file empty?
No, it does not as I mentioned before, it only makes it reusable (truncates-marks VLF's as truncated). so the log doesn't have to grow(which means it doesn't have to
how do i clear the uncommitted transactions
You cannot clear the uncommitted transactions. They will grow.
How do you have uncommitted transactions? this means that there are open transactions which will bloat your tlog file.
...thanks for explaining me ..[/quote]
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply