SQL Transaction Log Backups

[madeinwales]

I am currently running a daily maintenance plan which involves backing up the transaction logs every 2 hours between 07.00 and 19.00. For some reason every day the plan fails at 17.00 with the following message in event viewer. It doesn't matter what time I restart the Plan it always fails on the 17.00 backup.

Win 2000
SQL 2000

Event Type: Warning
Event Source: SQLSERVERAGENT
Event Category: Job Engine
Event ID: 208
Date: 04/09/02
Time: 17:00:17 PM
User: N/A
Computer: TETRA
Description:
SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan 'DB Maintenance Plan1'' (0xDC2C8787056FB24BBBD6173A039FF6B5) - Status: Failed - Invoked on: 2002-09-04 17:00:00 - Message: The job failed. The Job was invoked by Schedule 3 (Schedule 1). The last step to run was step 1 (Step 1).

 

[madeinwales]

Change to above: It is now failing at random times.

 

[jester777]

Does your maintenance job write a report file? Usually that is the best place to look for reasons of failure. If it doesn't then change the job to generate a report file.
Does the SQL Errorlog contain any info on the job failure?

 

[madeinwales]

Thanks. Was trying to back up transaction logs for Master, MSDB and Pubs which apparently isn't allowed.

 

[jester777]

You cannot backup the transaction log of any database which has got the 'Truncate Log On Checkpoint' option set. By default those system databases have this option set.

 

[madeinwales]

Having done all that and got the backups running it is still cancelling the schedule after the 17.00 transaction log backups. There is nothing in the SQL Logs, the maintenance plan history or the Event Viewer to explain why it is stopping. The 17.00 backup seems to have run fine.

 

[tlbroadbent]

Change the ending time of the schedule from 19:00 to 19:02 or some other time later that 19:00. The last run may not be starting because the end time has been reached and that fact takes precedence over the scheduled 7:00 pm start time. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[madeinwales]

I have tried redefining the schedule to run from 08.00 to 18.00 with 2 hourly backups. This has run correctly up to and including the 16.00 backup but now the schedule is disabled again.

 

[tlbroadbent]

You missed the poin of my last post. Change your new schedule to 08:00 to 18:02. Make the ending time slightly after the last start time of the day.

If a job is scheduled to start at 18:00 but the schedule ends at 18:00, the agent will probably not start the last run. That appear to be what is happening. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[madeinwales]

I have set that up now, we'll see what happens tomorrow. Thanks for your perseverance.

 

[etolentino]

Set the Recovery in the Options to Full instead of Simple.

 

[madeinwales]

What does that do?

The other solution seems to have worked, thanks.

 

[tlbroadbent]

Setting the Recovery Model to FULL would have no impact on the problem. The recovery model option affects performance, disk space usage and recoverability or protection against data loss. See BOL for more explanation.

http://msdn.microsoft.com/library/en-us/adminsql/ad_bkprst_4l83.asp

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.