Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Starlet_GT
Yak Posting Veteran
81 Posts |
 Posted - 2014-03-24 : 07:21:21
|
| Hi every one,Appriciate if you please provide me query through which I can restart SQL Server Agent, I have google this issue but unable to find a single string to restart a running instance of agent. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-03-24 : 09:01:40
|
| Agent is a Windows Service. So if you want to start or stop the service, use xp_servicecontrol. There does not seem to be a restart option for xp_servicecontrol. So you will have to stop and then start. I might be wrong on this, but I think this is an undocumented stored proc, so use with caution. Here is an example: http://www.mssqltips.com/sqlservertip/2036/monitor-start-and-stop-sql-server-services-using-xpservicecontrol/ |
 |
|
|
Starlet_GT
Yak Posting Veteran
81 Posts |
Posted - 2014-03-25 : 01:10:37
|
| Thanks for the reply, it is working. There are two queries with "EXEC" one that stop the agent and the other which start the service again, if i run both queries togather, first one which stops the service executed sucessfully but the other one which starts agent failed with error that service is already running, is there any way that it should execute sucessfully. |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-03-25 : 10:36:23
|
| It may be that it takes a bit of time for the service to stop, and you are requesting it to be started before it has stopped. You may have to use the QueryState parameter to determine whether the service has stopped, and if not wait a bit and then try again. That page also has examples of using QueryState. You can use the WAIT FOR command in T-SQL to wait before starting. |
|
|
|
|
|
|
Restarting SQL Server Agent