Frequency and Benefit of Rebooting SQL Server

(0) Share

Report

Posted on by Jim Lines

Greetings!
I'm a believer in rebooting our SQL Server every once in a while as standard maintenance. I like to think this practice will clear out any cached junk that may cause performance issues. However, now that our GP team is hands off this sort of thing, I have to request a reboot from our systems team and verification from our DBA team. Of course, they want to know why the server needs to be rebooted. Unfortunately, my "because" doesn't really convince them that it's necessary.

I believe Victoria Yudin mentions in one of her posts, that it's a good idea to reboot no less than once a month, but I don't think there was any reason as to what it does. I've not seen, nor would I expect to see any "formal" documentation on this. Does anyone have information that I can use as my "because" so I can request these reboots without resistance? What do you do in your environment? Why? What benefit do you see? etc.

You feedback would be greatly appreciated! Thanks.

Jim

*This post is locked for comments

I have the same question (0)

All responses (15)

Answers (5)

Verified answer

KirkLivermont 5,985 on at

Like (0)

Report

Hi Jim,

I would tend to stay away from saying the SQL Server needs to be rebooted for any reason SQL related. Stick with Windows Updates need to be applied and the server needs to rebooted to do so. I tend to agree with you that it makes sense to reboot every once in a while to clear out any cached junk but why argue when you can just point to Windows updates and say it needs a reboot?

Regards,

Kirk

Was this reply helpful? Yes No
Verified answer

Mahmoud Saadi 32,738 on at

Like (1)

Report

This is one of the most controversial topics that arises every now and then among the IT people from the various domains. Some agree while others disagree, and each has their valid reasons

There are no doubt that every one would sharply agree about the fact that the SQL server must be restated when the tempdb is growing, or when applying new SQL server updates or hot fixes and other reasons. On the other hand, some people would argue that such a restart would require the server to warm up all over again as the buffer and cached memory is gone.

So you basically have to admit that there are downsides and upsides of restarting the SQL server. Allow me to quote few interesting information about this topic from a survey that I once read online,

Here is a question that was asked to several people, about restarting the SQL server,

And here is something more interesting ,

Reference: Survey results on rebooting – is it good or bad?

I personally don't like to regularly restart the SQL server, unless for a valid reason. The specifications should be up to the recommended levels and everything should be performing perfectly. Although, I don't mind a regular preventive restart every once and a while.

Was this reply helpful? Yes No
Verified answer

Victoria Yudin 22,769 on at

Like (1)

Report
Jim,

This is a great question. And thanks for putting me on the spot. Kidding! :-) I don't have a great technical reason for recommending a regular restart of the SQL server. By the way, I also recommend this for Remote Desktop servers. I can probably point to a few small technical reasons that can be argued away and server maintenance in general is certainly not my area of expertise. What I base my recommendation on is many years of experience specifically with Dynamics GP SQL servers and Remote Desktop servers.

To summarize my experience:

I have never seen a regular restart hurt anything on a Dynamics GP SQL server or Remote Desktop server.

I have seen environments with issues when there is no regular restart and where introducing a regular restart makes the environment more stable and better performing.

I have seen servers all of a sudden have an issue and need a restart in the middle of a workday or servers that unexpectedly restart on their own. This seems to typically happen on servers that are not rebooted regularly.

I have also seen servers that have not been restarted for years have issues on a restart or take an exorbitant amount of time because it's been so long.

Putting all of these experiences together, I make a general recommendation for a regular restart because it won't hurt, but it may help. Monthly seems to be a good general average. We have some clients that find restarting every few weeks better. We (internally) typically go 2-3 months between restarts.

Hope this helps give some perspective to my recommendation.

Was this reply helpful? Yes No
Verified answer

Richard Wheeler 75,848 Moderator on at

Like (0)

Report

The only thing I would add here is that it may be good to create a document that describes the entire reboot sequence so that non-technical people could perform a reboot, if necessary. I know most IT people never go on vacation but they do get sick every now and then. Also, sometimes there are third party products or customizations that require services to be running or certain user accounts to be logged into GP to insure data is flowing. It is always best to take stock of your environment and then commit to paper(or electronic ink) all the procedures needed to restart not only your GP box but a terminal server plus your domain controller.

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (1)

