9

All I've read about is how potentially damaging it is to stop SQL Server because it creates a cold cache and sucks up memory. So why would someone want to stop the SQL Server? If you can provide any links to articles so that I can read more into this I would really appreciate it!

This question was posed by my teacher. Unless it's some trick question, it has me absolutely stumped. His exact question was:

Conduct research using the Internet and learn why someone would want to stop the SQL Server. Explain your answer.

This was in the context of us exploring how to use SQL Server 2008 R2. I'm not sure if he is asking for the obvious answer, or if there is something I'm missing.

  • 2
    Power outage? OS upgrade? Migration of a physical server? Basically all the reasons you'd want to restart your computer. Or what about upgrading SQL Server itself, how are you going to do that while it's running? – immibis Jan 8 '17 at 8:35
22

Brent listed some invalid reasons for stopping the service, but there are valid reasons too:

  • Restart required by a service pack or other update
  • Certain configuration changes (e.g. service account change, hardware changes, instant file initialization, this list could go on for weeks)
  • In a cluster, a restart to force a failover or applying a rolling patch
  • Windows patching
  • 1
    Can I suggest Hardware improvements, SSD, Memory, ...? – McNets Jan 7 '17 at 22:45
  • 1
    I'd add enabling IFI, as well. – Erik Darling Jan 7 '17 at 23:07
  • If you're moving the system databases from C: to another drive you need to stop it. There's another valid reason. – SQL_Underworld Jan 8 '17 at 0:51
  • Enabling Always On Availability Groups in the service control manager is another one. – Kris Gruttemeyer Jan 8 '17 at 1:09
  • Certain actions performed in the SQL Server Configuration Manager, for example enabling a TCP/IP port or named pipes, also require a restart of the service to take effect. – dlatikay Jan 8 '17 at 18:28
19

Because they think there's a memory problem - SQL Server uses all of the memory available to it, up to its max memory setting (and even beyond.) Unknowing folks go into Task Manager, see SQL Server using a lot of memory, and think, "There must be a memory leak - I'll stop and restart SQL Server, and see what happens." Sure enough, that frees up a lot of memory (because SQL Server doesn't allocate it all right away by default), so they think they fixed the bug. Next thing you know, they're restarting SQL Server weekly.

Because they think there's a CPU problem - queries will use a ton of CPU resources, especially in the case of parameter sniffing issues. Unknowing folks try to connect into the SQL Server without knowing about the Dedicated Admin Connection (DAC), be unable to connect, and just run out of options. They restart because the executives are standing behind them, wanting a solution fast.

Because they've heard it fixes corruption - when folks run into a corruption issue, they're often willing to try anything to fix it.

Because they want a rollback to finish - they kill a query, and it sticks in rollback for a while because they didn't know rolling back a query is single-threaded. After minutes (or hours) of waiting, they restart the SQL Server, thinking the rollback won't be necessary when it starts back up again. Sadly, they're wrong, and SQL Server just keeps right on going with the rollback upon startup.

  • "Because they want a rollback to finish": I remember learning that lesson the hard way. – Michael J Swart Jan 9 '17 at 13:03
1

One reason could be is that you bought new hardware and migrated the databases to this new server. You now are shutting down this sql server instance on the old box (together with the box itself) since you want to make sure nobody connects to it anymore

You moved to the cloud, the on prem box is not needed anymore, it is shut down, reformatted and repurposed (if not too old)

1

A valid reason is when there is other software running on the same server that needs some of the memory SQL server has, but that only runs a few times a month.

For example my wife (an accountant who wishes to know as little (and no less) about SQL server that is needed to do her job) has a SQL server based system used by 3 people including her to process a very large dataset, they do lots of ad-hock queries, but a few times a month they have to run a calculation engine that is on the same server, and accesses the database. The calculation engine needs memory. They don’t have a DBA, they can’t get funding for more hardware, even if they could, the IT department (who know less about SQL then the accountants do) would take months to setup new hardware, and a reset of SQL server lets them do their jobs as accountants. (The transactional system is separate.)

  • They may want to reduce and increase the max Memory setting instead to free some RAM. – Magier Jan 9 '17 at 22:59
  • @Magier and what is the return on investment of an accountant learning how to do that instead of doing her job.... (And as soon as anything changes it will need different magic numbers to be set as different times.) – Ian Ringrose Jan 9 '17 at 23:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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