cloudbase
asked on
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.
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.
Right Click on Services, Run as Adminstrator. Find Sql Server Agent, right click, and click on Start.
Attached is a screen shot of the above.
StartSqlServerAgent.png
StartSqlServerAgent.png
ASKER
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.
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>\MSSQ L\Log)
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>\MSSQ
ASKER
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)
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)
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)
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)
What about Windows event logs?
BTW, what is you SQL Server Eiditon for SS2008R2 instance? Because SQL Agent is not supported in Express Edition.
ASKER
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"?
Still I cannot start the Agent. Is it something that related to "Agent XP is disabled"?
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
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
ASKER
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.
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.
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?
ASKER
Sorry mastoo,
it is not working either.
it is not working either.
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.
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -
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.
--------------------------
ASKER
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.
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.
Wich user are configured for SQL Server Agent service? It's the same for SQL Server service?
ASKER
No, 2 different users.
One for the agent and one for the SQL Service.
One for the agent and one for the SQL Service.
Hope that are users from AD. You shouldn't use Network Service or other pre-defined users.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Not relevant anymore