Tuesday, January 20, 2009

How to restart windows service from SQL Job?

There could be many reasons for this requirement which, I am not going to discuss in this post. Here I will explain how you can do it.
Now, suppose you want to create an sql job that will restart window service, it means window service will be stoped first and then started again.

Following are the steps to create sql job that will do it.

1. Open Microsoft SQl Server Management Studio than click on the SQL Server Agent under the Sql server where you want to create sql job.
2. Right click on the jobs folder under SQL Server Agent and choose New Job option from the short cut menu.
3. In the New Job window General page, name your job.

Now you will have to create 2 job steps, first for stopping the window service and another one for starting it again, for that:

4. Click on the Steps in the "Select a page" section to open Steps page.
5. Click on the "New..." button on the Steps page to create new step.
6. In the new job step window, give name to your step in the "Step Name" field.
7. Choose "Operating system (CmdExec)" option from the dropdown for the job step type.
8. Enter following command in the "Command" field.

net stop "< Windows Service Name >"

like - net stop "Terminal Services" to stop terminal service.

9. Click on OK button to complete the job step creation process.
10. Now repeate steps 5 to 9 for creating another job step for starting the window service. But this time the command would be as follows:

net start "Terminal Services"

11. Now choose the first job step from the job step list and click on edit button, the job step will open in a window.
12. Click on the "Advance" page and choose second job step for the "On success action" and "On failure action" from the respective dropdown controls and click on OK button.

Reason: If service is already stop than this job step will fail so we will start the service otherwise if service is running than it will be stopped successfully so we will start it again.

13. Now go to the "Advance" page for the second job step and choose "Quit the job reporting success" for "On success action" and "Quit the job reporting failure" for "On the failure action" from the respective dropdown control.

There are other settings like Schedules, Alerts, Notifications, Targets for the
SQL Jobs that you can set according to your requirement, I am not going to discuss them in this post.

14. Now click on the OK button on the New Job window to complete the job creation process.

Now you are ready to run your newly created sql job to test it.



how are you, im from peru
I read your post, and it was cover my doubts. But i think the next thing:

- It's secure, for example if the Sql Server Database Service is Down or whatever occur, and my service remain inactive.

Another thing you implement something in the same server, that host the services, and maybe via code do a while until the service is starting.

Best Wishes
And good Posts,
See my blog and give me recomendations =)

Anonymous said...

...please where can I buy a unicorn?

PaulM said...

Thankyou very much for this solution - exactly what I needed.

Most people might wonder why on earth someone would want to do this but we needed to; we have a service that reads data from a SQL database on a timer (every 30secs) - we also have a SQL job that runs every day at 15:00. Sometimes the job would fail with deadlocks because the service would run during the job and cause the locking.

Now we can stop the service before SQL job runs, and start it again on completion/fail.


Alejandro Blanco-Uribe said...
This comment has been removed by the author.
nariseti uday said...

Hello Sanjay,
I am new to sql. I need to start a service from the SQL job. I have this code but it is not working for me.

USE msdb;
EXEC dbo.sp_add_job
@job_name = N'starting a service' ;

EXEC sp_add_jobstep
@job_name = N'starting a service',
@step_name = N'step 1',
@subsystem = N'CMDEXEC',
@command = N'NET STOP "CarboniteService"',
@retry_attempts = 5,
@retry_interval = 5 ;

EXEC dbo.sp_add_jobserver
@job_name = N'starting a service';

Can you please help me.

Thanks in Advance

Santosh Kumar said...

Nice post! I thank you for sharing this nice blog with us. Likewise, I would like to say something that Happy Heap Marketing is also one of the SQL Jobs