SQL Server Performance Forum – Threads Archive

Backup problem

Hello DBA’s I am having a strange rpoblem in T-log backups. This is my cycle: 1. Full backup at 12:00 A.M
2. T-log backups from 2:00 A.M to 10: P.M in the night for every 2 hrs.
3. full backup at 11:00 P.M
…
again full backup at 12:00 A.M. From Sat morning log backups have failed for all the 3 days.
I checked the error log and this is the error message: ‘BACKUP failed to complete the command BACKUP LOG "D/B name". Check the backup application log for detailed messages.’ When I tried to backup the log by issuing a query, the message says there is no current backup. But all the 3 days I am sure that the full backup at 12:00 A.M was success. When I tried running the full backup once again today followed by log backup, it was a success. Any idea why my log backups have failed for 3 days? Thanks
Satya

---

When I tried referring to the following link http://support.microsoft.com/default.aspx/kb/921106 it says that this problem may occur when the internal status flag for a full database backup does not reset after you create the full database backup. But this is mentioned with reference to diff backup. Does this happen in case of T-log backups too?

---

Query the system tables to see status of all backups..
Do you see the full backup file, if yes run the restore headeronly to see when it was completed and its details… select * from msdb..backupset
where backup_start_date > getdate()-3
order by backup_start_date desc MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

Yes Mohammed I have already looked into those and the I can see the backup files created that nght at 12:00. I am sure that all the 3 nights, the full backup was successful Thanks
Satya

---

Were there any non-logged operations or any bulk insert type of operations in between the database backups? 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. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

---

No Satya, There were no such operations. Just curious, would that affect this in any way? Was this problem beause of something like the internal status flag for full d/b backup not being reset? Thanks
Satya

---

Yes it will be mismatch the LSN between the log backups, if any of such process will attempt to change recovery model on the database will invalidate such log backups between the complete backups. 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. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

---

Tlog backups should fail even you had non-logged operations or full backup fails…
Did you try running the tlog backup in query window to a different drive… MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

See this KBAhttp://support.microsoft.com/kb/827452 in thsi 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. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

---

OK But the strange thing is I have 4 d/b’s scheduled in log backups and all gave me the same error that there is no current backup while the previous night’s full backup was success and the backup files are valid. Any other reason why this might have happened? Thanks
Satya

---

Checked the KBA above and see whether it meets this criteria? 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. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

---

Ya just looked at it I dont think my problem comes under that issue. To be more precise from my SQL log: Message
Error: 3041, Severity: 16, State: 1 It is some what near to the following one: http://support.microsoft.com/default.aspx/kb/921106
I am not sure if the above one is the problem with my case.

---

If it meets the specification on the above KBA then go and submit to obtain the hotfix, fyi there will be a charge for it. 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. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

---

Thanks for the info Satya But the KBA has the info with respect to diff backup. I was just wondering if that could even trouble the T-Log backup too. Anyway I will wait for 2 or 3 more weeks and if it repeats once again, I will try to apply the hotfix. Satya

---

For hotfix related to bugs….generally there will be NO charge….
Any idea why there will charge in this case? MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

Why don’t you reproduce the problem in the dev. or test environment to ensure that hotfix may no be required, to save another hassle if it occurs. Differential backups do not allow point-in-time or marked log recovery. Fyi

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. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

---

I have tried to see if the problem occurs once again.The past 3 days full backups have been successful and the log backups were failing. And this is the first time that this has occured in the past 1 month of log backups happening. But when I tried to run the full backup followed by log backup today morning everything was fine(it does’nt give me the error "current full backup does not exist"). Why is it that the backups at night that could not be recognised while the ones generated today morning recognised…that’s strange Thanks
Satya

---

Why are you running full backup at 11:00 pm and at 2:00 am?
Is it anything to do this it?
How much time each full backup is taking and what is the db size? MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

Mohammed The full backups are running once at 11:00 P.M in the night and once again in one hour at 12:00. I made sure the schedules are not clashing with each other. (The one at 11:00 is to have a particular set of d/b’s to a diff server. I did not want them to interfere with my main backup job that is scripted according to a policy of maintaining weekly,monthly etc..) It takes hardly 20 mins for the 11:00 P.M backups to complete and on the day the T-log backups started failing it was the same. I checked the SQL log too and made sure the scheduled did not clash with each other. Thanks
Satya

