How often should I schedule a reboot of SQL Server 2005?

Question

How often should I schedule a reboot of SQL Server 2005?

Duane Lawrence

SSC Enthusiast

Points: 191
More actions
May 10, 2007 at 11:13 am

#120007

How many times per year should we schedule time to reboot a SQL Server 2005 Cluster? We want to do this to defrag the memory. What I am looking for is a document from Microsoft or a consulting firm that has a good reputation that I can show my manager.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

I don't have any documents to show you, all I can tell you is what we do. Where I work now, our servers are rebooted weekly. At my previous employer, they reboot monthly after applying service packs or hot fixes to the OS/SQL Server. I would recommend at least reboots after applying os/sql server updates.

Jeremy Brown SSCarpal Tunnel Points: 4223 More actions · Answer 2

Interesting question. I'm dealing with the same issue here, but not with a cluster.

I did find this interesting writeup on how CLR application authors are dealing with the issue...

http://blogs.msdn.com/bclteam/archive/2006/10/17/clr-behavior-on-outofmemoryexceptions-brian-grunkemeyer.aspx

Other than that I've not found an "official" Microsoft document that gives reccomendations for avoiding or mitigating memory fragmentation.

One thing that you can do to reduce potential memory fragmentation is set the "Lock pages in memory" privelage for the SQL Server service account. At least that way you don't have to worry about fragmentation caused by shared memory pages.

Looking at perfmon, If I were to take an educated guess, I would monitor the Memory::Transitions Faults / sec and the Memory::Write Copies / sec counters. Seems to me that if you see a steady increase in these counters it would be something akin to a disk fragment. There will be the "sweet spot" that these grow to before you start recieving "out of memory" issues. I assume from that you can determine a MTBF and carry out some kind of plan to cycle SQL Server or the box itself.

Also, I'm not sure how bad this is or not, but I use the SysInternals PageDefrag utility, which defrags the page file, as well as the registry and some of those other "pesky" files that a normal disk defragmenter won't touch while the system is "hot". PageDefrag basically defrags the page file on the next bootup of the box. SysInternals is actually owned by Microsoft now, so check the tool out.

But yeah, an official "this is our reccomendation" from Microsoft would be super swell, since we're talking about denying service while the box / service restarts.

Antares686 SSC Guru Points: 125444 More actions · Answer 3

Honestly if things are running the way they should and barring patches you want to apply or high risks ones you really should never have to reboot the server. Now I now a lot of folks will say you have to but the question I have is why you think you would ever need to in your case.

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 4

Microsoft releases patches every month so probably after monthly Windows update you will have to reboot the server anyway.

Regards,Yelena Varsha

Antares686 SSC Guru Points: 125444 More actions · Answer 5

Only if the patch requires you to do so. And yes MS has put out a lot recently that need to do so and are High Risk items but that is not always the case and you may choose to only install patches on a schedule of your own if the servers are not on a public network or you deem patches have to be tested in-house before implementation. Again what are seeing happen that makes you feel other than Patching time you need to reboot your servers?

Anders Pedersen SSChampion Points: 11410 More actions · Answer 6

After 6.5 I don't think I have had scheduled reboots of servers with SQL on it, and then mostly because of a memory leak in a custom made XP. Of course in 6.5 prior to SP3 (I think) you had to restart SQL every 89 days if I remember right or the internal date became wrong.

Best I have had was a SQL 2000 server running for 13 months with a restart. Not sure why there was no patches that went on during that time frame.....

Duane Lawrence SSC Enthusiast Points: 191 More actions · Answer 7

Duane Lawrence

SSC Enthusiast

Points: 191

May 10, 2007 at 1:13 pm

#705634

Jeremy Brown has the best answer so far, thanks.

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 8

i've been at my job for 6 years and we have windows servers that stay up for months at a time with no reboots and no problems. i really don't understand this weekly reboot thing and the belief that it does something useful

Michael Ross SSC Enthusiast Points: 139 More actions · Answer 9

I agree. Most people don't see this problem either because of the monthly Windows Update processes or they are not making extensive use of CLR integration in their database implementations.

One question: How many instances of SQL are you running on your clusters? (hot-hot or hot-cold?)