Post reply

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

  • 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.

  • May 10, 2007 at 11:40 am

    #705595

    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.

  • May 10, 2007 at 11:51 am

    #705599

    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.

  • May 10, 2007 at 12:39 pm

    #705616

    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.

  • May 10, 2007 at 12:46 pm

    #705621

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

    Regards,Yelena Varsha

  • May 10, 2007 at 1:04 pm

    #705629

    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?

  • May 10, 2007 at 1:08 pm

    #705632

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

  • May 10, 2007 at 1:13 pm

    #705634

    Jeremy Brown has the best answer so far, thanks.

  • May 11, 2007 at 11:11 am

    #705932

    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

  • May 11, 2007 at 11:55 am

    #705951

    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?)

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

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