I have SQL Server job. In the last step of the job, I want to stop/start SQL Server.

Next, I created .bat file with NET STOP "MsSqlServer"

I tried SSIS package that uses the execute process task with cmd.exe and then execute the .bat file to restart mssqlserver and it works fine...

However, when I called this package in the SQL Server job, it stops the service but does not not start it back.

the question is how to stop/start sql server after another sql serve job complete..?

i have sql server/analysis services/reporting services in one station with 64 GB Memory.and i have nightly job with this steps :

  • 1-ETL that Update DW
  • 2- Process Analysis services Database
  • 3-Update Reporting services Subsciptions

Sometimes, in beginning the AS Processsing in the step 2, it gives me error: there is no resource Available and and the job got failed.

All I want is a good memory configuration for this environment.

what strategy I must choose for Memory Management ?

Another problem: After step 2 for update subscription (i have about 50 Subscriptions), if I try Update Subscription with clean memory (after I restart SQL Server service), the subscription executes very fast.

If i don't restart the SQL Services, it take long time to run and sometimes it never gives me results.

  • 5
    Why are you bouncing the instance? – Thomas Stringer Dec 30 '13 at 13:39
  • i want to sql server release memory – abianari Dec 30 '13 at 13:47
  • 1
    ooohh nooooooo. All that precious buffer cache, just trown away.. After SQL Server tried so hard to get all that data from slow disks and put it all in much faster memory... On a more serious note.. If you stop the service that is hosting your msdb database that holds all your job info.. I don't think that will work... – Edward Dortland Dec 30 '13 at 13:57
  • Are running this job on Sql Server which you want to stop/start or you have different Sql Server which you can use to trigger this kind of job ? Btw. maybe issue is related to temp dbs files ? You can check what kind of latches you have in activity monitor. – Kuba Miazek Dec 30 '13 at 14:24
  • 4
    @abianari Please, no. It is a good thing that SQL Server is consuming memory. Are you running into actual memory pressure problems? If so, then those need to be addressed at the root of the problem. Restarting SQL Server is never going to be the right answer. – Thomas Stringer Dec 30 '13 at 14:44

You can't expect a service that is not running to execute a command to start itself. You need to use something external. The SQL Agent service is dependent upon the SQL service running, that's why you can't do this in a job inside of SQL Agent. You could use the default windows task scheduler, or you could look to have the SQL service try to restart itself if it is found to be stopped.

That being said, your reason for restarting the service in order to release memory means that you should focus your efforts on proper memory management and not on how to dynamically restart the instance.

  • i want slq server to use memory for ETL Step an after that i want give memory back to OS for another Process(Analysis service /Reporting Services ...) – abianari Dec 31 '13 at 15:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.