Report

The only time I restart is for patching. When you restart SQL yes you get all the memory back to the server and tempDB is wiped out but the trade off is that depending on how you configured your server that the plan cache is wiped out. Depending on the workload of the server it will take a while for SQL to cache decent execution plans.

In my environment I found having a solid nightly / weekly / monthly maintenance plan and making sure I set that Maximum amount of memory for the servers reduces any need to restart the server because I'm not starving the OS for memory and SQL has enough memory. If your server is setup to use ad-hoc workloads then loosing the plan cache may not be a big deal since SQL won't buffer as many single use plans. But again it all comes back to the default IT answer of "It Depends". If you want to get rid of bad cache there are several DMV's in SQL that will show you whats in the cache and they can be flushed out individually or in bulk. Kimberly Tripp has several articles on this www.sqlskills.com/.../plan-cache I hope this helps.

Was this reply helpful? Yes No
Jim Lines 4 on at

Like (1)

Report

Wow! Thanks to everyone for their feedback. I hope this post will help others with the same questions. So much great info (even though there is not a cut & dry answer).

Kirk - Thanks for responding. That's a good way to go about it, but our systems team handles all the server upgrades and maintenance, so I'm out of luck there.

Mahmoud - You weren't the first responder this time! We're you sleeping? Do you sleep?! Lol. You're input is awesome and very valuable. Thank you!

Victoria - I feel like a celebrity responded to my post! Thanks for your additional input, this actually seems like the best argument for maintenance from a GP/SQL perspective. Regarding terminal servers, I reboot those as often as I can. Thank you.

Richard - Fellow Northern New Yorker (notice I didn't say "Upstate?"), we do have a shutdown and startup plan that our support team maintains. It's tough to be a "hands off" GP admin when in my past years, the GP environment was always ruled by the GP admin!

Jonathan - Our DBA team has configured our maintenance plan for us (another thing I used to do as the system admin) and I think they try to keep it universal for all of our SQL server. Good info and thanks for the link.

Thanks to everyone who responded, this info is all VERY helpful.

Best regards,

Jim

PS: I don't know how many responses I can select as "yes" for answering my question, so don't be offended if I didn't "yes" you.

Was this reply helpful? Yes No
KirkLivermont 5,985 on at

Like (0)

Report

Jim,

Thanks for asking the question. There are many things brought up by Mahmoud, Victoria, and Jonathan I never would have thought of. Richard also brought up the great point of documentation so some tasks can be performed by non-technical staff. I might have to rewrite some procedures in the morning.

Thanks,

Kirk

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi All,

This is a great thread for GP Admins. Our internal process at is to stop all services connected to the SQL instance, reboot the server, then bring services back up.

As a Senior GP Sys Admin I have seen environments where monthly SQL reboots bring much stabilization. I have also experience "new GP environments" where reboots where not followed; and as a result SQL itself became unstable and did indeed force a reboot as Victoria mentioned.

Thank you again all for making my job easier :-)

Donnette

Was this reply helpful? Yes No
MG-16101311-0 26,225 on at

Like (1)

Report

If I may chime in.

I am a purist and I see absolutely no reason to reboot SQL Server IF properly configured and all hardware configurations are in place (disk arrays, memory, etc.) to support the deployment. SQL Server should be rebooted ONLY if a Windows update requires it or a SQL Server service pack requires it, other than that, the normal operation is for it to be up and running 24/7

:-)

Was this reply helpful? Yes No
Beat Bucher GP Gee... 28,058 Moderator on at

Like (0)

Report

I'm with Mariano on this one..

Some of my SQL servers have been running for month before they do get their semi-annual patches and require a reboot from the OS side.. Never seen any side-effect of letting run SQL 24/7, when properly configured and taken care of .. Of course, this implies also that you have put some monitoring in place, not to be caught short when all of a sudden your SQL stops responding and you realize that it run out of disk space.. :-)

Was this reply helpful? Yes No