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)

Tim Wappat 5,703 on at

Like (0)

Report

RE: Frequency and Benefit of Rebooting SQL Server

Béat,

Sadly too many companies only have a generalist IT person "babysitting" the database server. You wouldn't want nor be able to get them to issue commands like that against it...

whereas turn it off and on again they already know how to do. --This is the reality of the world.

Tim.

Was this reply helpful? Yes No
Suggested answer

Beat Bucher GP Gee... 28,058 Moderator on at

Like (1)

Report

RE: Frequency and Benefit of Rebooting SQL Server

Hi Richard,

Even for the TempDB I wouldn't restart the server.. there are other way of taking care of this (also in case of an emergency ) :

www.brentozar.com/.../when-shrinking-tempdb-just-wont-shrink

sqlsunday.com/.../shrinking-tempdb-without-restarting-sql-server

In a nutshell :

-- write everything from your buffers to the disc!

CHECKPOINT;

GO

-- Clean all buffers and caches

DBCC DROPCLEANBUFFERS;

DBCC FREEPROCCACHE;

DBCC FREESYSTEMCACHE('ALL');

DBCC FREESESSIONCACHE;

GO

-- Now shrink the file to your desired size

DBCC SHRINKFILE (TEMPDEV, 40960);

-- Make sure that there is no running transaction which uses the tempdb while shrinking!

-- This is most trickiest part of it all.

GO

(adjust the name of TempDB file & size to your needs).

Was this reply helpful? Yes No
Richard Wheeler 75,848 Moderator on at

Like (1)

Report

RE: Frequency and Benefit of Rebooting SQL Server

If I may throw my two cents here, I would agree that a SQL server properly maintained should not need to be rebooted unless updates or the installation of new software requires a reboot. However, in the real world where servers sit in closets and people touch the server that should not and install who knows what I have found that rebooting once a month usually keeps support calls to a minimum as the reboots tends to flush out any garbage or memory leaks. Plus for those clients with limit hard drive space and huge tempdb databases a reboot is the easiest way to shrink that database.

Was this reply helpful? Yes No
Tim Wappat 5,703 on at

Like (0)

Report

RE: Frequency and Benefit of Rebooting SQL Server

Great thread.

Only get a reboot when server gets rebooted for OS updates or SQL gets updated for patches. So it does not get rebooted very often (runs for weeks).

I don't like losing the plan cache or having the downtime for our many integrating services.

Tim.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Frequency and Benefit of Rebooting SQL Server

I'm glad he capitalized the IF.

That is a BIG IF.

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

Like (0)

Report

RE: Frequency and Benefit of Rebooting SQL Server

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
Mariano Gomez 26,225 on at

Like (1)

Report

RE: Frequency and Benefit of Rebooting SQL Server

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
Community Member on at

Like (0)

Report

RE: Frequency and Benefit of Rebooting SQL Server

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
KirkLivermont 5,985 on at

Like (0)

Report

RE: Frequency and Benefit of Rebooting SQL Server

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
Jim Lines 4 on at

Like (1)

Report

RE: Frequency and Benefit of Rebooting SQL Server

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