Transaction Log backup error (sql 2000 - no service packs)

Question

Post reply

Transaction Log backup error (sql 2000 - no service packs)

AaP-385828

SSC Enthusiast

Points: 136
More actions
December 7, 2006 at 12:52 pm

#170647

Hello everyone. I'm an extreme newbie.
I've inherited an old old NT machine running SQL 2000 with no service packs and have discovered some bad news on it of course.
The Transaction Log back-up has been failing for the past 5 months at the very least. I've discovered that this could possibly be from not having the service packs up to date, but I'm not certain : http://support.microsoft.com/kb/902388
Event Viewer :
"Event ID:208
SQL Server Scheduled Job 'Transaction Log Backup for DB Maintenance Plan 'DB Maintenance Plan2" (0x08A1EDFF61442945B00E86ED9D69A26C) - Status: Failed - Invoked on 2006-12-06 01:00:00 - Message: The job failed. The Job was invoked by Schedule 8 (Schedule 1). That last step to run was step 1 (Step 1)."
Database Maintenance Plan History Entry for DB Maintenance Plan2 :
"Status - failed
Duration - 0 seconds
Message - Backup can not be performed on this database. This sub task is ignored."

Can anyone share any experience with solving this ? Thanks!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohen Old Hand Points: 330 More actions · Answer 1

Try running a regular backup log command and post the error you get.

Syntax:

backup log [your db name]

to disk = 'your backup file path'

with init, format

Adi

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 2

What is the 'recovery mode' of the database ?

If it is 'simple' recovery more, then transaction log backups to a file are not possible.

If it is any other recovery mode (full or bulk-logged) then you must first have a full database backup performed before any other subsequent transaction log or differential backups are performed.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

MohammedU SSCoach Points: 19644 More actions · Answer 3

A transaction log backup job that is created in Database Maintenance Planner fails to execute

http://support.microsoft.com/kb/303229

BUG: Expired Transaction Log Backups May Not Be Deleted by Maintenance Plan

http://support.microsoft.com/kb/303292

MohammedU
Microsoft SQL Server MVP

AaP-385828 SSC Enthusiast Points: 136 More actions · Answer 4

Thanks for all the input.

Rudy, I havent been able to access the SQL Server in question, so I will have to wait a while before I can confirm the recovery mode status.

From the evidence I have, the recovery mode must be set to 'simple'.....I say this because :

1) a full backup occurs at 3am.

2) a transaction log backup is set for 2:30am and has always failed

3) 5 months ago the same transaction log backup was set for 730pm and still failed

Would this not indicate that it is set to 'simple' recovery mode ?

If I were to change the database recovery mode to 'full' what problem may I encounter ?

or would changing the time for the transaction log backup to 3:30am solve this failure ?

MohammedU SSCoach Points: 19644 More actions · Answer 5

First try to figure it out what is causing this failure...

1. Run tlog backup in a Query Analyser window and see that error do you get..

Backup log <database name> to disk = 'c:\dbname.trn' with init

If get the following error message when you run the backup log in QA then your recovery model is simple..

Server: Msg 4208, Level 16, State 1, Line 1

The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.

Server: Msg 3013, Level 16, State 1, Line 1

BACKUP LOG is terminating abnormally.

You can change the recory model to FULL using ALTER DATABASE or modify the maintenace plan to not to run tlog backup...

MohammedU
Microsoft SQL Server MVP