January 5, 2009 at 6:58 pm
we have a transaction log backup job runs every 30mins. it backs up 4 databases. once a while, we receive email saying that job failed. when I look at the job history. it shows failed too. however, sql server log did not show any error on that backup. I also check the backup directories (each database has its own subdirectory), all 4 TRN backup files are there. can anyone tell me why I am getting the job failed message?
our sql server is 9.0.3159 standard edition
here is the info in job history:
[font="Arial"]Date 1/5/2009 10:10:00 AM
Log Job History (User Databases.Transaction Log Backup)
Step ID 1
Server corp-prod\instan1
Job Name User Databases.Transaction Log Backup
Step Name Transaction Log Backup
Duration 00:00:06
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: DM\sqlsupper. ...sion 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 10:10:00 AM Progress: 2009-01-05 10:10:03.17 Source: {3B15AC30-CB78-4EA9-91F7-B73BD01FE008}
Executing query "DECLARE @GUID UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete End Progress Progress: 2009-01-05 10:10:03.41
Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'E:\C_PROD".: 25% complete End Progress Progress: 2009-01-05 10:10:03.41
Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'E:\C_PROD".: 50% complete End Progress Progress: 2009-01-05 10:10:03.41
Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'E:\C_PROD".: 75% complete End Progress Progress: 2009-01-05 10:10:03.41
Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_crea... The package execution fa... The step failed.[/font]Thanks in advance,
January 6, 2009 at 7:26 am
Can you get the full message?
Go to the SQL job, open the job step that's failing. On the 'Advanced' page you can enter a file name for the full output to be written to. That should help in seeing why the job's failing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 6, 2009 at 8:50 am
Thanks Gail. I will do that. It may take couple days to see the error again. I will post what I saw from the log file If I need more help.
January 6, 2009 at 1:02 pm
can you post details of the job steps?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 6, 2009 at 2:08 pm
Open the Maintenance folder in Object Explorer - right click on your maintenance plan and view history. This history will show you what step in the maintenance plan failed.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
January 6, 2009 at 8:23 pm
It shows the job was successful, no error from there at all. The error message I posted was from right click on the job name under job category. We also get email saying the job was failed. Is this a bug?
January 7, 2009 at 2:52 am
Perry Whittle (1/6/2009)
can you post details of the job steps?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 14, 2009 at 2:14 am
I am getting the same error - says the job fails, but the log files are created in any case.
Maintenance history shows it was successful but the job history shows it fails.
This is the error in the job history:
Description: The Execute method on the task returned error code 0x80131931 (Data is Null. This method or property cannot be called on Null values.). The Execute method must succeed, and indicate the result using an "out" parameter.
Has anyone come up with a solution yet?
January 14, 2009 at 8:09 am
This seems to be a common occurrence in SQL Server 2005 jobs. There seems to be a bug in the job handling mechanism that makes it think there is one more step in the job, even though there is not. The job fails when it tries to execute this (nonexistent) ghost step. Deleting and recreating the job doesn't seem to fix the problem. After checking several of our jobs where this happens over a period of time and observing that all meaningful steps were successful, I scripted the job and modified it to only do a notification on the failure of a real step, and not the entire job. This seems to be a valid workaround, but nevertheless, the bug is still there. So far, I haven't found a fix from MS for this.
June 8, 2009 at 5:23 am
I encoutered the same issue om my production server (SQL 2005).
To overcome the issue of receiving "failure notifications" from SQL Agent, I added 1 retry event under the Advanced properties of the job. This seems to sort the problem, but it will now create 2 transaction log files - 1 for when it said it failed but didn't, and then a second for the retry event.
What is the way to test these log files to see if they have correctly backed up?
June 9, 2009 at 8:14 am
Casper (6/8/2009)
What is the way to test these log files to see if they have correctly backed up?
The best test is to restore them. Lets you know that the files are good AND lets you test out, and perfect, a DR strategy.
-- You can't be late until you show up.
June 9, 2009 at 8:27 am
yeah I thought you might say that... 🙂
so this means I will have to restore a backup of the DB and then restore the log files after that?
June 9, 2009 at 8:58 am
Correct. As I stated, it'll also allow you to practice point in time recovery. You can also run RESTORE VERIFYONLY (see BOL) against the backup file which will also validate that the file is indeed usable without actually doing a restore.
-- You can't be late until you show up.
June 9, 2009 at 1:07 pm
Are you using MS native maintenance backup job right? Since you already saw those four dbs tlog backup generated on the disk but job still failed. I suspect it failed at when the job tried to remove old tlog backup files. Job history might not record this. You might check any other process holding any lock on the old tlog files such as your tape backup process.
hope it will give you some clues.
August 3, 2009 at 1:08 pm
Just wanted to say that this is happening to me as well. Here is my configuration in case anyone is interested. Unfortunately, no answer to the problem yet, but for the sake of completeness I'm offering this information to the masses. 🙂
Maintenance Plan to perform a total of 3 tasks:
1. Remove all .bak files older than 1 week
2. Remove all .trn files older than 1 week
3. Remove all .txt (maintenance logs) files older than 1 year
When I complete the wizard, this generates 2 different jobs:
1. Remove .bak and .trn files (maintenance plan shows this as 2 subplans, but is a 1 step job)
2. Remove all .txt. files
Every time I run these jobs manually, they complete successfully as reported in Job History. When they run automatically, they fail according to the Job History. However, I also create logs of the maintenance plans and these report success every time.
This exact job (only difference is the name of the directory it's going into to remove files) is created on 2 other servers and they always complete without issue. This is the only server giving me problems.
Am going to be trying some of the suggestions people posted earlier and/or just try and create 3 separate Maintenance Plans/Jobs and see if that fixes the problem. I'm tired of getting email spam for problems that appear to not be problems at all.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply