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

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

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.

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

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.

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.

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

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

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.

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.

quote:Originally posted by Roji. P. Thomas

Are you refering to this entry?

8 Steps to better Transaction Log throughput

Roji. P. Thomas
SQL Server MVP
http://toponewithties.blogspot.com

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 />

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