August 22, 2005 at 3:00 pm
Hi,
After our server was moved two jobs running in our sql server since last couple of years have failed. (first job was doing the back up of production database and then restoring development database every night from that back & second job was backup and shrink transaction logs)
I checked job status history in sql server agent and I found these error messages.
(For first Job error message):The job failed. The owner () of job Truncate Log does not have server access.
(For second Job error message) : Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
Owner of both the jobs is SA. I work remotely. All I can do is play from sql server enterprise manager. How can I solve this problem?
August 22, 2005 at 4:17 pm
Try changing the job owner to something else, and then back to sa. This sometimes happens when moving domains and when the owner can't be resolved, it's displayed through EM as your login (which I'm assuming is sa).
Cheers,
- Mark
August 23, 2005 at 8:23 am
had a similar situation and i killed the jobs then rebuilt them the exact same way. worked fine afterwards.
may be an issue in the SQL builds the node after reading post above - which I concur with, try it first then this second.
Good luck
Studdy
August 23, 2005 at 11:30 am
Hi,
I tried changing the owner to someone else then again back to SA. Now something is weird happening. Job is still failing but in the view job status history i'm not seeing any entry for failed job. Seems like sql server agent notification is also screwed up. I will appreciate your feedback.
August 23, 2005 at 12:20 pm
Script out the JOB, delete it and add it back in for starters ...
Also, check the user account provileges for the service accounts.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
August 23, 2005 at 12:52 pm
Hi Rudy,
I'm new to sql server administration. I'm a developer.
(1) How do I script out the job? Is there a way so that I can just copy the job and create the new one which is exact replica of that one because there are so many things going inside that complex job and i am afraid that I will screw up something if i try writing it from the beginning.
(2) How exactly I can "check the user account provileges for the service accounts"? You mean asking network administrator of our domain about account provileges for the SA account ?
August 23, 2005 at 1:14 pm
You can script out the jobs, by rightclicking the job and chose All Task -> generate script.
August 24, 2005 at 11:26 am
If you have Active Directory ( besides assigning job ownership to 'sa'), check if that server is up and running. Might need to restart it...
my 2 cents
August 26, 2005 at 1:41 pm
I know I can can script out the jobs, by rightclicking the job and chose All Task -> generate script. How to recreate a new job from that saved script?
To create new job I go to to jobs right click on it then chose new job but i don't see any options there to paste this script?
August 27, 2005 at 4:57 pm
can you paste it in QA and then execute it....
November 22, 2005 at 11:22 am
I recently changed from one server to another...renaming the new server to the old server name (as to not break any code). My jobs failed as well. I beleive it is because they have an ID in a system table and they need to be regenerated so that the new server can recognize them. I scripted out the jobs, deleted them and the Maintenance plans, ran the script on the new server and rebuilt the Maintenance Plans and all works well!!!!!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply