MSSQL 2000 SP4
Recently our maintenance jobs started failing. The only error which is traceabel is if we log an individual log step, other wise the job does not log. The error returned is:
Job 'Transaction Log Backup Job for DB Maintenance Plan 'SomeName'' : Step 1, 'Step 1' : Began Executing 2006-03-27 07:55:20
Msg 22029, Sev 16: sqlmaint.exe failed. [SQLSTATE 42000]
I have removed and recreated the maintenance plan. Attempted to run the sql statement in query analyzer with the same results. And attempted to change the job to use xp_cmdshell by changing the step to an operating system command. All failed. In addition, running simple OS commands through xp_cmdshell like 'dir c:\' will intermittantly return null when run or return the correct results.
This problem started in the middle of the day on friday,3/24, with no apparent root cause (I have looked in the event logs, sql error logs) (although I still have not heard from the system administrators as to what may have been done during that time period).
I have run ntregmon and ntfilemon to try to determine if something is being denied access, I've reviewed the file system permissions. I cannot find any clue as to why this is occuring.
Right now the jobs will run when run from the command line (after adding the server, username, password parameters for sqlmaint.exe) so I've added them to the System tasks and they appear to be running normally from there.
Does anyone have any idea what may cause this xp_sqlmaint fails but sqlmaint.exe succeeds.
In addition, other jobs which do not call xp_sqlmaint are still running normally.
xp_sqlmaint fails sqlmaint.exe succeeds
MelSam
Upon further invstigation, MOM using Dell OpenManage scripts was leaving numerous cmd.exe processes hanging thus preventing MSSQL from spawning another cmd.exe process.
After noticing the numerous hung processes, the server spontaneously rebooted (ran out of resources due to the hung processes) we shut off MOM and the maintenance jobs ran as expected.