November 30, 2010 at 9:32 am
Looking for suggestions and or input/thoughts on the below How To question:
How to scheduled automatic restarts of SQL Server Services and or the Server that hosts the SQL Server services.
Currently our IT team uses bat files (which call the shutdown.exe command) that are executed on a schedule via Windows Scheduled Tasks. I'm curious if any of you have any alternative suggestions on how to do this, ideally ones that don't require additional software purchases. I know there are a number of scheduling apps available but I'm interested in non-paid alternatives to Windows Scheduled Tasks with bat files.
This method we use now may be the best free option out there and if that’s the case that’s great. I’m looking only because I’d like to see if there are alternatives because I’ve had some issues recently with these scheduled tasks running reliably and so my DB Servers are not always rebooting when they should.
As always, any input is welcome and appreciated.
Kindest Regards,
Just say No to Facebook!November 30, 2010 at 9:51 am
YSLGuru (11/30/2010)
I’m looking only because I’d like to see if there are alternatives because I’ve had some issues recently with these scheduled tasks running reliably and so my DB Servers are not always rebooting when they should.As always, any input is welcome and appreciated.
I'm assuming "reliably" meaning the scheduled task executes but something prevents the shutdown command from executing successfully in the batch file?
I would probably try using PowerShell commands they might work better for you:
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
November 30, 2010 at 10:56 am
Batch files from a scheduler is how I'd go about it, except I can't think of any reason I'd ever want to reboot a server on a fixed schedule. Why do that? What does it solve for you?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 30, 2010 at 12:27 pm
Shawn Melton (11/30/2010)
YSLGuru (11/30/2010)
I’m looking only because I’d like to see if there are alternatives because I’ve had some issues recently with these scheduled tasks running reliably and so my DB Servers are not always rebooting when they should.As always, any input is welcome and appreciated.
I'm assuming "reliably" meaning the scheduled task executes but something prevents the shutdown command from executing successfully in the batch file?
There are some switches to the shutdown command to force it
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 30, 2010 at 1:36 pm
GSquared (11/30/2010)
Batch files from a scheduler is how I'd go about it, except I can't think of any reason I'd ever want to reboot a server on a fixed schedule. Why do that? What does it solve for you?
I agree with Gus - why would you schedule a system restart like this? I wouldn't want my SQL Server systems to automatically restart on a schedule. In fact, the only time I want any of my systems to be restarted is after applying Windows OS patches - or when I apply a service pack/cumulative update to SQL Server.
We schedule patches on a monthly basis - but only patch the systems when there are patches available that we need. I generally see my systems restarted every other month - and sometimes even longer.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 30, 2010 at 4:06 pm
Jeffrey Williams-493691 (11/30/2010)
GSquared (11/30/2010)
Batch files from a scheduler is how I'd go about it, except I can't think of any reason I'd ever want to reboot a server on a fixed schedule. Why do that? What does it solve for you?I agree with Gus - why would you schedule a system restart like this? I wouldn't want my SQL Server systems to automatically restart on a schedule. In fact, the only time I want any of my systems to be restarted is after applying Windows OS patches - or when I apply a service pack/cumilative update to SQL Server.
We schedule patches on a monthly basis - but only patch the systems when there are patches available that we need. I generally see my systems restarted every other month - and sometimes even longer.
The restart is not my decission, IT makes those calls. That said I'm suprised to hear this level of "No reboots " comemnts. The system we run does not have to be up 24/7 so rebooting it iun the middle of the night does not interfer with anything and we do see less problems over all and user complaints when systems are routinely restarted. I know that properly maintained servers are supposed to be able to run without ever rebooting assuming no updates or hardware repairs; my C++ inctructor used to brag about how he kept the Schools seevres running for months at a time with no problem. That said I've never seen where regular reboots of systems caused problems (excetp of course when they have to be avaiable 24/7) but I've seen a large number of problems lessen or stop altogether with regular system restarts.
Thanks
Kindest Regards,
Just say No to Facebook!November 30, 2010 at 7:45 pm
The point is - everytime you restart the server, SQL Server starts without any data in the buffer cache and no plans cached. So, the first time data is accessed it has to be read from the disks and loaded into the buffer cache, which will cause performance issues the first time data is accessed.
As for the plan cache - every stored procedure and query executed will have to be compiled the first time it is run after the restart. Again, causing performance issues while that is being done.
I don't see any advantage to restarting any more often than every couple of months to apply patches. Again, at most I'll have servers restarted every month if there are patches to be applied - which is more often than is really needed.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 1, 2010 at 6:16 am
As mentioned, there's data the system loses when you restart SQL Server. Nothing that matters too awefully much, but some of it does matter. Plan use, for example, can be useful in performance tuning, but you lose it every time you reboot.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 1, 2010 at 8:03 am
YSLGuru (11/30/2010)
The restart is not my decission, IT makes those calls. That said I'm suprised to hear this level of "No reboots " comemnts. The system we run does not have to be up 24/7 so rebooting it iun the middle of the night does not interfer with anything and we do see less problems over all and user complaints when systems are routinely restarted. I know that properly maintained servers are supposed to be able to run without ever rebooting assuming no updates or hardware repairs; my C++ inctructor used to brag about how he kept the Schools seevres running for months at a time with no problem. That said I've never seen where regular reboots of systems caused problems (excetp of course when they have to be avaiable 24/7) but I've seen a large number of problems lessen or stop altogether with regular system restarts.Thanks
I've been there as well. I worked at a former company that had a POS system that ran on SQL Server. It processed sales for every store in Alabama and usually after 2 or 3 nights the system was just shot and lagged serverly processing data and reports. So they had us do scheduled reboots on the weekend, and then it go to every other day if I remember. I will admit at that time I was not that experienced in DBA performance tuning.
Along with the SQL system data and DB data you loose it can cause more wear and tear on the hardware. You may not see it right off but every start up of that server all that hardware is reinitializing and reading/writing. It annoyed me everytime I saw that the server had rebooted.
I might ask them to let me (me = you) have the system go for a day or two without rebooting, since it does seem to be a low use system (at least not 24/7). Go and setup some perfmon logs to capture performance data on the server. This will also allow you to capture the performance data that SQL Server collects (which resets after each reboot). Then go through that and see if you can determine where their issue is. IF nothing else post the numbers on here and we can help you out.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
January 10, 2011 at 11:39 am
Sevral have made good ponts about how daily reboots requires the recompiling of plans and loss of other data and i had not thought about that since its been like it is (daily reboots) for so long that I just got accustomed to it. It is annoying in some ways as I have to capture info daily and summarizie it myself becaus eof teh daily reboots. That said the system that uses the one DB that is on the SQL Server is mission critical for the hours it is schedule dto be up and I'd rather deal with the inconvienances a daily reboot can cause if it means less possible issues with the accounting system that uses the DB. We have little control over customizing the setup (short of additional views and the like we setup to see the data in alternate ways) as it could void our support if we change too much and the software endor suggest daily reboots.
Thanks for all the replies.
Kindest Regards,
Just say No to Facebook!Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply