• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3005
  • Last Modified:

MSSQL 2008 R2 - Agent cannot be started

Hi,

I've recently installed new MSSQL 2008 R2 server.
When I opened Management Studio, I could see that the agent is stopped and stated that Agent XP is disabled.
I cannot start this agent as well.

Please assist.

Thanks.
0
cloudbase
Asked:
cloudbase
  • 9
  • 4
  • 3
  • +3
1 Solution
 
John_BonCommented:
Right Click on Services, Run as Adminstrator.  Find Sql Server Agent, right click, and click on Start.
0
 
John_BonCommented:
Attached is a screen shot of the above.
StartSqlServerAgent.png
0
 
cloudbaseAuthor Commented:
The SQL Server Agent service on Local Computer started and then stopped. Some services stop automatically if they are not in use by other services or programs.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
RimvisCommented:
Hi cloudbase,

Check Windows event log for errors (Start->Administrative Tools->Event Viewer->Windows Logs->Application and ...->System )

You can also check SQLAGENT.OUT file in SQL Server instalation directory (most likely C:\Program Files\Microsoft SQL Server\<instancename>\MSSQL\Log)
0
 
cloudbaseAuthor Commented:
here's SQLAGENT.OUT:

2011-09-01 13:18:50 - ? [100] Microsoft SQLServerAgent version 10.50.1600.1 ((Unknown) unicode retail build) : Process ID 2076
2011-09-01 13:18:50 - ? [101] SQL Server HE-MSSQL-STG version 10.50.1600 (0 connection limit)
2011-09-01 13:18:50 - ? [102] SQL Server ODBC driver version 10.50.1600
2011-09-01 13:18:50 - ? [103] NetLib being used by driver is DBNETLIB.DLL; Local host server is
2011-09-01 13:18:50 - ? [310] 1 processor(s) and 8192 MB RAM detected
2011-09-01 13:18:50 - ? [339] Local computer is HE-MSSQL-STG running Windows NT 6.1 (7600)
2011-09-01 13:18:50 - ? [432] There are 7 subsystems in the subsystems cache
2011-09-01 13:18:55 - ! [364] The Messenger service has not been started - NetSend notifications will not be sent
2011-09-01 13:18:55 - ? [129] SQLSERVERAGENT starting under Windows NT service control
2011-09-01 13:18:55 - + [260] Unable to start mail session (reason: No mail profile defined)
2011-09-01 13:18:55 - + [396] An idle CPU condition has not been defined - OnIdle job schedules will have no effect
2011-09-01 14:18:17 - ? [131] SQLSERVERAGENT service stopping due to a stop request from a user, process, or the OS...
2011-09-01 14:18:19 - ? [098] SQLServerAgent terminated (normally)
0
 
John_BonCommented:
Your SQLAGENT.OUT file looks fine:
Mine is listed below:

2011-01-19 19:31:07 - ? [100] Microsoft SQLServerAgent version 10.50.1600.1 (x86 unicode retail build) : Process ID 604
2011-01-19 19:31:07 - ? [101] SQL Server JEWELS-PC version 10.50.1600 (0 connection limit)
2011-01-19 19:31:07 - ? [102] SQL Server ODBC driver version 10.50.1600
2011-01-19 19:31:07 - ? [103] NetLib being used by driver is DBNETLIB.DLL; Local host server is
2011-01-19 19:31:07 - ? [310] 2 processor(s) and 3070 MB RAM detected
2011-01-19 19:31:07 - ? [339] Local computer is JEWELS-PC running Windows NT 6.0 (6002) Service Pack 2
2011-01-19 19:31:08 - ? [432] There are 7 subsystems in the subsystems cache
2011-01-19 19:31:08 - ! [364] The Messenger service has not been started - NetSend notifications will not be sent
2011-01-19 19:31:08 - ? [129] SQLSERVERAGENT starting under Windows NT service control
2011-01-19 19:31:08 - + [260] Unable to start mail session (reason: No mail profile defined)
2011-01-19 19:31:08 - + [396] An idle CPU condition has not been defined - OnIdle job schedules will have no effect
2011-01-19 19:31:11 - ? [131] SQLSERVERAGENT service stopping due to a stop request from a user, process, or the OS...
2011-01-19 19:31:14 - ? [098] SQLServerAgent terminated (normally)
0
 
RimvisCommented:
What about Windows event logs?
0
 
RimvisCommented:
BTW, what is you SQL Server Eiditon for SS2008R2 instance? Because SQL Agent is not supported in Express Edition.
0
 
cloudbaseAuthor Commented:
Windows event logs are clear and my SQL 2008R2 is Enterprise.

Still I cannot start the Agent. Is it something that related to "Agent XP is disabled"?
0
 
John_BonCommented:
Hi CloudBase,

  If this was just installed, you may want to configure SQL Server Agent via Sql Server Configuration Manager.

The screen shots for the following steps are attached:

  1.  Open SQL Server Configuration Manager
  2.  In SQL Server Configuration Manager, Click on SQL Server Services ( in the left pane. )
  3.  In the right pane, right click on SQL Server Agent, Click on Properties.
  4.  In SQL Server Agent Properties, Click Service Tab, Set Start Mode to Automatic
  5.  Click the Log On tab, Select the Built-in account, Network Service.  Click the Start Button.  Click Ok.
  6.  In the SQL Server Configuration Manager, Right Click SQL Server Agent, Click Restart.
  7.  In SSMS, Object Explorer, Click on the Server, and Click Refresh
  8.  After Refreshing, you will see the SQL Server Agent running with its objects showing.

Hope this works out for you.
SQLServerAgentConfiguration.png
0
 
cloudbaseAuthor Commented:
Hi John,

thanks for the hard work putting all the screenshot here.
Unfortunately, the agent refuse to start.
When I'm trying to change the account, a pop-up error is seen:

WMI Provider Error - The Process terminated unexpectedly.
So I cannot change the account.

When I'm trying to start the agent once more from the configuration manager, I'm getting this error:
The request failed or the service did not respond in timely fashion.
0
 
mastooCommented:
This is just a guess but if you are running SSMS directly on the server, start it first by right-click runas admin and then try starting sql agent from within it.  And presumably you are connecting as someone configured as a sql manager?
0
 
cloudbaseAuthor Commented:
Sorry mastoo,

it is not working either.
0
 
VENKAT KOKULLASQL Server DBACommented:
1) Follow these steps.

Step 1: SQL server Network configuration

step 2: Protocols for local server name Protocol name VIA Disabled or not.. if not disabled , disable and check.

-------------------------------------------------------------------------------------------------

2) sp_configure 'show advanced options',1

go

reconfigure with override

go

sp_configure 'Agent XPs',1

go

reconfigure with override

go

sp_configure 'show advanced options',0

go

reconfigure with override

go
-----------------------------------------------------------------------------------------------

3)

1.Click Start, point to All Programs, point to Microsoft SQL Server 2008, and then click SQL Server    
   Management Studio.
2.In the Connect to Server dialog box, verify the default settings, and then click Connect.
3.In Object Explorer, right-click the server, and then click New Query to open a new Database Engine
   Query Editor window.
4.In the Query Editor window, type the following Transact-SQL statement:
5.EXEC sp_addsrvrolemember '<Domain>\<Account>', 'sysadmin';
6.GO
Note <Domain> represents the domain name, and <Account> represents the domain user account that is assigned to the SQL Server 2008 Agent service.
7.Press F5 to execute the query.
8.Close the SQL Server Management Studio window.
9.Try to restart the SQL Server Agent service.
-------------------------------------------------------------------------------------------------



0
 
cloudbaseAuthor Commented:
Hi,

thanks for the solution, it does changed the status of the Agent XPs (not showing as disabled), but I cannot start the SQL Agent service.
The same error is shown.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Wich user are configured for SQL Server Agent service? It's the same for SQL Server service?
0
 
cloudbaseAuthor Commented:
No, 2 different users.
One for the agent and one for the SQL Service.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Hope that are users from AD. You shouldn't use Network Service or other pre-defined users.
0
 
cloudbaseAuthor Commented:
No, both users are from AD
0
 
cloudbaseAuthor Commented:
Not relevant anymore
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 9
  • 4
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now