3

I'm looking for a way to automatically restart the SQL service after an agent job has completed or as the last step of an agent job. It occurs to me that the agent service is dependent on the server service, is this even possible?

Microsoft SQL Server Standard Edition (64-bit) 2008 R2(10.50.1777.0)

A little background:

This would be a temporary fix.

We have a sql server that runs an export job for our accounting software the analytic server. After it runs, the sql server becomes unresponsive, sometimes. I am assuming its the export job because with the job disabled no unresponsiveness. This job takes about 30 minutes to complete and is fired off around midnight. Restarting the SQL server service manually fixes the issue and is a temporary work around until we can figure out why the export job is causing problems. I'd like to preemptively restart service after the export job completes before sql becomes unresponsive.

Pete
  • 271
  • 1
  • 4
  • 19

2 Answers2

1

I'm with the commentors; I can't begin to fathom why you'd want to do this. What are you trying to accomplish? Maybe we can help you come up with a better solution.

That said, you won't be able to to do this from within a SQL Agent job (at least fully) since, as you pointed out, the SQL Agent service is dependent on the SQL Server service. Stopping SQL Server stops the SQL Agent. At best you would be able to send a restart signal to the SQL Server service, but you wouldn't be able to get the return status of anything and you lose control over the situation if something were to go south.

You could write a service or a create a scheduled task that monitors the job in question and upon job completion does your restart.

But again, this sounds like a band-aid fix for something.

EDIT: If you have a reliable time after hours when you know the job will have finished then I'd just create a scheduled task to restart the service at that time using a Powershell script or something (restart-service MSSQLSERVER or restart-service -displayname "SQL Server (MSSQLSERVER) assuming you're talking about a default instance.

And then figure out why the service comes to a grinding halt. Run SQL Profiler traces, check performance counters, manually run the things that the job runs to isolate the issue.

squillman
  • 37,883
  • 12
  • 92
  • 146
  • Correct, band aid until we figure out why the job is causing problems. I'll elaborate in my question's details – Pete Jul 31 '12 at 20:34
  • See my edit above – squillman Jul 31 '12 at 20:44
  • marking as accepted, answer is no! I will probably go the Powershell route, although I'd be interested in hearing suggestions on how to use a scheduled task to monitor job. – Pete Jul 31 '12 at 21:13
0

IF the SQL Agent job is actually finishing. (And that's a big 'if') then the last step of the job could use xpcmdshell to fire psservice (from the PSTOOLS suite) and tell the SQL Server service to restart.

It's real bad kludge-ey. But it could be done.

Bob
  • 597
  • 2
  • 8