---

If additional issues occur or any troubleshooting is required, you might have to create a separate service request. The usual support costs will apply to additional support questions and issues that do not qualify for this specific hotfix.
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. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

---

Satya I just came to know from one of the developers that there was a bulk operation in one of the d/b’s from 5:00 P.M to 11:00 P.M on Friday evening. Would that have been the cause for the problem? The log backups started failing from 2:00 A.M Sat morning, but the 12:00 A.M full backup on Saturday was success. So If I am not wrong, the bulk operation is not related to the log backups failing. Correct me if I am wrong. Thanks
Satya

---

I don’t think non-logged/bulk operation cause this issue unless db option or recovery model changed… Is any one changed recovery model of the database? You must perform a full database backup before you back up the transaction log for a database in SQL Server 2005 and in SQL Server 2000
http://support.microsoft.com/kb/928317
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

Ok Mohammed That’s what my perception too was. It takes a valid full backup for the immediate log backup to be a success. Then my error seems to be a strange one. The full backup is success and the immediate T-Log backup is a failure saying that there is no current backup.(that repeated for 3 days because I did not check the jobs over the weekend). When I repeat the same process of taking a full backup followed by T-Log backup today morning it is success. I will wait and see if the error returns once again in the future… Thanks
Satya

---

NOn logged operations will invalidate the transaction log, minimal logging involves logging the minimal amount of information in the transaction log that is required to recover the transaction without supporting point-in-time recovery. 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. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

---

Yes Satya I understood what you said, but the full abckup at 12:00 A.M on Sat should be enought to take a log backup at 2:00 A.M on Sat while the bulk operations where between 5:00 P.M and 11:00 P.M on Fri Thanks
Satya

---

Satya,
Are you sure? NOn logged operations will invalidate the transaction log in sql 2005?
I think it was true in sql 70 but not 2000 and laster…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

I believe so, as I have discussed this with Kimberly Tripp earlier as she referred one of the blogs too. I will confirm it back after checking it again. 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. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

---

Are you refering to this entry? 8 Steps to better Transaction Log throughput Roji. P. Thomas
SQL Server MVP
http://toponewithties.blogspot.com

---

Mohammed and Satya I think I know where the problem is. There is also a step in the full backups that truncates the log files immediately after the full backup. It is causing the log to be invalid after the full backup. Correct me if I am wrong. If that is what causing the log backups to fail, what would be the best time to truncate the log files? Thanks
Satya

---

If you have the step to truncate the log then your log backup will fail and you should get the following error…
and you test with pubs db… 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. MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

NO where mentioned about log invalidation…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

Yes Mohammed, Thats the exact error I got, so these 2 things cannot happen together: truncating the log files and immediate log backups. I am thinking of a better strategy towards this, scheduling to truncate the log at a different time or not doing so frequently.

---

Why do you want to truncate the log?
If you truncate the log you are effecting your poin in time recovery… If you want to truncate the for any reasons and not worried about the recovery truncate the log before full backup….
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

Mohammed How would that effect point in time recovery in any way? I was truncating the log only after the full backup.Also I have the hard drive that has full backups and log backups backed up onto tapes on daily basis. So if I wish to go back I can, right? If I have the full backup and the succeding transaction log backups until the next backup (this happens on daily basis), I can go back to any point in time recovery. Would truncation of transaction log file effect this process in any way? Tell me if there is any flaw in my perception. Thanks
Satya

---

I don’t think you can use STOPAT option with full/diff backups incase if you want to restore your backup.. 11:00 pm – Full backup started at …
11:10 pm – Some delete the data
11:20 pm – Full backup completed
11:21 pm – Log truncation.
11:30 pm – realized the deletion and want to restore as of 11:09 pm. How do you do it? if you truncate the log.
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

