SQL Server Performance Forum – Threads Archive

Failing transaction log backups after full backup

it is problem while taking backups in 2005 sql…. i scheduled full backup at 9:00pm and tlog backup for every one hour..
after completion of taking full backup at 9:00pm it is taking the tlog backups at 10:00pm sucessfully but the same tlog job is failing from 11:00pm onwards by saying error there is no full database backup prior to this.i checked in that path for full backup it is there…strange i am unable to see any other error messange otjer than this…could anyone help from this issue
i am facing the same problem in many of other servers also which is havinng SP1 AND SP2

Are you making any changes to the transaction log (anything like truncating the log) in between these periods? Satya

Do have any non-logged operations or bulk inserts after 10pm and ensure the recovery model is set properly for such bulk loads. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Bulk operations should not cuase the problem…..
But your processes might be changing the database recovery model to SIMPLE and back to FULL ….
Can you post the exact error what you are getting… Run SQL Profiler/trace to see is there any recovery model change…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

I too had a similar problem long time ago and figured out that change in recovery model was breaking the log backups. Thats the most plausible reason

I don’t think I’m agreeing for this, unless you manage the recovery model from FULL to BULK vice-versa you are good to go.

quote:Originally posted by MohammedU Bulk operations should not cuase the problem…..
But your processes might be changing the database recovery model to SIMPLE and back to FULL ….
Can you post the exact error what you are getting… Run SQL Profiler/trace to see is there any recovery model change…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

OK [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] I have changed recovery model from Bulk to full and full to bulk many times without breaking the tlog sequence and it is doable. I have not seen the case changing the bulk to full or full to bulk break the tlog sequence… MohammedU. Moderator SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Yes Probably full-bulk is good, but in my case I noticed that somebody changed the recovery model of a database from full to simple and then I changed it back to full. The next T-Log backup gave me the same error

SIMPLE from BULK or FULL is nothing but chopping complete hands, thats why you had that problem, SIMPLE is not that simple to handle the log consistency. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by satya.sqldba Yes Probably full-bulk is good, but in my case I noticed that somebody changed the recovery model of a database from full to simple and then I changed it back to full. The next T-Log backup gave me the same error <hr height="1" noshade id="quote"></blockquote id="quote"> You guys may need to change the access to server if some changing the recovery models without knowing the consequences of it [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] MohammedU. Moderator SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

sorry guys……
i am not changing any recovery models for databases all databases are in full recovery maodel..and the transactions per hour is also too less….. Still i am getting the same error.there is a full backup and transactional backups is there in that path 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.

As I mentioned run the profiler and see what is happening….
You may not changing manually, it might be through scripts or jobs or some one else etc…. Here is the situation you may be encounterring… 1. Full backup 9:00 pm – good…
2. Tlog backup 10:00 pm – good..
3. Tlog backup 11:00 am – good…
4. Some process changed the recovery model from full to simple and changed it back from simple to full…
5. Tlog backup 12:00 am – FAIL…. After the recovery model changed from full to simple or simple to full you have to run the full backup again before running tlog backup. MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

See this relevant KBAhttp://support.microsoft.com/kb/928317 and also check do you have any truncate logs job in any case. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

]]>