Solved

MSSQL 2008 R2 - Agent cannot be started

Posted on 2011-09-06
22
2,631 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
22 Comments
 
LVL 2

Expert Comment

by:John_Bon
Comment Utility
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
Comment Utility
Attached is a screen shot of the above.
StartSqlServerAgent.png
0
 

Author Comment

by:cloudbase
Comment Utility
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
 
LVL 19

Expert Comment

by:Rimvis
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
What about Windows event logs?
0
 
LVL 19

Expert Comment

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

Author Comment

by:cloudbase
Comment Utility
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
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:cloudbase
Comment Utility
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
Comment Utility
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
Comment Utility
Sorry mastoo,

it is not working either.
0
 
LVL 5

Expert Comment

by:VENKAT KOKULLA
Comment Utility
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
Comment Utility
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 45

Expert Comment

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

Author Comment

by:cloudbase
Comment Utility
No, 2 different users.
One for the agent and one for the SQL Service.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
Comment Utility
No, both users are from AD
0
 

Author Closing Comment

by:cloudbase
Comment Utility
Not relevant anymore
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

OfficeMate Freezes on login or does not load after login credentials are input.
A safe way to clean winsxs folder from your windows server 2008 R2 editions
This tutorial will walk an individual through the steps necessary to enable the VMware\Hyper-V licensed feature of Backup Exec 2012. In addition, how to add a VMware server and configure a backup job. The first step is to acquire the necessary licen…
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now