May 11, 2009 at 8:00 am
I was curious as to the inadvisability of using NO_LOG/TRUNCATE_ONLY with BACKUP LOG. I know that if this is done and something happens afterwards to the database, you've pretty much screwed the pooch on that one (pardon my french). This link explained it pretty well.
However, sometimes you have no choice. This past weekend, we had a transaction log backup to over 9Gig (normally hovered in the 200Meg range). Because of this, it filled enough of the drive so the next full backup would keep failing. I had two options, the first being to delete that big backup, do a BACKUP LOG with NO_LOG, then IMMEDIATELY do a full backup and cross my fingers.
My second choice, which I went with in order to keep some stress off, was to move the large transaction backup to another drive and immediately do a full backup.
I guess my question is, if I do a full backup immediately after a BACKUP LOG with NO_LOG/TRUNCATE_ONLY, does it matter as opposed to what I ultimately did?
Thanks.
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
May 11, 2009 at 9:56 am
After truncating the log using either NO_LOG or TRUNCATE_ONLY, the changes recorded in the log are not recoverable. For recovery purposes, immediately execute BACKUP DATABASE to take a full or full differential backup.
Although this option can be used to manually truncate the transaction log, it's strongly recommended that you do not do this, as it breaks the log chain. Until the next full or full differential backup, THE DATABASE IS NOT PROTECTED FROM MEDIA FAILURE. Use manual log truncation IN ONLY VERY SPECIAL CIRCUMSTANCES, and create backups of the data immediately.
May 11, 2009 at 2:48 pm
I am not clear why you felt it was necessary to truncate the log. If you run out of disk space to perform the backup - the backup job will fail and the transaction log will continue to grow.
By truncating the log - all you have done is to allow the transaction log to be cleared so the space could be reused.
Are you saying that you ran out of space on the drive where the transaction log exists, and that the transaction log could no longer grow? And this was caused because you could not backup the transaction log anymore?
No matter what the case, if you are running out of disk space and have to resort to truncating the log to get the system back up and available - I have to recommend that you consider purchasing more disk space.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply