|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SQL Jobs Fail but give no errorRunning into a first for me here..... I 'inherited' a SQL Server as part of my new job and there are some quirks happening that I have never encountered before. I am not a full fledged DBA, yet I thought myself at least fairly knowledgeable in SQL Server, but this has me stumped.... In the SQL Enterprise Manager, in the Jobs list under SQL Server Agent under Management, when a job has failed, to my knowledge, you always get a red circle X and the Last Run Status column states Failed and the time. You could then right click the Job, check out its history, expand the steps and get some info on the error. At least I thought so.... There are several existing jobs, and one I created myself, that fail but do not do any of the above. The one I made, a multi-step job, when I manually kick it off I can refresh the Job list and watch as it goes from step to step and then all of a sudden the Status says "Performing completion actions" before ever getting to the last step and the Job stops, but the Last Run Status column doesn't change, and there is no red circle X. The Job History has no information in it and SQL Server logs do not show anything in them. At a loss I checked the Event Viewer under the Application Log and found the following in conjunction with kicking off the job: Type: Warning Source: SQLServerAgent Description: SQL Server Scheduled Job 'Provider Registration MCPXDCT Table Synchronization' (0xAD686907F8460D4B8B6916DDE83C0B15) - Status: Failed - Invoked on: 2006-03-17 14:45:51 - Message: The job failed. The Job was invoked by User [censored]. The last step to run was step 4 (Get Temp Table Records). The job was requested to start at step 1 (Drop Temp Table). As you can see, it says what failed, when it failed, on which step it failed, but nothing at *why* it failed. This job in particular is an 8 step job and yesterday I ran it and it didn't hit the wall until step 7 and I ran it again just now to get the error and it failed on step 4. Each step runs stored procedures. There are other jobs that are 1 step Jobs that run DTS packages having the same outcome. But there are also other Jobs that are successfully completing and I don't know the best way to find out what is different between them, if that would even help. I have no clue what is going on here, and as I said before, I'm stumped. Any help is greatly appreciated. -- Andrew Hi Andrew
Job steps do not need to run in sequence you can specify the next step when something is successful or not on the second tab of the job step details, although if you have created the job yourself I would expect you to have set this up yourself. The job history should show the steps that have been executed therefore you may be able to run the steps manually (by pasteing the code into QA say!) and see what error values they give. You may also want to append the step information to a log file which could give you information not stored within the history. John Show quote "Andrew" <AndrewR***@hotmail.com> wrote in message news:entQFAhSGHA.2176@TK2MSFTNGP10.phx.gbl... > Hey all, > > Running into a first for me here..... I 'inherited' a SQL Server as part > of my new job and there are some quirks happening that I have never > encountered before. I am not a full fledged DBA, yet I thought myself at > least fairly knowledgeable in SQL Server, but this has me stumped.... > > In the SQL Enterprise Manager, in the Jobs list under SQL Server Agent > under Management, when a job has failed, to my knowledge, you always get a > red circle X and the Last Run Status column states Failed and the time. > You could then right click the Job, check out its history, expand the > steps and get some info on the error. At least I thought so.... > > There are several existing jobs, and one I created myself, that fail but > do not do any of the above. The one I made, a multi-step job, when I > manually kick it off I can refresh the Job list and watch as it goes from > step to step and then all of a sudden the Status says "Performing > completion actions" before ever getting to the last step and the Job > stops, but the Last Run Status column doesn't change, and there is no red > circle X. The Job History has no information in it and SQL Server logs do > not show anything in them. At a loss I checked the Event Viewer under the > Application Log and found the following in conjunction with kicking off > the job: > > Type: Warning > Source: SQLServerAgent > Description: SQL Server Scheduled Job 'Provider Registration MCPXDCT Table > Synchronization' (0xAD686907F8460D4B8B6916DDE83C0B15) - Status: Failed - > Invoked on: 2006-03-17 14:45:51 - Message: The job failed. The Job was > invoked by User [censored]. The last step to run was step 4 (Get Temp > Table Records). The job was requested to start at step 1 (Drop Temp > Table). > > As you can see, it says what failed, when it failed, on which step it > failed, but nothing at *why* it failed. > > This job in particular is an 8 step job and yesterday I ran it and it > didn't hit the wall until step 7 and I ran it again just now to get the > error and it failed on step 4. Each step runs stored procedures. There > are other jobs that are 1 step Jobs that run DTS packages having the same > outcome. But there are also other Jobs that are successfully completing > and I don't know the best way to find out what is different between them, > if that would even help. > > I have no clue what is going on here, and as I said before, I'm stumped. > Any help is greatly appreciated. > > -- Andrew > John,
I am aware you can set the steps to run in virtually any order you may need, and can dictate what to do on success or failure of any step. I have built many, many jobs on several other SQL Servers, just this one here is the first one I have ever seen that does not report errors, doesn't add anything to the Job History, doesn't update the Jobs node under management, and doesn't display the red circle X if a job fails. It is the weirdest damn thing I have ever seen. But then, I have no idea what "tweaks" the person before me may have done. This job is 8 steps, running straight 1 to 8 and set to "Quit reporting errors on failure" for each step. Steps 1-3 and 5-8 are stored procs, with step 4 a DTS package. I can run each one in QA without trouble and run the DTS successfully as well. But when I run the job, each step takes waaaay longer than it should (a Create Table that takes 8 seconds ???) and then on whatever step it fails on: - I get no error anywhere in SQL Server - I try to right click the job and select Job History I get "There is no history information for this job" - The Jobs node under Management does not show anything for Last Run Status - There is no red circle X I just do not understand why something on any other server runs and performs as advertised doesn't even come close to running as one would expect on this server. -- Andrew Show quote "John Bell" <jbellnewspo***@hotmail.com> wrote in message news:uOEg2RqSGHA.4280@TK2MSFTNGP10.phx.gbl... > Hi Andrew > > Job steps do not need to run in sequence you can specify the next step > when something is successful or not on the second tab of the job step > details, although if you have created the job yourself I would expect you > to have set this up yourself. The job history should show the steps that > have been executed therefore you may be able to run the steps manually (by > pasteing the code into QA say!) and see what error values they give. You > may also want to append the step information to a log file which could > give you information not stored within the history. > > John > > "Andrew" <AndrewR***@hotmail.com> wrote in message > news:entQFAhSGHA.2176@TK2MSFTNGP10.phx.gbl... >> Hey all, >> >> Running into a first for me here..... I 'inherited' a SQL Server as part >> of my new job and there are some quirks happening that I have never >> encountered before. I am not a full fledged DBA, yet I thought myself at >> least fairly knowledgeable in SQL Server, but this has me stumped.... >> >> In the SQL Enterprise Manager, in the Jobs list under SQL Server Agent >> under Management, when a job has failed, to my knowledge, you always get >> a red circle X and the Last Run Status column states Failed and the time. >> You could then right click the Job, check out its history, expand the >> steps and get some info on the error. At least I thought so.... >> >> There are several existing jobs, and one I created myself, that fail but >> do not do any of the above. The one I made, a multi-step job, when I >> manually kick it off I can refresh the Job list and watch as it goes from >> step to step and then all of a sudden the Status says "Performing >> completion actions" before ever getting to the last step and the Job >> stops, but the Last Run Status column doesn't change, and there is no red >> circle X. The Job History has no information in it and SQL Server logs >> do not show anything in them. At a loss I checked the Event Viewer under >> the Application Log and found the following in conjunction with kicking >> off the job: >> >> Type: Warning >> Source: SQLServerAgent >> Description: SQL Server Scheduled Job 'Provider Registration MCPXDCT >> Table Synchronization' (0xAD686907F8460D4B8B6916DDE83C0B15) - Status: >> Failed - Invoked on: 2006-03-17 14:45:51 - Message: The job failed. The >> Job was invoked by User [censored]. The last step to run was step 4 (Get >> Temp Table Records). The job was requested to start at step 1 (Drop Temp >> Table). >> >> As you can see, it says what failed, when it failed, on which step it >> failed, but nothing at *why* it failed. >> >> This job in particular is an 8 step job and yesterday I ran it and it >> didn't hit the wall until step 7 and I ran it again just now to get the >> error and it failed on step 4. Each step runs stored procedures. There >> are other jobs that are 1 step Jobs that run DTS packages having the same >> outcome. But there are also other Jobs that are successfully completing >> and I don't know the best way to find out what is different between them, >> if that would even help. >> >> I have no clue what is going on here, and as I said before, I'm stumped. >> Any help is greatly appreciated. >> >> -- Andrew >> > > Hi,
In SQL Jobs you can define what to do "on sucess" or "on failure" for each step of your job (on the second tab of the job step details). Our case looks like you have defined the "on failure" to do the "Quit with sucess" choice. Phil. Phil,
Just checked it out, and under the Job Properties, Edit Job Step, Advanced tab, each step within the job is set "Son Failure Action: Quit the job reporting failure". This is what it defaults to every time I have ever built a job which is why I am a little stumped as to why it was not doing this. Oh, sure, it quit, but forgot all about the reporting part. I found the problem that was causing the thing to blow up...I misspelled a table name in the Stored Procedure. But on every SQL Server (except for this one here) I have ever used, *any* problem with *any* step in the job would provide an error, set the Job History as failed, and show the red circle X in the Jobs node under Management. This server is not, and I do not know why. -- Andrew <Phil***@gmail.com> wrote in message Show quote news:1142707781.489501.233690@e56g2000cwe.googlegroups.com... > Hi, > > In SQL Jobs you can define what to do "on sucess" or "on failure" for > each step of your job (on the second tab of the job step details). Our > case looks like you have defined the "on failure" to do the "Quit with > sucess" choice. > > Phil. > Andrew,
Your Server smells fishy, what is the SP level of it ? it may need the latest one. Another possible problem could be a corrupted MSDB, maybe you should try an DBCC CHECKDB on it. Regards Phil. One more idea (already mentioned by John), under the Job Properties,
Edit Job Step, Advanced tab you will find the possibility to have all the screen output of you job's steps writen in a log-file. You can either define another log-file for each step or put all of them in one file by making the 1st step do "overwrite" and all the other steps do "append". This way you should see the inside life of your steps. Phil. Phil,
Thought of that myself just a bit ago. I went through the job and set each step to use an output file so I could see if there was anything. 7 of the 8 steps are stored procs, nothing I have not done on several SQL Server before. Also, I made double sure each step of the job is set to "Quit the job reporting failure", yet it seems that if the step does fail it quits just doesn't report the failure. I can run each step manually in QA without error, and run the DTS (step 4 of the 8) manually with no errors either. However, when I run the steps manually, they take but a blink of the eye, yet when run under the Job, they take upwards of 5 to 10 seconds a piece. I mean, a Create Table query taking 6 seconds??? Grasping for straws as to what is happening here I went to the Edit Job Steps Advanced Tab for each step and gave it an output file for the step. What I got after kicking off the job is this: Job 'Provider Registration MCPXDCT Table Synchronization' : Step 1, 'Drop Temp Table' : Began Executing 2006-03-20 08:45:23 Job 'Provider Registration MCPXDCT Table Synchronization' : Step 2, 'Create Temp Table' : Began Executing 2006-03-20 08:45:29 Job 'Provider Registration MCPXDCT Table Synchronization' : Step 3, 'Create Temp Table Indexes' : Began Executing 2006-03-20 08:45:35 DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: Delete from Table [MedHCG].[dbo].[MCPXDCT_TEMP] Step DTSRun OnFinish: Delete from Table [MedHCG].[dbo].[MCPXDCT_TEMP] Step DTSRun OnStart: Copy Data from MCPXDCT to [MedHCG].[dbo].[MCPXDCT_TEMP] Step DTSRun OnProgress: Copy Data from MCPXDCT to [MedHCG].[dbo].[MCPXDCT_TEMP] Step; 1 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 1 DTSRun OnFinish: Copy Data from MCPXDCT to [MedHCG].[dbo].[MCPXDCT_TEMP] Step DTSRun: Package execution complete. Job 'Provider Registration MCPXDCT Table Synchronization' : Step 5, 'Delete Unassociated Records' : Began Executing 2006-03-20 08:45:51 Job 'Provider Registration MCPXDCT Table Synchronization' : Step 6, 'Delete Identical Records' : Began Executing 2006-03-20 08:45:58 Job 'Provider Registration MCPXDCT Table Synchronization' : Step 7, 'Update Existing Records' : Began Executing 2006-03-20 08:46:04 Job 'Provider Registration MCPXDCT Table Synchronization' : Step 8, 'Add New Records' : Began Executing 2006-03-20 08:46:10 As you can see, there is nothing about an error in there anywhere, yet the Jobs screen under management shows "Unknown" as the Last Run Status for the job. When I right-click the job and click View Job History... I get the message "There is no history information for this job". I am at a loss here. I have never seen this happen on any other SQL Server. But as I mentioned before, I took over this server and have no idea what "tweaks" the previous person may have done, if any. I just do not know how to correct this and get it to perform as I am used to. -- Andrew <Phil***@gmail.com> wrote in message Show quote news:1142872779.955935.95540@e56g2000cwe.googlegroups.com... > One more idea (already mentioned by John), under the Job Properties, > Edit Job Step, Advanced > tab you will find the possibility to have all the screen output of you > job's steps writen in a log-file. > You can either define another log-file for each step or put all of them > in one file by making the 1st step do "overwrite" and all the other > steps do "append". This way you should see the inside life of your > steps. > > Phil. > Andrew,
I have no idea what's your problem so far, but there are still a few think to try. It can only get better. - 1st how long as this server not been rebooted? - 2nd check if there are some available SP's for both OS and SQL and apply them. - 3rd try to script the job, then review the generated script, looking for strange stuff. And if the script looks OK, then delete the job, run DBCC CheckDB(msdb) one more time, and finally rebuild the job using the generated script. You may have to check the Object ID of the DTS job. If all this fails, light some candles and give some offering to the spirits, it helps some time. Phil. Phil,
The server info: Product: SQL Server Standard Edition Operating System: Microsoft Windows NT - 5.2 (3790) Product Version: 8.00.2039 (SP4) Language: English (United States) OS memory: 2039(MB) Processors: 2 And I just ran DBCC CheckDB(msdb) -- returned 0 errors. Good thing I keep my hair cut short....can't get a good grip that way.... -- Andrew <Phil***@gmail.com> wrote in message Show quote news:1142872394.625561.256890@i39g2000cwa.googlegroups.com... > Andrew, > > Your Server smells fishy, what is the SP level of it ? it may need the > latest one. > Another possible problem could be a corrupted MSDB, maybe you should > try an DBCC CHECKDB on it. > > Regards > > Phil. >  |
|||||||||||||||||||||||
Other interesting topics