transaction log backup failed 1

[maswien]

We have database to be backed up every day, and the transaction log backup every hour. The full database backup never have problem, but the transaction log backup used to work fine, but recently it keeps failing, nothing changed on the account or database, I don't know why. I copy the code in this job and paste it to QA and it runs OK without error. Any idea? Thanks.

 

[JayKusch]

Can you post the error you are receiving?

Sounds like a Rights issue on the share you are trying to backup the TLog to.

Thanks

J. Kusch

 

[bikerboy718]

Did you check the event log on the server? If so what error message is it logging?

With Great Power Comes Great Responsibility!!!

Michael

 

[maswien]

In event viewer or SQL Server log, the error message is nothing more than following:

"BACKUP failed to complete the command master..usp_BackupLog 'ivize' "

The destination for database backup file and transaction log backup file is the same, I don't think that's because write permission. Everytime when it fail, I can copy the command in the job and paste to the QA and it works.

 

[JayKusch]

It may very well be a permission issue. The rights that you use to get into Query Analyzer and run the job MAY NOT be the same right that are permitted on the account that is running the back JOB.

Thanks

J. Kusch

 

[maswien]

But the full database backup never fail, the account should be the same for transaction log backup

 

[SQLBill]

You cannot do a transaction log backup of the master database. The master database is ALWAYS in simple recovery mode.

Only do a full backup of the master database.

-SQLBill

Posting advice: FAQ481-4875

 

[maswien]

No it's not master database, it fails once a while, not always, I don't know the reason

 

[SQLBill]

Do you copy the backup to tape? If so, is it failing at the same time the tape is copying the backup file (both can't use it at the same time). I had this happen with a couple of my jobs at random times. The tape backup would kick off a little bit early and the SQL backup would fail.

-SQLBill

Posting advice: FAQ481-4875

 

[maswien]

SQLBill, you deserve a star!

You are right, because of the application upgrade, someone change the netbackup schedule on the server and forget change it back when the upgrade is done, it used to be in the midnight when there are no SQL backup jobs running.

 

[maswien]

I change the netbackup schedule to be not inflict with the SQL transction backup job, but the log backup job still fails. If I right click this job in EM and click "start job" then this job gets run successfully, but if schedule it at a time, it will for sure fail! What's the substantial difference between these two actions??? Eithor application log or sql server error log won't give me any clue on this.

Thanks, any idea on this will be very helpful!

 

[SQLBill]

1. when you click on it, you are running it as you
2. when you schedule it, you are running it as whomever the job is assigned to.
3. Check to see how long the netbackup job runs (start and stop times). Then make sure the SQL Server job isn't scheduled for that time frame.

-SQLBill

Posting advice: FAQ481-4875

 

[maswien]

It's OK now, what I did is just delete the problematic job and create a new job which run the exact same SQL command, from then on, it works fine. I don't know why, but it's good enough for me