July 26, 2008 at 9:15 am
1) BACKUP LOG TestDB WITH TRUNCATE_ONLY will break the LSN.
I understand that we have to take the fresh complete backup after running the above SQL statement otherwise we can't restore T-log backups which are taken after log truncation.
Rajesh Kasturi
July 26, 2008 at 9:54 am
Truncating the log breaks the log chain. Once you're run it, you can't take any more log backups until you take a full or diff backup. They fail with the error
Msg 4214, Level 16, State 1, Line 1
BACKUP LOG cannot be performed because there is no current database backup.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
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
July 26, 2008 at 9:55 am
correct
---------------------------------------------------------------------
July 26, 2008 at 10:05 am
If you have a question, please list it. We do not want statements only posted here. You should have asked if what you think is true and not just left it as a statement.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
August 3, 2008 at 11:35 am
Thanks for the reply, So if the database is configured for log shipping or mirroring, after the log truncation we should take complete backup or diff backup other wise log shipping will fail.
again thanks for you reply.
Rajesh Kasturi
August 3, 2008 at 11:51 am
begs the question,
if you're implementing log shipping, why are you truncating the log?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 3, 2008 at 12:03 pm
valid question, Assume that My log shipping backup job configured to run for every 4 hours. Assume that 1:00 AM 5:00 AM like that, my log file was placed on d drive and d drive capacity is 10 GB
between 1:00 AM and 5:00 AM, REBUILD INDEX or any activity which causes the log file growth, unexpectedly my log file occupied 9.5 GB so I have only 500 MB free space. Assume that there is another database .mdf file was placed on the same drive..
Now my data file can't grow ...
at this point I believe only the way is I should truncate the log file, to get free space on D drive and allow other data files to grow..
Rajesh Kasturi
August 3, 2008 at 12:23 pm
After log truncation I did T-Log backup I got the below message
There is no current database backup. This log backup cannot be used to roll forward a preceding database backup.
Processed 2 pages for database 'testdb', file 'testdb_log' on file 2.
BACKUP LOG successfully processed 2 pages in 0.631 seconds (0.021 MB/sec).
Rajesh Kasturi
August 3, 2008 at 1:45 pm
by truncating the log you have broken the lsn chain. You will be able to backup the log again but as the message says you wont be able to roll forward through the logs for recovery purposes, you will need to take another full backup to start the lsn chain again.
You have also broken log shipping as you cannot roll forward through the logs as there are missing lsns, so you will need to reinitialise logshipping via full backup restore on the secondary.
---------------------------------------------------------------------
August 4, 2008 at 5:12 am
An option might be to change the Recovery model of the database to Bulk Logged before you rebuild your indexes, and then change it back to full once that's completed. This will mean that not as much gets logged in the TL when you rebuild your indexes so it shouldn't grow quite so much, but you can still perform your transaction log backups. If you still have a problem with the physical size of your TL once you've done your index rebuild then backup the log and use dbcc shrinkfile to free up some space - or ask for a bigger D: drive 😉
August 4, 2008 at 8:57 am
The error message you posted suggests that you are using SQL Server 2000, not 2005. SQL Server 2005 is 'smarter' in that it will prevent you from making a useless transaction log backup. Not so with SQL Server 2000.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
August 4, 2008 at 1:29 pm
I always fight with system and network engineers to add more space, they never understand DBA problems.
Rajesh Kasturi
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply