New Server - SQL Agent Will Not Start

[OUCATS]

I have just setup a new Data Warehouse Server. One of the problems with the older server was that suddenly the SQL Agent stopped working and I couldn't figure out how to get it started again. Since I was about to purchase a new server, I decided I would see if the new server would fix my problem.

So I setup the new server, applied all Windows patches, installed SQL Server 2000 and applied all SQL Server patches. I then checked the SQL Agent and it started and was running just fine. So now I decided to copy the old databases over tho the new server. I copied these databases over:
Master
Model
MSDB
Northwind
Pubs
Tempdb

I ran into one problem, the new Server Registration stopped working. So I copied the previous Master Database back to the new server and it started up just fine with all the older databases available.

Here's the problem: I checked to see if the SQL Agent was still running and it WAS NOT running. So I tried to start it and it would not start. So now it's acting just like it did before on the older server. So if it was running fine before I copied the old databases, then it's probably safe to say that one of the older databases has a problem that is keeping the SQL Agent from running. But I don't know which database could be causing this and what about the database is keeping it from running.

Any ideas would be extremely appreciated.

Thanks!!

 

[JayKusch]

Is the Agent returning any error messages? Look at your Agent error log as well as SQL Servers error logs. Anything noteworthy in there for us to chew on? May be a permissions issue.

Thanks

J. Kusch

 

[OUCATS]

Sorry about that, I meant to include the error message:

Event ID: 103
SQLServerAgent could not be started (reason: SQLServerAgent must be able to connect to SQLServer as SysAdmin, but '(Unknown)' is not a member of the SysAdmin role).

Thanks!

 

[OUCATS]

The other error message I just noticed is:

[298] SQLServer Error: 208, Invalid object name 'msdb.dbo.syscachedcredentials'. [SQLSTATE 42S02] (ConnIsLoginSysAdmin)

Thanks!

 

[OUCATS]

PROBLEM SOLVED!!

Someplace, somehow a system table was either removed or appling a SQL service patch caused the SQL Agent to look for a table that was not there. But I compared an original install of SQL Server 2K and found the system table (syscachedcredentials) in the MSDB database. But when I looked at my production system, the syscachedcredentials table was missing. I copied the table over (which was empty) and now I can start the SQL Agent.

Don't know why, but it's working and I'm cool with that.