| 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 |
 |
|
|
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.. |
|
|
|
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.". |
|
|
|
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? |
|
|
|
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. |
|
|
|
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. |
|
|
|
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... |
|
|
|
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 |
|
|
|
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. |
|
|
|
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? |
|
|
|
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 '"
|
|
|
|
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... |
|
|
|
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? |
|
|
|
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 |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-09-14 : 07:59:05
|
| have you tried recreating the job? |
|
|
|
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. |
|
|
|
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. |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2004-09-15 : 05:42:10
|
| Glad I could help... |
|
|
|
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 |
|
|
|
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. |
|
|
|
Next Page
|
Jobs failing