5

Is there a "best" way to automatically restart SQL Server on a regular basis? I read that I could create a batch file to net start, net stop the agent and service in a batch file and schedule that, but I was curious if there was a better way of doing this.

Please nevermind how this is indicative of a larger problem of operation, I've been arguing this for a month and we've ended up here.

Thanks :).

Improve this question
2
  • 1
    Would be very useful to understand the reason. Just because you've come to the conclusion independently that restarting SQL Server is the answer there is a good chance you'll get better input from actually having a conversation about the real problem here, instead of just asking how to implement the solution you've already decided on. Open minds, and all that.
    – Aaron Bertrand
    Feb 21, 2013 at 17:34
  • 3
    While the second paragraph is a bit awkwardly-worded, I think since this was mentioned at all, the goal of the OP was to solve the underlying problem, but for whatever reason the business wouldn't buy in.
    – Jon Seigel
    Feb 21, 2013 at 18:08

2 Answers 2

Reset to default
15

I won't go into detail about why it's not a great idea, since you're already aware of all that. For anybody else stumbling across this answer: Try not to do this if possible!

For the occasions when I need to schedule an after-hours restart (patching, configuration changes, etc), I normally just run this in a batch file scheduled via Windows Task Scheduler:

net stop SQLSERVERAGENT
net stop MSSQLSERVER
net start MSSQLSERVER
net start SQLSERVERAGENT

Note that service names may vary if you aren't restarting the default instance. Also, the user account that the scheduled task runs under will need to have permission to restart the service.

I don't see any reason why this wouldn't work with a recurring schedule, as opposed to the one-time runs that I do.

Improve this answer
1
  • 2
    tip: net start SQLSERVERAGENT implicitly starts SQL server itself too.
    – Sedat Kapanoglu
    Feb 21, 2013 at 21:33
9

For the sake of being thorough, you can also do this through PowerShell. And then like db2 suggested, just schedule your PowerShell script to run on a scheduled routine through Task Scheduler.

Stop-Service -Name "MSSQLSERVER"
Start-Service -Name "MSSQLSERVER"

Or, to do this in one fell swoop with:

Restart-Service -Name "MSSQLSERVER"

This will essentially bounce the SQL Server service. And I will reiterate as well, routinely restarting SQL Server is not advised.

Improve this answer
3
  • Does the restart service also automatically restart dependant services?
    – Martin Smith
    Feb 21, 2013 at 16:53
  • 1
    @MartinSmith Testing it out on my local machine, yes it looks like it does. Though you need to use the -Force switch to restart the service when a dependent service exists.
    – Thomas Stringer
    Feb 21, 2013 at 17:35
  • > routinely restarting SQL Server is not advised. Do you have any evidence behind this? If this was wikipedia, I'd tag as "[citation needed]
    – Brian Webster
    May 31, 2017 at 15:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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