Transaction Log does not back up

[ewin740]

I set-up a DB Maintenance plan to do both complete back up and transaction log backup. DB backup is daily every 3 hrs while transaction logbackup is sched daily every 2 hrs.
I only see .bak files created but no .TRN files.
Looks like the transaction log backups are not happening.
any ideas?
This is a machine with SQL 2000 (SP3) on Windows 2000 server.

 

[ptheriault]

Are they in the same maint plan? If so that is incorrect. you need a separate maint plan for tlog backups.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ewin740]

Thanks! my bad, both were set-up in the same db maint plan.

I've just isolated the transact logs maint plan by itself.
Do i have to re-start any services for this stand alone transaction logs maint plan to take effect?
Thanks again.

 

[ptheriault]

>>Do i have to re-start any services for this stand alone transaction logs maint plan to take effect?

No, just schedule it.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ewin740]

I don't know what else i've missed.
I scheduled and waited but still no .TRN files transaction log back up generated.
The database size to backup is about 6 Gb.

 

[ptheriault]

If you right click on the job and select view job history what does it say? When was the last time the job ran. If it hasn't run, then right click on the job and select start job.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[SQLBill]

What database(s) are you trying to do transaction log backups of?

The system databases should be left in SIMPLE mode.

We set up maintenance plans to do our backups on SQL Server 2000 and for those databases where we do transaction log backups we don't make separate plans. But we also have SP4 installed, which might make a difference.

With 2000 and 2005, I have found that if a maintenance plan doesn't work, the best thing to do is delete it and recreate it. That ususally will resolve the issue.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[ewin740]

The job history shows this error message:

Executed as user: sa sqlmaint.exe failed SQL STATE 42000 [Error 22029]. The step failed.

 

[ptheriault]

As Bill has eluted to you can't backup a tlog when the database recovery model is set to simple. Check that you are not trying to backup a tlog where a db recovery model is simple. To do that, right click on the database and select properties -> options. If the db is in Simple Recovery model remove it from your maint plan and try it again.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ewin740]

Thanks again. my user dbs were indeed set to recovery model: simple
I changed those to Full and now i'm able to back up tlogs!
awesome!!

 

[SQLBill]

Next time a job fails, 'check' the box for Show job steps. That will show all the steps for the jobs and any error messages for specific steps. Next look in the SQL Server Error Logs, usually you can get more detail there.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875