September 23, 2008 at 9:09 am
I have a 40G database in full recovery mode and every Sunday night i rebuild indexes right before a full backup.
I also have Tlog backups every 6 hours.
Every Monday morning at 6AM i get a tlog which is the same size as the full backup.
So right after rebuild index i created a step to truncate log with truncate_only to get rid of that 40GB Tlog.
And now i get "BACKUP LOG cannot be performed because there is no current database backup" every time there is a Tlog backup.
Someone from MSDN forum suggested that:
In SQL2005, we've changed it so you are aware that your previous db backup can not be used to rollforward with the logs (because the log chain was destroyed by the truncation).
The correct way to discard the log backup chain is to switch to the SIMPLE recovery model. Once you've decided to resume log backups, switch to FULL recovery model, take a full or differential backup and then start taking log backups again.
Our system has 20 inserts a second.
Will i lose those 20 inserts during the switch between full and simple recovery modes?
Has anyone come across this problem?
Any help appreciated.
September 23, 2008 at 9:20 am
As soon as you truncate the log you've broken the log chain. Any log backups taken after that point are useless and the log records will be automatically discarded (when the checkpoint runs) until another full/diff backup runs.
You can leave the truncate in and take a full backup immediately after the truncate runs to restart the log chain. It is important that you do so, as you cannot restore to a point in time after truncating the log unless you take a fill/diff backup
Another option is that you switch to simple recovery for the duration of the index rebuilds, switch back after they've completed and then take a full/diff backup for the same reasons as above.
Last option (which I would suggest) is that after the index rebuilds you backup the log normally. Accept that the log backup will be big and make accommodations for a large backup file.
Switching recovery model doesn't take the DB offline and you won't lose any transactions while switching.
Two things to be aware of.
Backup log .. with truncate only is deprecated in SQL 2005 and does not work in SQL 2008
If you're in simple recovery, you cannot do point in time recoveries. So if the DB fails during the index rebuild you can only restore to the point of the last log backup you took before entering simple recovery.
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
September 23, 2008 at 9:43 am
GilaMonster (9/23/2008)
You can leave the truncate in and take a full backup immediately after the truncate runs to restart the log chain. It is important that you do so, as you cannot restore to a point in time after truncating the log unless you take a fill/diff backup
I had a truncate log step right after full backup.
Thank you i will change the step to execute after rebuild indexes.
September 23, 2008 at 9:51 am
AlexSQLForums (9/23/2008)
I had a truncate log step right after full backup.
You need the full backup after the truncate to restart the broken log chain.
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
September 23, 2008 at 11:07 am
GilaMonster (9/23/2008)
AlexSQLForums (9/23/2008)
I had a truncate log step right after full backup.You need the full backup after the truncate to restart the broken log chain.
I do have full backup after rebuild index step.
I just moved the truncate Tlog step right after rebuild index step and before full backup step.
Thank you
September 23, 2008 at 8:51 pm
Our system has 20 inserts a second.
Your post suggests that your database is still active when the index rebuild is running. If so, you should also consider if you will ever need to restore to a point in time during the index rebuilds.
Also, given that you back up your transaction log only every 6 hours (which is a bit infrequent for a database that has 1200 inserts a minute, but to each his own), you should at least back up your transaction log immediately before the index rebuild if you do not already do so. This provides you with more recovery options.
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply