7

Does the server cache get wiped (similarly to when you restart the SQL instance / machine) when you only restart the SQL Services themselves?

11

Yes it does. Since SQL Server is in charge of maintaining those caches, when you shut down SQL server's services, it releases the memory back to the operating system.

  • Thank you! As a bonus question, do you know if restarting just the services during a query being rolled back could potentially result in the server coming back up in the "recovering" state, or is that only a potential outcome from restarting the whole instance / machine? – J.D. May 6 at 16:45
  • 1
    It could, but I don't know if it would. The perpetual rollback is a problem of course, and I have had to do what you are asking about before. The DB did not come back in a recovering state, but I might just have been lucky. Some of the consultant class people, like Brent Ozar's crew or Aaron Bertrand may know for sure. – Laughing Vergil May 6 at 16:53
  • Recovery is only needed on unclean shutdown or with preexisting corruption – eckes May 6 at 20:59
  • 3
    A database always start in the recovering state, but recovery is typically so quick so you don't catch it. (There is an exception where there's a bit in the mdf file stating that shutdown of clean database, but you should conceptually think if a database always starting in recovery state). The recovery phase can take a long time, if you have a large rollback, i.e., you might now notice this state. But you can also have a problem with the recovery, like a database file missing, and you will now have "recovery pending" (and this won't sort itself out). – Tibor Karaszi May 7 at 10:27
14

The behavior is no different between restarting the service alone or restarting the service due to rebooting the underlying operating system. Which information is wiped on such a restart?

  • Query plans? Yes.
  • Table/index data? Yes.
  • Statistics? No.

(Query plans, which use statistics, will have to be recompiled, but statistics won't have to get re-created unless there was also some event or condition to trigger that (there are several).)

If you stop the service while a transaction is rolling back, you likely haven't accomplished anything, since rollback may just start again from where it left off (or it may have to start over completely, depending on the type of activity that is getting rolled back).

If you restarted the service because you got impatient waiting for a rollback to complete, that's probably an expensive lesson you'll only have to do one or two times before you stop trying. See:

  • Thanks Aaron, insightful as usual! When you say "If you stop the service while a transaction is rolling back, you haven't accomplished anything, rollback will just start again from where it left off" are you saying that's always true or in most cases? If the former, how come when we restarted the services, sp_who2 no longer showed the processes being rolled back (their SPIDs were gone)? – J.D. May 6 at 17:04
  • 1
    @J.D. sorry, will should be may. It's possible the rollback was blocked before the restart and after the restart there was nothing blocking it so it was able to complete immediately. Killing the services is just a dangerous and unpredictable game. – Aaron Bertrand May 6 at 17:06
  • Ah gotcha. And agreed, unfortunately it wasn't my choice in this case, but I figured it had the same potential repercussions as an instance restart. Do you know if it's possible for the database to come back up in a "recovering" state as well for just the services being restarted similarly to what can happen when the instance is restarted during a rollback? – J.D. May 6 at 17:07
  • 1
    @J.D. Sure, anything is possible if you sever SQL Server's artery. :-) Depending on what else was going on in the database at the time you killed the service, you will have to wait for both redo and undo to complete before the database will be available, and there could be other more catastrophic causes for Recovery Pending specifically. – Aaron Bertrand May 6 at 17:17
0

sql server reserves operating system memory called buffer pool when starts. day to day workload increases the buffer pool and every page , cache, query stats,procedure stats etc are stored in buffer pool. this information reside in buffer pool untill following events happen.

  1. sql server/system restart
  2. dbcc dropcleanbuffers statement execute
  3. Memory pressure.

i hope this would help you to understand.

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.