SQL Server Agent will not start

SQL Server (2000) Agent failed to start today and the message in the Event Viewer was
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).

We looked at permissions and policies on the Win2K server and also tried using other administrator ids without success.

Finally, when all else failed, I tried profiler and discovered…

This is caused by setting the msdb compatibility to 7 (sp_dbcmptlevel ‘database_name’, 70). Specifically, the use of COLLATE fails in a temporary table creation within the sp_sqlagent_has_server_access stored procedure which is called as part of service startup

Just run sp_dbcmptlevel ‘database_name’, 80 to correct.

Unfortunately, SQL Server 2000 allows you to change the compatibility level on the msdb and, therefore, cause this problem.

Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: