March 6, 2008 at 9:17 am
I'm trying to configure mail notifications on a job, and i read up a few things online which i had already done and then i restarted the SQL Server Agent as advised, but it would not restart. Right now, it shows beside it's icon as SQL Server Agent(Agent XP's disabled). when i try to start it it comes up with this error
"Unable to start service SQLSERVERAGENT on server MyServ.
Additonal Information:
The SQLSERVERAGENT service on MyServ started and then stopped. (Object Explorer)"
Any ideas what i might have done wrong / the problem and possible solutions?
thanks
------------------------------------------------------------------------
All it takes, is a step in the right direction, your feet will manage to find the way. I didn't say it'll be easy!!![font="Comic Sans MS"]:cool:[/font]
March 6, 2008 at 3:30 pm
What does it say in the SQL Server ERRORLOG and the SQL Agent Log?
By default they are in ...\MSSQL.1\MSSQL\LOG (initial bit depends on drive, folder where you installed SQL Server) for a default instance.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
March 7, 2008 at 3:26 am
these lines are from the records that read Error Log in the EventClass Column
2007-07-19 13:03:24.54 spid51 Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
2007-07-19 13:03:24.54 spid51 Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.
2007-07-19 13:03:24.55 spid51 Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.
------------------------------------------------------------------------
All it takes, is a step in the right direction, your feet will manage to find the way. I didn't say it'll be easy!!![font="Comic Sans MS"]:cool:[/font]
March 7, 2008 at 3:58 am
Can you post the contents of your ..\MSSQL\LOG\SQLAGENT.OUT file too please.
March 7, 2008 at 4:09 am
Hi,
You'll need to enable the Agent XP's by using following:
sp_configure 'show advanced options','1'
RECONFIGURE
go
sp_configure 'Agent XPs','1'
RECONFIGURE
go
This will enable Agent XP's and SQL Server Agent should start.
Thanks,
Phillip Cox
MCITP - DBAdmin
March 7, 2008 at 4:11 am
here is the content of SQLAgent.out
2008-03-07 10:23:33 - ! [298] SQLServer Error: 2, Named Pipes Provider: Could not open a connection to SQL Server [2]. [SQLSTATE 08001]
2008-03-07 10:23:33 - ! [165] ODBC Error: 0, Login timeout expired [SQLSTATE HYT00]
2008-03-07 10:23:33 - ! [298] SQLServer Error: 2, An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. [SQLSTATE 08001]
2008-03-07 10:23:33 - ! [000] Unable to connect to server 'Starwars'; SQLServerAgent cannot start
2008-03-07 10:24:03 - ! [298] SQLServer Error: 2, Named Pipes Provider: Could not open a connection to SQL Server [2]. [SQLSTATE 08001]
2008-03-07 10:24:03 - ! [165] ODBC Error: 0, Login timeout expired [SQLSTATE HYT00]
2008-03-07 10:24:03 - ! [298] SQLServer Error: 2, An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. [SQLSTATE 08001]
2008-03-07 10:24:03 - ! [382] Logon to server 'Starwars' failed (DisableAgentXPs)
2008-03-07 10:24:04 - ? [098] SQLServerAgent terminated (normally
------------------------------------------------------------------------
All it takes, is a step in the right direction, your feet will manage to find the way. I didn't say it'll be easy!!![font="Comic Sans MS"]:cool:[/font]
March 7, 2008 at 4:14 am
Hi,
have you enabled remote connections via the Surface Area Configuration tool?
Thanks,
Phillip Cox
MCITP - DBAdmin
March 7, 2008 at 4:29 am
thanks's Phillip,
what database should i run this against? msdb?
sp_configure 'show advanced options','1'
RECONFIGURE
go
sp_configure 'Agent XPs','1'
RECONFIGURE
go
This will enable Agent XP's and SQL Server Agent should start.
------------------------------------------------------------------------
All it takes, is a step in the right direction, your feet will manage to find the way. I didn't say it'll be easy!!![font="Comic Sans MS"]:cool:[/font]
March 7, 2008 at 4:40 am
Hey,
It's a master database query.
Thanks,
Phillip Cox
MCITP - DBAdmin
March 7, 2008 at 5:55 am
Hi Phillip,
this was displayed on the result pane after i ran the block of code against the master database.
Remote connections are enabled and i have tried to start SQLSERVERAGENT to no avail.
------------------------------------------------------------------------
All it takes, is a step in the right direction, your feet will manage to find the way. I didn't say it'll be easy!!![font="Comic Sans MS"]:cool:[/font]
March 7, 2008 at 5:59 am
sorry, forgot to include the message on the result pane
Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
------------------------------------------------------------------------
All it takes, is a step in the right direction, your feet will manage to find the way. I didn't say it'll be easy!!![font="Comic Sans MS"]:cool:[/font]
March 7, 2008 at 7:27 am
thanks all,
after i executed the block of code (Phillip's) and refreshed my connection, i could open the properties of SQLSERVERAGENT, which prior to now had been greyed out(disabled).
As i went through each of the tabs, i realised i had mistakenly typed the server name in the "alias local host server" box in the connection tab. i deleted the entry and restarted the agent and it worked!!
i haven't sorted out the notification mailing though, but i'm glad scheduled jobs can run now.
thanks again
------------------------------------------------------------------------
All it takes, is a step in the right direction, your feet will manage to find the way. I didn't say it'll be easy!!![font="Comic Sans MS"]:cool:[/font]
March 20, 2008 at 5:33 am
Hi,
I've had the same problem: enabling Agent XPs didn't help.
I've managed to fix it by repairing the Native Client setup:
-Select "Run the SQL Native Client Installation Wizard" in the setup screen
-Click "Next"
-select "Repair" and click "Next"
-Click "Install" and "Finish"
Try to start the service now.
March 20, 2008 at 5:37 am
bimpe_d (3/7/2008)
thanks all,after i executed the block of code (Phillip's) and refreshed my connection, i could open the properties of SQLSERVERAGENT, which prior to now had been greyed out(disabled).
As i went through each of the tabs, i realised i had mistakenly typed the server name in the "alias local host server" box in the connection tab. i deleted the entry and restarted the agent and it worked!!
i haven't sorted out the notification mailing though, but i'm glad scheduled jobs can run now.
thanks again
Hi bimpe_d,
Great result and thanks for update!!
Phillip Cox
MCITP - DBAdmin
January 18, 2009 at 7:30 pm
awesome xanthos...it worked for me..thanks
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply