|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Tuesday, August 04, 2009 3:18 PM
Points: 7,
Visits: 97
|
|
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:
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.Thanks in advance,
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 1:57 AM
Points: 17,125,
Visits: 12,225
|
|
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
We walk in the dark places no others will enter
We stand on the bridge and none may pass
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Tuesday, August 04, 2009 3:18 PM
Points: 7,
Visits: 97
|
|
| 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.
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: Yesterday @ 4:15 AM
Points: 1,107,
Visits: 3,622
|
|
can you post details of the job steps?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 
|
|
|
|
|
SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 11:32 PM
Points: 2,959,
Visits: 5,282
|
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Tuesday, August 04, 2009 3:18 PM
Points: 7,
Visits: 97
|
|
| 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?
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: Yesterday @ 4:15 AM
Points: 1,107,
Visits: 3,622
|
|
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"
|
|
|
|
|
SSC Journeyman
Group: General Forum Members
Last Login: Tuesday, March 16, 2010 7:01 AM
Points: 87,
Visits: 462
|
|
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?
|
|
|
|
|
SSC Eights!
Group: General Forum Members
Last Login: Friday, March 12, 2010 9:35 AM
Points: 936,
Visits: 209
|
|
| 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.
|
|
|
|
|
SSC Journeyman
Group: General Forum Members
Last Login: Tuesday, March 16, 2010 7:01 AM
Points: 87,
Visits: 462
|
|
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?
|
|
|
|
