Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 BACKUP failed to complete the command BACKUP LOG

Author  Topic 

negmat
Starting Member

20 Posts

Posted - 2006-11-30 : 10:32:13
Guys,

I have the following issue:

In my SQL Server Logs I got the following error message: 'BACKUP failed to complete the command BACKUP LOG "TestDB" WITH TRUNCATE_ONLY'.

I interpreted this message as "Backup failed for "TestDB" database"

When I checked the "Database Maintenance Plan History", however, it says that this database was backed up and the backup was verified.

Can anyone shed light as to when this message occurs and whether I am interpreting this correctly?


Thanks a lot

Kristen
Test

22859 Posts

Posted - 2006-11-30 : 11:20:26
Might be worth checking what MSDB thinks is backed up:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=54300#273265

I don't think that the Maintenance Plan would use "WITH TRUNCATE_ONLY", so I think its more likely that someone did that manually. Checking the time in the Log against the MSDB output might tell you something

Kristen
Go to Top of Page

negmat
Starting Member

20 Posts

Posted - 2006-11-30 : 11:44:06
The backup of this table, as identified by running the statements you pointed to in the link, was between 3:28 and 4:11 AM today. The error message, as mentioned above, occured at 4:05 AM.

As far as I understand, BACKUP LOG 'DB_NAME' WITH TRUNCATE command, gets rid of the inactive portion of the log.

Why could it fail?

Are there any known problems when this backup fails?


Thanks a lot
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-11-30 : 12:16:52
Do you mean it was running from 3:28 until 4:11? That's a long time for a TLog truncation ...

... perhaps it timed out, or was aborted, because something else was going on then too - full backup perhaps? Maybe it was a full backup running from 3:28 to 4:11? Even from 4:05 to 4:11 is a long time for TLog truncation.

Perhaps there are two separate maintenance plans and the backup is scheduled for 3:28 and the TLog for 4:05, but the backup is now taking longer and overrunning the start of the TLog backup?

Still ... that should only mean that the TLog backup sits and waits until the other backup finishes - although that may not be the case for a "WITH TRUNCATE ONLY" I suppose.

Try running it manually (presumably you don't want to backup the TLog??) and see what error you get.

Kristen
Go to Top of Page

negmat
Starting Member

20 Posts

Posted - 2006-11-30 : 13:40:18
Kristen,

With your help, I solved the puzzle. Here it is:

I have a Defragmentation Job which is scheduled to run at 4AM every day. The 2nd step of this job is "BACKUP LOG TestDB WITH TRUNCATE_ONLY".

I also have full backup of all user dbs scheduled at 3AM (backup of this db happened to start at 3:28AM). Most of the time db backup finished before 4AM (I learned this by executing the commands you specified in the link). Yesterday, however, and a few times before, the backup went past 4AM, conflicting with the 2nd step of the Defragmentation Job. This is why I got this error in my error log and also why "Defragmentation" job failed yesterday as well.


Thanks a lot for your help
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-11-30 : 15:18:43
To summarize:

3AM Full backup
4AM Defrag
4AM+ Truncate log

Not sure this is a good idea!

After 4AM+ you have no recovery path until your next full backup (3AM next day)

Is your database in SIMPLE or FULL Recovery Model?

If anything other than SIMPLE I reckon that your restorability is at risk.

4AM+ is shrinking the TLog file. Which is then re-extended during the day. And probably significantly further extended by the 4AM defrag.

Each shrink + extend is going to further fragment the file, which will reduce efficiency.

You would be much better off leaving the Log file at the size it naturally wants to be.

If that is too big, AND you are using SIMPLE recovery model then change that to FULL and make frequent TLog backups (e.g. every 10 minutes), which will prevent the TLog growing beyond the maximum it can need for 10 minutes work.

If you are already in FULL Recovery model then consider making the TLog backups more frequently, or reducing the size of a "batch" during database maintenance.

Kristen
Go to Top of Page

negmat
Starting Member

20 Posts

Posted - 2006-11-30 : 16:09:36
Kristen,

I am currently exploring how the backups work in our system. I inherited this set up and is thus unaware as to what recovery model is used (along with other backup-related issues) and why.

In the meantime, would you know of a good source/link describing good practices of backup, where I can get information about what to do and what not to do and why, as related to backups.

I might change things around here as long as I know that what I am doing will do more good than bad.

Thanks a lot
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-11-30 : 16:46:41
Well, you could try this:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=How%20Backup%20works,What%20type%20of%20backup%20should%20I%20use,Automating%20Backups,Backup

Kristen
Go to Top of Page

sql_er
Constraint Violating Yak Guru

267 Posts

Posted - 2007-02-02 : 11:00:40
Kristen,

Would the following solve his problem: 'Backup the log after the defrag'

Log backup will perform the truncation, but only after the log is backed up.

Thus, there will be no risk, no?

When you say "After 4AM+ you have no recovery path until your next full backup (3AM next day)", do you mean that the data between 3AM and the time that log truncation completes could be lost if the original approach is used and if some problem occurs?


Thanks
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-02 : 12:49:07
Yes that is true. This is why you should not truncate the log unless you are out of disk space. Once you truncate the log, you haven't broken the transaction log chain. So must perform a full backup to start the chain again. During the period of the truncate and the full backup, you will not be able to restore that data to a point in time. Truncating the log should be avoided!

Tara Kizer
Go to Top of Page
   

- Advertisement -