Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Jobs failing

Author  Topic 

RM
Yak Posting Veteran

65 Posts

Posted - 2004-09-14 : 05:14:14
We have some jobs defined for database maintenance which are scheduled to run daily. Sometimes, some of these jobs fail with the message "The job failed. The owner 'own' of job DB Backup does not have server access."

All the jobs have same owner and some of them are running where as some are failing. Infact some jobs starts working the next day.

Any clues why this could happen? Even mailing is also failing in case of job falure.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-14 : 05:21:28
not sure if this is best practice, but i encountered the same and i just had the jobs owned by sa or a standard login with sysadmin privilege
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-09-14 : 05:22:46
What versions of Windows and SQL are you on?

This could be to do with the Server losing view of the PDC for some reason..
Go to Top of Page

RM
Yak Posting Veteran

65 Posts

Posted - 2004-09-14 : 06:00:59
Its Win2k and SQL server2k. SP3 is installed for sql2k.
I tried to start the job through 'sa'. One of the jobs started and completed successfully but one failed with error "The job failed. The Job was invoked by User sa. The last step to run was step 1 (Step 1). NOTE: Failed to notify 'Adm' via email.".
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-14 : 06:02:52
the job that failed, can you post what the job is doing and the error details?
Go to Top of Page

RM
Yak Posting Veteran

65 Posts

Posted - 2004-09-14 : 06:15:13
The job is for performing integrity check and uses xp_sqlmaint. The errors/messages I have listed above.
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-14 : 06:25:12
is that all the error message? how about viewing the jobhistory>>session details? how about checking event viewer for additional info?

coz if it's SA, basically you won't have any more errors pertaining to permissions.
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-09-14 : 06:25:43
Can you list the details of the job error? In the view job history, click the 'show step details' checkbox...
Go to Top of Page

RM
Yak Posting Veteran

65 Posts

Posted - 2004-09-14 : 06:35:14
I have checked for the job history. I have checked the sysjobhistory table also. There are no detailed errors as such. Its the same "The job failed. The Job was invoked by User sa. The last step to run was step 1 (Step 1). NOTE: Failed to notify 'Adm' via email."

There is just 1 step in the job and that is to run xp_sqlmaint
Go to Top of Page

RM
Yak Posting Veteran

65 Posts

Posted - 2004-09-14 : 06:43:22
Here is the error detail of the step...

Executed as user: own. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-14 : 06:48:33
we need to know you exact step commands if you want us to help you, the error you posted don't say much.

--edit

so the job is only the mailing step?
Go to Top of Page

RM
Yak Posting Veteran

65 Posts

Posted - 2004-09-14 : 07:08:18
As I said, this job contains just 1 step in it. and on viewing Step details in the history it shows error message...

"Executed as user: own. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed."

The step contains T-SQL script like "EXECUTE master.dbo.xp_sqlmaint N'-PlanID 02A52657-D546-11D1-9D8A-00A0C9054212 -To "Adm" -Rpt "G:\Database\MSSQL$MAIN2\LOG\Morning Backup2.txt" -WriteHistory -CkDBRepair '"

Go to Top of Page

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-09-14 : 07:14:18
Does the login you have for the SQLSeverAgent account have local admin rights? If you log onto the server as this account, can you see the files on the G:\ drive?

Also, when you open the job, who is the owner of the job and are they an admin on windows? If not then you might want to change this to be set to a local admin user...
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-14 : 07:35:19
can the account that you use for the job create a file on the path you specified?

have you checked the disk capacity? you may be running out of space or the log file is full?
Go to Top of Page

RM
Yak Posting Veteran

65 Posts

Posted - 2004-09-14 : 07:52:56
Yes, this login has local admin rights.The other jobs are running successfully (these were set through the same login)

There is about 36GB of space available. Our DB size is about 16GB
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-14 : 07:59:05
have you tried recreating the job?
Go to Top of Page

nr
SQLTeam MVY

12543 Posts

Posted - 2004-09-14 : 22:30:56
Well, well. Another mysterious problem with sqlmaint, he says smugly.
Must add it to my signature.
http://www.nigelrivett.net/BadThings.html


==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

RM
Yak Posting Veteran

65 Posts

Posted - 2004-09-15 : 03:11:25
Recreating the job was the last thing I wanted to do. Instead, I reinstalled Outlook. Now mail is working. Further more, I got the errors in the report which I think will solve my problem...

Here are the errors listed in the report...
"
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC SQL Server Driver][SQL Server]Database state cannot be changed while other users are using the database 'dtba'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
[1] Database dtba: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode.

The following errors were found:

[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
"

Anyways, tnx a lot everybody for all the valueable solutions provided. Sp. RickD's solns helped me to dig deep into it. Once again tnx a lot to all.
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-09-15 : 05:42:10
Glad I could help...
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-15 : 14:08:36
Do you have SQL Mail configured? This part: Failed to notify 'Adm' via email.". requires SQL Mail.

Can you send an e-mail with xp_sendmail?

And for the maintenance plan, do not check the option to fix problems under the integrity screen. That requires exclusive access to perform. If any integrity problems arise, a DBA should work on it. The plan should not do it for you.

And don't use maintenance plans! Write your own scripts to perform database maintenance routines. If you need some ideas, see:

http://weblogs.sqlteam.com/tarad


Tara
Go to Top of Page

MuadDBA

628 Posts

Posted - 2004-09-16 : 11:03:29
quote:


And for the maintenance plan, do not check the option to fix problems under the integrity screen. That requires exclusive access to perform. If any integrity problems arise, a DBA should work on it. The plan should not do it for you.




Tara



By all that's holy, listen to Tara! Those REPAIR statements can cause data loss, and tey require everyone else be out of the system. Automatically fixing them can cause you more headaches, because you don't know what the hell it fixed.
Go to Top of Page
    Next Page

- Advertisement -