November 16, 2004 at 8:12 am
When I try to run any jobs with more than one step, the job window shows step id completely out of order. My 1st thought was that MSDB could be corrupted so I ran CHECKDB and CHECKALLOC but there were no errors. I also rebooted the server but didn't help.
I also noticed the following error in event viewer's application log:
Source: SQLSERVERAGENT
Category: Alert Engine
Event Id: 322
The data portion of event 19002 from MSSQLSERVER is invalid.
I don't know if this error has anything to do with the problem I'm having. Microsoft's KB has confirmed this error (see article id Q230393) but it says "The message ... are informational only and can be ignored."
Does anyone had this same problem (job sequence out of order) and if there is a fix for this ? Please help.
FYI, we are running Window 2000 Adv. Server sp4 and SQL Server Ent. Edition (8.00.818) sp3.
Thanks,
Jimmy
November 16, 2004 at 9:50 am
When you say the job window shows step id completely out of order, is this the history window? Also, when you create a job, you can specify it to start with step xx, it's not always step 1. The properties of the job, the steps tab has a list box to pick the start step.
November 16, 2004 at 9:57 am
Steve, thanks for replying. No. It's not the history window. If you start a job manually it will pop up a window which shows all the steps, and you have to click Start or Cancel. This job have been there for a while and it was working until we had this problem.
November 16, 2004 at 1:48 pm
Can you still script the job ?
if so, do you notice anything weird in the script ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 17, 2004 at 1:55 pm
Finally I got this problem fixed. The problem is the clustered index of Sysjobsteps table where step id is not in the order it suppose to be. Running DBCC DBREINDEX does not fix the problem, you really have to drop and recreate it. Following are the steps to reconstruct Sysjobsteps table:
(1) Stop SQLSERVERAGENT
(2) write down the name of indexes and columns that make up the index which are: clust ==> unique clustered ==> job_id, step_id
nc1 ==> unique nonclustered ==> job_id, step_name
(3) Point to MSDB database and type:
select * into tempSysjobsteps from sysjobsteps
(4) truncate table Sysjobsteps
(5) drop index sysjobsteps.clust
drop index sysjobsteps.nc1
(6) insert into sysjobsteps select * from tempSysjobsteps order by job_id, step_id
(7) create unique clustered index clust on sysjobsteps (job_id, step_id)
create unique nonclustered index nc1 on sysjobsteps (job_id, step_name)
(8) drop table tempSysjobsteps
(9) Start SQLSERVERAGENT
November 19, 2004 at 8:41 am
I thought what I've done had fixed the problem but it came back yesterday afternoon. I ran the same script to fix it but it didn't work.
Let me repeat the symptom of this problem: when I start a job manually, it will not list the job steps in order, instead it displays 4, 3, 1, 2,....however, the job is executed in order ! At 1st thought you will say it's the GUI problem, but if this is the case it will show in other servers as well.
Now I've not clue what's happening, any ideas will be appreciated. Thanks.
November 19, 2004 at 9:55 am
Jimmy, the index thing seemed like a good guess. If it's just the display, I'm not sure what's wrong there. If it's really bugging you perhaps you want to contact Microsoft and see if they will accept it as a bug.
November 19, 2004 at 10:16 am
Thanks Steve. I feel better now.
December 23, 2004 at 12:57 pm
I called Microsoft for this. After several weeks of emails, phone calls and FTP to send our MSDB for their diagnosis, Microsoft finally admits it's a bug ! There is not hotfix for this. The workaround solution is to modify a system procedure - sp_help_jobstep - by adding the "ORDER BY step_id" to the SELECT statement.
If you also have this problem, you can ALTER the procedure to add the ORDER BY. Then, from EM, diconnect the server and reconnect it. Run the (multi-step) job manually and you should the steps in order.
Jimmy
December 23, 2004 at 1:22 pm
Just thought I would mention that I had the same issue. see:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=116003#bm116046
April 12, 2006 at 3:59 pm
A year-plus later, this thread saved me I don't want to think how much time. Yeah, SQL Server Central!
I've got one more thing to add on to this. I'm in the process of migrating everything on server A to server B, and that includes several dozen stored procedures. I've dived into SQL DMO to script these out, and barring a bug or two it generally works--at least until I tried scripting jobs off of a "scrambled job order" server. The resulting script attempts to recreate the job by step alphbetical order, not step numerical order...
This was on an SP4 machine (on a cluster). Hope they fix this in sp5, but I can't wait.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply