Ttdfjt

Is it bad writing or bad story telling if first person narrative contains more information than the narrator knows?

Why was the ancient one so hesitant to teach Dr Strange the art of sorcery

Why are low spin tetrahedral complexes so rare?

spatiotemporal regression

Extending Kan fibrations, without using minimal fibrations

How can I avoid subordinates and coworkers leaving work until the last minute, then having no time for revisions?

How to handle DM constantly stealing everything from sleeping characters?

Ex-manager wants to stay in touch, I don't want to

Why did Captain America age?

Was Mohammed the most popular first name for boys born in Berlin in 2018?

Why was wildfire not used during the Battle of Winterfell?

Windows OS quantum vs. SQL OS Quantum

What do "KAL." and "A.S." stand for in this inscription?

What food production methods would allow a metropolis like New York to become self sufficient

Equivalent for "Make the jacket to the button"

Passport stamps art, can it be done?

Is it a Munchausen Number?

Is there a need for better software for writers?

When quoting someone, is it proper to change "gotta" to "got to" without modifying the rest of the quote?

Why should password hash verification be time constant?

Was there a contingency plan in place if Little Boy failed to detonate?

How is CoreiX like Corei5, i7 is related to Haswell, Ivy Bridge?

Watching the game, having a puzzle

Why are parallelograms defined as quadrilaterals? What term would encompass polygons with greater than two parallel pairs?

Does restarting the SQL Services (on the machine) clear the server cache (for things like query plans and statistics)?

What reasons are there to stop the SQL Server?Why did a SQL Server Restart roll-back seemingly committed transactions?KILLED/ROLLBACK query doesn't stop runningRollback taking foreverSQL Server 2008 R2 - Service shutdown - what with running stored procedures?What load does SQL Server Reporting Services (SSRS) have on the server?Why SQL Server installs multiple services for AS and RS, but only one for IS?Does SQL Server cache data results for DAO?Change SQL Server settings without restarting servicesDoes restoring database clear buffer cache and stored proc cacheConnecting a standalone Microsoft R Server to a SQL Server with R Services and a SQL Server without R ServicesCan the plan-cache and/or statistics be copied from one instance to anotherSQL Server 2016 All Queries recompile all the time (plan cache corruption?)Plan Cache: Why are multiple plans cached for this stored procedure in SQL ServerHow does SQL Server determine how much memory to allocate to Data cache

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

6

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

sql-server sql-server-2008-r2 statistics cache plan-cache

share|improve this question

asked May 6 at 16:27

J.D.J.D.

573414

add a comment | 

6

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

sql-server sql-server-2008-r2 statistics cache plan-cache

share|improve this question

asked May 6 at 16:27

J.D.J.D.

573414

add a comment | 

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

sql-server sql-server-2008-r2 statistics cache plan-cache

share|improve this question

asked May 6 at 16:27

J.D.J.D.

573414

share|improve this question

asked May 6 at 16:27

J.D.J.D.

573414

share|improve this question

asked May 6 at 16:27

J.D.J.D.

573414

asked May 6 at 16:27

J.D.J.D.

573414

asked May 6 at 16:27

J.D.J.D.

573414

3 Answers
3

active

oldest

votes

10

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.

share|improve this answer

answered May 6 at 16:38

Laughing VergilLaughing Vergil

1,078313

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  2 days ago
  

  
  
  
  

add a comment | 

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:

• Why did a SQL Server Restart roll-back seemingly committed transactions?


• KILLED/ROLLBACK query doesn't stop running


• Rollback taking forever


• SQL Server 2008 R2 - Service shutdown - what with running stored procedures?


• https://dba.stackexchange.com/a/160293/1186

share|improve this answer

edited May 6 at 17:06

answered May 6 at 16:48

Aaron Bertrand♦Aaron Bertrand

155k18303508

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  
  

  
  
  
  

add a comment | 

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.

share|improve this answer

answered yesterday

Vinod NarwalVinod Narwal

493

add a comment | 

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f237494%2fdoes-restarting-the-sql-services-on-the-machine-clear-the-server-cache-for-th%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

10

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.

share|improve this answer

answered May 6 at 16:38

Laughing VergilLaughing Vergil

1,078313

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  2 days ago
  

  
  
  
  

add a comment | 

10

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.

share|improve this answer

answered May 6 at 16:38

Laughing VergilLaughing Vergil

1,078313

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  2 days ago
  

  
  
  
  

add a comment | 

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.

share|improve this answer

answered May 6 at 16:38

Laughing VergilLaughing Vergil

1,078313

share|improve this answer

answered May 6 at 16:38

Laughing VergilLaughing Vergil

1,078313

answered May 6 at 16:38

Laughing VergilLaughing Vergil

1,078313

answered May 6 at 16:38

Laughing VergilLaughing Vergil

1,078313

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:

• Why did a SQL Server Restart roll-back seemingly committed transactions?


• KILLED/ROLLBACK query doesn't stop running


• Rollback taking forever


• SQL Server 2008 R2 - Service shutdown - what with running stored procedures?


• https://dba.stackexchange.com/a/160293/1186

share|improve this answer

edited May 6 at 17:06

answered May 6 at 16:48

Aaron Bertrand♦Aaron Bertrand

155k18303508

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  
  

  
  
  
  

add a comment | 

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:

• Why did a SQL Server Restart roll-back seemingly committed transactions?


• KILLED/ROLLBACK query doesn't stop running


• Rollback taking forever


• SQL Server 2008 R2 - Service shutdown - what with running stored procedures?


• https://dba.stackexchange.com/a/160293/1186

share|improve this answer

edited May 6 at 17:06

answered May 6 at 16:48

Aaron Bertrand♦Aaron Bertrand

155k18303508

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  
  

  
  
  
  

add a comment | 

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:

• Why did a SQL Server Restart roll-back seemingly committed transactions?


• KILLED/ROLLBACK query doesn't stop running


• Rollback taking forever


• SQL Server 2008 R2 - Service shutdown - what with running stored procedures?


• https://dba.stackexchange.com/a/160293/1186

share|improve this answer

edited May 6 at 17:06

answered May 6 at 16:48

Aaron Bertrand♦Aaron Bertrand

155k18303508

share|improve this answer

edited May 6 at 17:06

answered May 6 at 16:48

Aaron Bertrand♦Aaron Bertrand

155k18303508

answered May 6 at 16:48

Aaron Bertrand♦Aaron Bertrand

155k18303508

answered May 6 at 16:48

Aaron Bertrand♦Aaron Bertrand

155k18303508

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.

share|improve this answer

answered yesterday

Vinod NarwalVinod Narwal

493

add a comment | 

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.

share|improve this answer

answered yesterday

Vinod NarwalVinod Narwal

493

add a comment | 

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.

share|improve this answer

answered yesterday

Vinod NarwalVinod Narwal

493

share|improve this answer

answered yesterday

Vinod NarwalVinod Narwal

493

answered yesterday

Vinod NarwalVinod Narwal

493

answered yesterday

Vinod NarwalVinod Narwal

493