Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

So I've read that it's really a bad practice to restart SQL Server on a production environment..

Does restarting SQL Server speed it up?

Automatically restart SQL Server 2008

Why are periodic restarts required to keep my instance performing well?

Then tell me this,

How many of you have maintained an SQL Server on a production environment without having to restart it for at least ONE year or more? If so, can you share some general guidelines on how to do this? namely with regards to the following:

  1. Minimizing/eliminating deadlocks
  2. Cost efficient CPU and Memory usage
  3. Minimizing/eliminating memory leaks
  4. Monitoring the servers' processes to identify increase resource consumption

Please pardon me if anyone would find this question as offensive, I would just like to be more enlightened with the subject matter.

share|improve this question

closed as not constructive by Mark Storey-Smith, Paul White, Marian, Jon Seigel, RolandoMySQLDBA Mar 26 '13 at 1:00

As it currently stands, this question is not a good fit for our Q&A format. We expect answers to be supported by facts, references, or expertise, but this question will likely solicit debate, arguments, polling, or extended discussion. If you feel that this question can be improved and possibly reopened, visit the help center for guidance.If this question can be reworded to fit the rules in the help center, please edit the question.

If you don't restart Sql Server, you are not applying OS/Sql Server patches either. – vonPryz Mar 20 '13 at 8:34
How would restarting the instance minimize/eliminate deadlocks in the long run? Or make the CPU/Memory usage most cost efficient? Why do you think a restart would help with these two conditions? – Marian Mar 20 '13 at 9:11
In 2009 I decommissioned a Windows 2000, SQL Server 2000, 512MB RAM server that had been running without reboot for 727 days. Special measures required, none. – Mark Storey-Smith Mar 20 '13 at 10:52
up vote 2 down vote accepted

The most important guideline: Don't, unless it's for OS or SQL Server patching reboots. If you've configured server memory usage properly, and you don't have a CLR component, SQL Server bug, or other software leaking resources, then "preventive reboots" aren't necessary.

I also wouldn't recommend keeping the server up for a year without patching. If you need this kind of uptime, you're better off looking at SQL Server's clustering/high-availability options and performing rolling updates rather than running way behind on patches. With a good HA setup, you can essentially create the illusion that the server reboot only takes a couple of seconds.

share|improve this answer

From my experience, I've never needed to restart a production server, except for patches. Just like Marian, I do not understand how a reboot could have a beneficial effect on your 4 points. My simple advice would be to avoid reboots as much as possible, as they are no cure to your problems. Proper configuration, profiling ... will get you much further.

share|improve this answer

Like @KookieMonster, I fail to understand how a reboot can minimize or eliminate deadlocks, make CPU and memory message "cost efficient", minimize or eliminate memory leaks, or reduce resource consumption.

I have maintained plenty of SQL Servers that have uptimes of way longer than a year. @vonPryz makes a good point though, there are often very good reasons to reboot servers, most importantly service packs and other updates. You also need to restart SQL Server in the event of certain configuration changes - both to SQL Server and the underlying OS and/or hardware.

I did have one exception where we intentionally restarted one node in a cluster every week, but it was due to a bug in SQL Server 2005 (SP2 I think) where the "temp tables for destruction" counter just climbed and climbed exponentially and eventually choked the server (memory exhaustion). We were issued a private hotfix after about 6 months of this.

That is an edge case, though, and disappeared when we applied the hotfix (and was only ever seen on one cluster, even though several were configured identically and ran the same app). In almost all cases you should not be looking at restarting SQL Server as preventative maintenance. Focus on real problems you actually have, and troubleshoot those problems proactively instead of just assuming that a restart is a magic cure-all.

There's an old joke in IT: if your computer isn't working, reboot. Please don't treat SQL Server this way.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.