Ok Got it, so the concern is with the transactions during the backup process. And yes I agree with u that we cannot do in point recovery with full/diff backup. But I can still do in point recovery in my case because I am having my T-log backups every 2 hours. But the situation you have picturised is crystal clear of how the truncation of log might effect in-point recovery, I was not thinking about such situations (transactions during the backup). Now I will have to think about something other than truncating the log daily. Thanks
Satya

---

You didn’t answer one more question [<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]<br />Why you want to truncate the log?<br /><br />MohammedU.<br />Moderator<br />SQL-Server-Performance.com<br /><br />All postings are provided “AS IS” with no warranties for accuracy.<br />

---

I never wanted to do that, my IT director wanted to. I was telling him about the consequences, but he was so much pressing on that to happen.

---

I know sometimes you get this type of managers specailly when they are half knowledge..<br /><br />Did he give any reason for that?<br /><br />Good luck [<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]<br /><br /><br />MohammedU.<br />Moderator<br />SQL-Server-Performance.com<br /><br />All postings are provided “AS IS” with no warranties for accuracy.<br />

---

He was concerned about the log growing too large and the system getting hanged. I told him that I could monitor the log usage and truncate when it really grows too large. BUt he was pressing me for the other way.

---

I don’t think any DBA would want to have the log files truncated immediately after a full backup, right? If so, what would be the best way to monitor the space the transaction log file occupies on the hard drive? Do you also suggest looking manually into how large is the log…I don’t think thr is any other way around to do this. Thanks
Satya

---

You can monitor the logs space use the DBCC SQLPERF(LOGSPACE);
command… http://msdn2.microsoft.com/en-us/library/ms189768.aspx MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

You did not answer my previous question: [<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]<br /><br />I don’t think any DBA would want to have the log files truncated immediately after a full backup, right?<br /><br />I think with all the log backups failing ,now I have enough explanation to convince him not to have the log files truncated daily… [V]<br /><br />Thanks<br />Satya

---

Yep, you got the evidence [<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />] prove him wrong…<br /><br />MohammedU.<br />Moderator<br />SQL-Server-Performance.com<br /><br />All postings are provided “AS IS” with no warranties for accuracy.<br />

---

I believe you have got most of it from Mohammed, as suggested attempting the truncate of log will have that mismatch that you have seen the consequences.<br /><br />Ask your Director to have a thorough read on BOL about transaction log architecture, in any case enusre to control the transactions, perform frequent log backups and if disk free space is not an issue set a sensible (high) size to transaction log to accomodate the activities on the day to day basis. If he still wanted to go in same route ask him to call us [<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />] for a brain wash.<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing. <hr noshade size="1">The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.</i></font id="size1"></font id="teal"></center>

---

Ok Mohammed and Satya<br /><br /> Thanks for ur suggestions [<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]<br /><br /> Satya

---

I implemented a work around for this. I have configured alerts to monitor the log space and execte a job of truncating the log files when they grow beyond a specified amount of space. I guess this is a good solution for my situation Thanks
Satya

---

Post the log space monitoring script, if any body wants use it and thanks for the feed back.
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

Ok Mohammed<br /> <br /> Here is the script of the alert that I have created:<br /><br />USE [msdb]<br />GO<br />/****** Object: Alert [Truncate log_AICETransfer] Script Date: 04/18/2007 16:00:23 ******/<br />EXEC msdb.dbo.sp_add_alert @name=N’Truncatelog_AICETransfer’, <br />@message_id=0, <br />@severity=0, <br />@enabled=1, <br />@delay_between_responses=0, <br />@include_event_description_in=1, <br />@category_name=N'[Uncategorized]’, <br />@performance_condition=N’MSSQL$SQLPROD02<img src=’/community/emoticons/emotion-2.gif’ alt=’‘ />atabases|Log File(s) Size (KB)|AICETransfer|&gt;|10000000′, <br />@job_id=N’449b07fe-f6c6-48c5-ba42-562b3d6216b6′<br />GO<br />EXEC msdb.dbo.sp_add_notification @alert_name=N’Truncate log_AICETransfer’, @operator_name=N’Satya’, @notification_method =1<br /><br />Hope this helps<br />Satya

---

]]>