Home > SQL, SQL2008 > Fighting with the SQL Agent

Fighting with the SQL Agent

I am currently struggling with getting new servers built out for upcoming projects, supporting our existing servers, and rapidly deploying new servers to meet increased demand from our customers (a very good problem to have in my opinion), and moving to a new cubicle at work to make room for our new team members.

Tonight the top thing on my list was to get a new Windows/SQL 2008 R2 server built up and databases restored to it for an upgrade project from SQL 2000.  Needless to say, just as I was setting my service accounts I was hit up with a production problem and became distracted.  In my rush to get the install going I swapped the Agent and Engine service accounts in the setup wizard.  It wasn’t until after the build was complete that I realized my error and corrected the accounts via Configuration Manager.  It really wasn’t anything more than housekeeping on my part.  The two accounts have identical domain privileges, and aside from the Agent account having agt in the AD properties it makes no difference to SQL server which account runs which service.  

So, fast forward a few hours when I am ready to start my restores and I realize that the SQL agent isn’t started, and Agent XPs are disabled. 

A quick script execution to enable Agent XPs and back to Configuration Manager I go to start the service, only to have it start then stop and to get this oh so helpful error on my screen:

The SQL Server Agent (MSSQLSERVER) service on Local Computer started and then stopped.  Some Services stop automatically if they have no work to do, for example, the Performance Logs and Alerts service.

So I start digging into event logs.  For whatever reason I started with the Windows Application Event log (guess it’s the sys admin in me), only to find the last Agent event was hours ago and showed a normal shutdown request.  Next up is the SQL Server error log where I see the reconfigure events, but nothing from the attempted start of the Agent service.   Finally I dig into the Agent error log where I notice that the file hasn’t been modified for a few hours, and to get a security error when I try to open it with notepad.   Huh, that’s a bit strange….

Next step is to look at the file permissions on the agent error log itself – turns out that my agent service account had no permissions set at all, and my engine service account had full permissions. 

I added the Agent service account and gave it full permissions to the sqlagent.out file and restarted the service again via Configuration Manager and will wonders never cease it started with no issues this time.

Hopefully this will help someone else out there in similar circumstances, and save you a bit of frustration.

Advertisements
Categories: SQL, SQL2008
  1. No comments yet.
  1. No trackbacks yet.

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: