Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MSSQL 2008 R2 - Agent cannot be started

Posted on 2011-09-06
22
Medium Priority
?
2,905 Views
Last Modified: 2012-08-13
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
Comment
Question by:cloudbase
  • 9
  • 4
  • 3
  • +3
20 Comments
 
LVL 2

Expert Comment

by:John_Bon
ID: 36492737
Right Click on Services, Run as Adminstrator.  Find Sql Server Agent, right click, and click on Start.
0
 
LVL 2

Expert Comment

by:John_Bon
ID: 36492744
Attached is a screen shot of the above.
StartSqlServerAgent.png
0
 

Author Comment

by:cloudbase
ID: 36493574
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 19

Expert Comment

by:Rimvis
ID: 36493636
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
 

Author Comment

by:cloudbase
ID: 36493722
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
 
LVL 2

Expert Comment

by:John_Bon
ID: 36493778
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
 
LVL 19

Expert Comment

by:Rimvis
ID: 36493811
What about Windows event logs?
0
 
LVL 19

Expert Comment

by:Rimvis
ID: 36493823
BTW, what is you SQL Server Eiditon for SS2008R2 instance? Because SQL Agent is not supported in Express Edition.
0
 

Author Comment

by:cloudbase
ID: 36493886
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
 
LVL 2

Expert Comment

by:John_Bon
ID: 36493896
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
 

Author Comment

by:cloudbase
ID: 36493946
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
 
LVL 21

Expert Comment

by:mastoo
ID: 36496336
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
 

Author Comment

by:cloudbase
ID: 36523210
Sorry mastoo,

it is not working either.
0
 
LVL 5

Expert Comment

by:VENKAT KOKULLA
ID: 36527711
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
 

Author Comment

by:cloudbase
ID: 36528468
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
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 36941854
Wich user are configured for SQL Server Agent service? It's the same for SQL Server service?
0
 

Author Comment

by:cloudbase
ID: 36973718
No, 2 different users.
One for the agent and one for the SQL Service.
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 36978158
Hope that are users from AD. You shouldn't use Network Service or other pre-defined users.
0
 

Accepted Solution

by:
cloudbase earned 0 total points
ID: 36978448
No, both users are from AD
0
 

Author Closing Comment

by:cloudbase
ID: 37589286
Not relevant anymore
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

876 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question