September 11, 2009 at 8:03 am
Hi,
I have a job that does the index rebuild on one of our prod databases and once the job has completed I see a failure on transactional log backups .
Steps:
Take a full backup before rebuild indexes job run
Here is the sequence of job steps in index rebuild job:
Take the db to Simple recovery model
rebuild the indexes
Update the statistics
Take the db back to Full recovery model
After the above job steps , I have a full backup scheduled as one different job , which had failed and also resulted in the
failure of transactional log bkp for 3 times with the following error:
"BACKUP LOG cannot be performed because there is no current database backup. BACKUP LOG is terminating abnormally"
Later job taking the transactional log bkp ran succesfully and never failed.
September 11, 2009 at 8:13 am
Take a full backup before rebuild indexes job run
Take the db to Simple recovery model
rebuild the indexes
Update the statistics
Take the db back to Full recovery model
Chage the sequence a bit... the error message is telling you that you can't do a transaction log backup without first having a full backup.
The switch to simple is invalidating the full backup you took at the start.
Take the db to Simple recovery model
rebuild the indexes
Update the statistics
Take the db back to Full recovery model
Take a full backup
September 11, 2009 at 8:16 am
Trooper (9/11/2009)
Hi,I have a job that does the index rebuild on one of our prod databases and once the job has completed I see a failure on transactional log backups .
Steps:
Take a full backup before rebuild indexes job run
Here is the sequence of job steps in index rebuild job:
Take the db to Simple recovery model
rebuild the indexes
Update the statistics
Take the db back to Full recovery model
After the above job steps , I have a full backup scheduled as one different job , which had failed and also resulted in the
failure of transactional log bkp for 3 times with the following error:
"BACKUP LOG cannot be performed because there is no current database backup. BACKUP LOG is terminating abnormally"
Later job taking the transactional log bkp ran succesfully and never failed.
I am sure that you know that when you change the recovery model, to simple, you break the backup chain and you need to do a full backup again, before you can do a log backup.
To clarify, if you recovery model is full and then you change it to simple and then back to full, at that stage you will need to do a full backup again before you can do a backup log.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 11, 2009 at 8:16 am
Forgot to also mention... switching to simple mode stops your ability to restore transaction logs beyond that point. So any user changes between that point and when you take your next full backup are at risk.
If the maintenance is being done while there are no users on the system, then ignore what I've said.
Edit.
Silverfox... looks like yours and my entries got crossed in the post.
I'm not just repeating what you say... honest.:-P
September 11, 2009 at 8:21 am
In my case the full backup failed yesterdy night and I had the full backup taken this morning.....
So will I not be able to restored the transactional log backups along with the full backup which was taken before the db was taken to Simple recovery model and then indees were rebuilt?
Thanks.
September 11, 2009 at 8:23 am
Ian Scarlett (9/11/2009)
Forgot to also mention... switching to simple mode stops your ability to restore transaction logs beyond that point. So any user changes between that point and when you take your next full backup are at risk.If the maintenance is being done while there are no users on the system, then ignore what I've said.
Edit.
Silverfox... looks like yours and my entries got crossed in the post.
I'm not just repeating what you say... honest.:-P
LOL, you must have seen my other post on that subject then 😛
I will not post anymore in this topic and let Mr Scarlet answer this, dont want to be accused of cross posting 😀
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 11, 2009 at 8:34 am
Trooper (9/11/2009)
In my case the full backup failed yesterdy night and I had the full backup taken this morning.....So will I not be able to restored the transactional log backups along with the full backup which was taken before the db was taken to Simple recovery model and then indees were rebuilt?
Thanks.
I'm not sure I fully understand the question here.
Have a look at Gail's blog article on this subject, she manages to explain things a lot clearer than I would
http://sqlinthewild.co.za/index.php/2008/07/23/recovery-model-and-transaction-logs/
If you still have questions after reading that, post back on this thread and someone will endeavour to answer your question.
September 11, 2009 at 8:51 am
thank for the link......
I am not sure if I understand this correctly...
I have a fulll backup taken before rebuilding of the indexes.
Index rebuild job runs.
A full backup scheduled to run after rebuilding of indexes fail.
I have transactional log backups taken every 15 minutes...
so if I were to restore the db to the point in time when after the rebuild of indexes job run...
Will I be able to restore the full backup taken before the rebuild of indexes job run and then followed by all the transactional log backups taken to a point after the job has finished....
Will this break the chain of the transactional log backups???
September 11, 2009 at 8:52 am
Ian Scarlett (9/11/2009)
Trooper (9/11/2009)
In my case the full backup failed yesterdy night and I had the full backup taken this morning.....So will I not be able to restored the transactional log backups along with the full backup which was taken before the db was taken to Simple recovery model and then indees were rebuilt?
Thanks.
I'm not sure I fully understand the question here.
Have a look at Gail's blog article on this subject, she manages to explain things a lot clearer than I would
http://sqlinthewild.co.za/index.php/2008/07/23/recovery-model-and-transaction-logs/
If you still have questions after reading that, post back on this thread and someone will endeavour to answer your question.
I would read Gails article, but to answer your question briefly, you can restore the full backup and the transaction log backups following that backup, up to the point, before you changed the recovery model.
Changing the recovery model, stopped you from creating new log backups, requiring you to create a new full backup before you could create new log backups.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 11, 2009 at 10:46 am
Trooper,
Also be aware of the fact that breaking the log chain has potential issues that could be disastrous to your company. Here is an example:
Full backup at 1am yesterday
Transaction Logs every 15 minutes
Break Log chain at 12am (alter to simple recovery)
Full backup at 1am today
Transaction logs...
Now, you find out that you have to restore to a point in time just before 8am this morning. Should be no problem, however - when you try to restore from this mornings backup you find out that the backup is no good (I/O error, file corrupted, whatever).
So, you say okay - we'll go back to our previous backup and restore. Great - it restores just fine but you can't restore all of the transaction log backups to this morning. You are stopped at 12am this morning because you broke the log chain.
The point is simple - just because you have a completed backup does not mean that you are safe. Now, if you have a complete log chain - and all of the files are good - you can restore to a point in time from any known good backup.
I would recommend that you do not break the log chain unless absolutely necessary to fix an unusual situation. It should not be a scheduled activity...
If you are trying to manage the transaction log size during your index rebuild operation, alter the database to bulk logged mode instead of simple. This does not break the log chain - but, you cannot reorganize indexes (not a bulk logged operation) and you have to rebuild indexes, and - the transaction log backups will be very large during that time - but it is much better than breaking the log chain and putting your companies data at risk.
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
September 11, 2009 at 11:25 am
Thanks for the reply......
I might try taking the db to Bul logged and perform th erebuild of the indexes and then get it back to Full recovery model.
Hope everything goes well.
Thanks.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply