September 2, 2002 at 2:34 pm
Hi all,
I've just set up 2 new SQL 7.0 servers, and my new maintenance jobs - backups, optimisations, consistency check jobs etc - are all mysteriously failing. I've created them both with the Maintenance Wizard, and again by hand. I've attempted manual and scheduled runs. All to no avail. Nor do they populate the sysmainthistory table, although they're configured to do so. The mystery is that we have successfully installed some user DTS processes, and THEIR jobs work. In order to resolve the problem, I've:
(1) ensured the Agent service is running;
(2) ensured adequate space on the drive etc;
(3) verified that sqlmaint.exe exists in Mssql\binn;
(4) unchecked the 'attempt to repair minor errors' box (I read this was a known cause of the problem);
I've got some clues to go on, but nothing conclusive;
'sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.'
Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe
failed. [SQLSTATE 42000] (Error 22029). The step
failed.
Additionally, both servers hold databases that I imported from another server-INCLUDING the msdb, and BOTH have the same problems and same error messages. This cannot be a coincidence.
Anyone have any ideas?
Thanks in advance,
JB.
Edited by - jaishel on 09/02/2002 2:42:13 PM
September 3, 2002 at 3:31 am
If you have not done so already you should do the latest Service Pack. There is a known issue with SQL 7 that was fixed in one of the SPs, which I forget which was the one, but 4 is cumulative so it does contain it.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
September 3, 2002 at 5:34 am
quote:
If you have not done so already you should do the latest Service Pack. There is a known issue with SQL 7 that was fixed in one of the SPs, which I forget which was the one, but 4 is cumulative so it does contain it."Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
I'm using Pack II, which (unfortunately) is part of the standard build in my org. Any idea where I can find out which SP fixes our bug?
Thanks,
J.
September 3, 2002 at 11:30 am
Who owns the jobs? sa or another user? If another user, does that user have sysadmin rights?
What context is the SQL Server Agent service running under? localsystem, a local user, or a domain user?
Is the proxy account set (only relevant if running under the context of a user without sysadmin rights)?
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
September 3, 2002 at 12:33 pm
quote:
Who owns the jobs? sa or another user? If another user, does that user have sysadmin rights?What context is the SQL Server Agent service running under? localsystem, a local user, or a domain user?
Is the proxy account set (only relevant if running under the context of a user without sysadmin rights)?
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
Hi,
The jobs log in as the same user as the Agent Service does. This is a Domain user that has Sysadmin rights. None of the jobs need to reference resources over the network.
Thanks,
JB.
September 3, 2002 at 12:44 pm
quote:
Who owns the jobs? sa or another user? If another user, does that user have sysadmin rights?What context is the SQL Server Agent service running under? localsystem, a local user, or a domain user?
Is the proxy account set (only relevant if running under the context of a user without sysadmin rights)?
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
Both the jobs and the Agent service login as the same Domain Admin user. I have granted this user explicit Sysadmin rights to all databases.
Thanks,
Jai.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply