SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Jobs failing
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

RM
Yak Posting Veteran

65 Posts

Posted - 09/14/2004 :  05:14:14  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 09/14/2004 :  05:21:28  Show Profile  Send jen a Yahoo! Message  Reply with Quote
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

United Kingdom
3608 Posts

Posted - 09/14/2004 :  05:22:46  Show Profile  Reply with Quote
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 - 09/14/2004 :  06:00:59  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 09/14/2004 :  06:02:52  Show Profile  Send jen a Yahoo! Message  Reply with Quote
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 - 09/14/2004 :  06:15:13  Show Profile  Reply with Quote
The job is for performing integrity check and uses xp_sqlmaint. The errors/messages I have listed above.
Go to Top of Page

jen
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 09/14/2004 :  06:25:12  Show Profile  Send jen a Yahoo! Message  Reply with Quote
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

United Kingdom
3608 Posts

Posted - 09/14/2004 :  06:25:43  Show Profile  Reply with Quote
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 - 09/14/2004 :  06:35:14  Show Profile  Reply with Quote
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 - 09/14/2004 :  06:43:22  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 09/14/2004 :  06:48:33  Show Profile  Send jen a Yahoo! Message  Reply with Quote
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?

Edited by - jen on 09/14/2004 06:49:44
Go to Top of Page

RM
Yak Posting Veteran

65 Posts

Posted - 09/14/2004 :  07:08:18  Show Profile  Reply with Quote
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

United Kingdom
3608 Posts

Posted - 09/14/2004 :  07:14:18  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 09/14/2004 :  07:35:19  Show Profile  Send jen a Yahoo! Message  Reply with Quote
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 - 09/14/2004 :  07:52:56  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 09/14/2004 :  07:59:05  Show Profile  Send jen a Yahoo! Message  Reply with Quote
have you tried recreating the job?
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 09/14/2004 :  22:30:56  Show Profile  Visit nr's Homepage  Reply with Quote
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 - 09/15/2004 :  03:11:25  Show Profile  Reply with Quote
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

United Kingdom
3608 Posts

Posted - 09/15/2004 :  05:42:10  Show Profile  Reply with Quote
Glad I could help...
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37446 Posts

Posted - 09/15/2004 :  14:08:36  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Edited by - tkizer on 09/15/2004 14:09:37
Go to Top of Page

MuadDBA
Aged Yak Warrior

USA
628 Posts

Posted - 09/16/2004 :  11:03:29  Show Profile  Reply with Quote
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
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000