September 19, 2007 at 10:30 am
Hi,
Recently we faced a problem of a 300 MB database with a 120 GB log file!!. Is there anything wrong if I truncate the transaction log.. The command which I used is
backup log <dbname> with truncate_only
Afterwards I shrinked the logfile using dbcc shrinkfile command.
I have read somewhere that truncating the log file is not advisable and incase if we do so we have to take a full backup immediately.
Why is it so and what is wrong if we truncate the log?
Thanks and Regards
Sandhya
September 19, 2007 at 10:56 am
Well - I find this syntax confusing - so let's talk about it for a minute. Truncation in this case is the process of freeing up space in the physical log file. Transactions that have been fully done, and committed are what get deactivated/freed up when logs are truncated. Freeing it up means you can use it for something else coming, so that's a good thing. But if your recovery model is set to FULL or BULK-LOGGED, you want to keep a copy of the transactions (what was changed, etc...) in case you need to do a "replay of the transaction logs" which is usually how you get from your last full backup to now.
Here's where it gets a little confusing:
BACKUP LOG <dbname> with TRUNCATE_ONLY doesn't "back anything up". Meaning - it ONLY does the truncate, so your ability to "replay transaction logs" since the last backup is gone.
On the other hand, if you use the "other" BACKUP LOG:
BACKUP LOG { database_name | @database_name_var }
TO <backup_device> [ ,...n ]
[ <MIRROR TO clause> ] [ next-mirror-to ]
[ WITH { <general_WITH_options> | <log-specific_optionspec> } [ ,...n ] ]
[;]
as long as you don't specify a NO_TRUNCATE option, the backup process will actually back up the committed transactions AND deactivate them (truncate them).
So - most times you DON't want to run truncate_only against anything other than DB's in simple mode. If you have to - just be aware that you will be running without that transaction log safety net until your next full backup completes successfully.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 19, 2007 at 11:32 am
It sounds like your only running FULL backups with a full recovery model. This will not clear the transaction log.
You should either add transactional backups to your backup routine or change your recovery model to simple.
September 19, 2007 at 7:26 pm
David and Matt are right TRUNCATE_ONLY will not backup the logs. I do not know if this is the best way but if your in full recovery model other than full back up try to make a transactional backups (different from full and differential backups) for you to attain a poin-in-time recovery before you truncate the tlog so the setting is that you truncated the log but you assured that before truncating it you have a copy (FULL&Transactional backups or if you want also add differential) of the log you truncated. remember backup first before truncating if you want to have a backup of the log incase disaster occurs that is for full recovery model because tlog backup is a must. Simple recovery model is another issue, if you care less about your tlogs then you can set your database recovery model to simple for the tlog to "auto truncate" yeah as the word implies it will truncate the log automatically (as said also by other DBAs) but be aware that at this type of recovery model you cannot do transactional backups and i think you also cannot manually truncate the log (since it is set to automatic). THe choice is yours it depends upon what your company/database needs and how critical your databases are.
"-=Still Learning=-"
Lester Policarpio
September 19, 2007 at 11:39 pm
Thank you very much for all your efforts to make my concept clear... But there are a few things still confusing me..
Taking the same scenario which I have explained in my last mail of having a 300 MB Database with a 120 GB log file.. we were running out of space and literally I can say 0% space left. To add up the fire there were no other drives shared for us with that server. To free up the space the only optioon left out was to shrink the log file.
1. As we cannot afford a log backup (as there is no freespace left) taking a full backup and then truncating the log and shrink the logfile is a safe method?
2. Does a job to take the transaction log periodically (say in every one hour) will stop transaction logs growing exorbitantly?
Many thanks in advance
Sandhya
September 20, 2007 at 12:34 am
1. If you take the full backup and truncate the transaction log without taking its backup you can recover your database only to the point of full backup and not upto the point of crash or data loss whatever be the case.If this level of recoverability is Ok with you then It is safe otherwise it is risky.
2.Yes if you overwrite yesterday,s transaction log backup files with today's files.But keep in mind that you need to have an unbroken chain of transaction log back ups since your last full back up so also take a full backup everyday with overwriting it on last day's full backup.
regards
September 20, 2007 at 2:52 am
It seems like the question is more: Which recovery model do you curerntly have? And which recovery model is it that you 'should' have? This question needs an answer before you know what sort of backups to do and when..
Go to BOL and search for 'Selecting a recovery model' - there you can read about what options there is.
When a model is selected, then we'll know what to be done.
/Kenneth
September 20, 2007 at 8:10 am
1. As we cannot afford a log backup (as there is no freespace left) taking a full backup and then truncating the log and shrink the logfile is a safe method?
If you're okay with the possibility of NOT being able to recover up until recently, but only being able to resture until say - last night's backup, then it's a "safe" method. But - that's a business decision you and the "owners of the data" need to make. If the data is critical and you couldn't "live" with the possibility of losing a day's worth of data - then no, it's not safe.
2. Does a job to take the transaction log periodically (say in every one hour) will stop transaction logs growing exorbitantly?
Yes, the growth in that file would stop. But the backups would be backing these up somewhere, so you'd have to have a solution to get those off of your machine.
The bigger question you might need to tackle is - why is that file getting so big so quickly? Sometimes a "bad plan" or a not so great stored procedure, or uncommitted/orphaned transactions can cause this thing to balloon like that.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 20, 2007 at 8:28 am
A lot of this is good conversation but this guy seems to be in a tight spot. If I were facing the situation you describe I would:
1. take a full backup
2. truncate the log as you've described
3. shrink the log file. I'd expect that will take a while your users might feel some pain
4. take another full backup once that finishes
5. Determine the correct recoverablitity level and put in the backup routines to support that.
Good luck,
September 20, 2007 at 9:44 am
David.. thank you very much explaining what exactly I was desperately looking for !!! But all you guys were too good good and the concepts are crystal clear now !!! Once again thanks for all your time..
September 26, 2007 at 3:42 am
Firstly – Thanks! I have a similar problem (98GB Tlog file spanned over two disks) and this thread has helped greatly.
I’ve backed up the database and tlog file to tape (due to a lack of disk space), and specified the “Remove inactive entries from the tlog” option.
My question is; Does specifying the “Remove inactive…” option truncate the log file in the same way as running the TRUNCATE_ONLY command?
If is does I intended to use the “shrink actions” from Enterprise Manager to shrink the log file.
I'm very new to SQL and the database I've inherited is business critical so I have to tread carefully!
Thanks in advance.
September 26, 2007 at 10:53 am
Well -
TRUNCATE_ONLY is the syntax applied when you don't want the log actually backed up (you just want the truncate, not the backup). That's why I find the syntax of BACKUP LOG WITH TRUNCATE_ONLY so confusing.
However - if the question is - If I use the backup to tape, with the "remove inactive.." option on, does it truncate, then the answer is yes.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 27, 2007 at 1:10 am
Thanks Matt. That's exactly what I wanted to know.
October 2, 2007 at 1:14 am
Hmm,
I've backed up the tlog and ran shrink options: "Compress pages & truncate free space from end of file", and, "Truncate free space from the end of file", I then backed up the tlog again. The space used has decreased but the file size has not changed.
What am I missing?
One suggestion I have received is to change the backup mode from Full to Simple. Using a Simple backup mode isn't out of the question, my only reservation is not knowing if switching modes will have any adverse effects on the database.
Again, Thanks in advance.
Rich
October 2, 2007 at 2:01 am
Rich (10/2/2007)
Hmm,I've backed up the tlog and ran shrink options: "Compress pages & truncate free space from end of file", and, "Truncate free space from the end of file", I then backed up the tlog again. The space used has decreased but the file size has not changed.
What am I missing?
One suggestion I have received is to change the backup mode from Full to Simple. Using a Simple backup mode isn't out of the question, my only reservation is not knowing if switching modes will have any adverse effects on the database.
Again, Thanks in advance.
Rich
Truncate = decrease space used
Shrink = decrease Transaction File Size (total)
I suspected that the 1 you did is truncate not shrink
Scenario From simple to full recovery:
Recovery model will not take effect not unless you did a FULL Database Backup after changing from simple to full recovery
"-=Still Learning=-"
Lester Policarpio
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply