Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 :).

share|improve this question
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 '13 at 17:34
2  
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 '13 at 18:08

2 Answers 2

up vote 8 down vote accepted

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.

share|improve this answer
1  
+1 I added my answer just to show the PowerShell way. –  Thomas Stringer Feb 21 '13 at 16:43
1  
tip: net start SQLSERVERAGENT implicitly starts SQL server itself too. –  ssg Feb 21 '13 at 21:33

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.

share|improve this answer
    
Does the restart service also automatically restart dependant services? –  Martin Smith Feb 21 '13 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 '13 at 17:35
    
Thanks. Useful to know! –  Martin Smith Feb 21 '13 at 17:36
    
No problem, Martin! –  Thomas Stringer Feb 21 '13 at 18:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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