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.

Improve this question
0

4 Answers 4

Reset to default
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
Improve this answer
6
  • 1
    Can I suggest Hardware improvements, SSD, Memory, ...?
    – McNets
    Commented Jan 7, 2017 at 22:45
  • 1
    I'd add enabling IFI, as well.
    – Erik Reasonable Rates Darling
    Commented Jan 7, 2017 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_Deadwood
    Commented Jan 8, 2017 at 0:51
  • Enabling Always On Availability Groups in the service control manager is another one.
    – Kris Gruttemeyer
    Commented Jan 8, 2017 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
    Commented Jan 8, 2017 at 18:28
20

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.

Improve this answer
1
  • "Because they want a rollback to finish": I remember learning that lesson the hard way.
    – Michael J Swart
    Commented Jan 9, 2017 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)

Improve this answer
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.)

Improve this answer
2
  • They may want to reduce and increase the max Memory setting instead to free some RAM.
    – Magier
    Commented Jan 9, 2017 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
    Commented Jan 9, 2017 at 23:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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