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
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;
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
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
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
asked May 6 at 16:27
J.D.J.D.
573414
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
sql-server sql-server-2008-r2 statistics cache plan-cache
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
asked May 6 at 16:27
J.D.J.D.
573414
asked May 6 at 16:27
J.D.J.D.
573414
573414
add a comment |
add a comment |
3 Answers
3
active
oldest
votes
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.
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 |
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
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 |
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.
- sql server/system restart
- dbcc dropcleanbuffers statement execute
- Memory pressure.
i hope this would help you to understand.
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
);
);
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
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
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
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.
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.
answered May 6 at 16:38
Laughing VergilLaughing Vergil
1,078313
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.
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
answered May 6 at 16:38
Laughing VergilLaughing Vergil
1,078313
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 |
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
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
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
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
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
Recovery is only needed on unclean shutdown or with preexisting corruption
– eckes
May 6 at 20:59
3
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
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 |
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
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
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
answered May 6 at 16:48
Aaron Bertrand♦Aaron Bertrand
155k18303508
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
answered May 6 at 16:48
Aaron Bertrand♦Aaron Bertrand
155k18303508
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
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 |
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
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
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
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
@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
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
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
@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 |
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.
- sql server/system restart
- dbcc dropcleanbuffers statement execute
- Memory pressure.
i hope this would help you to understand.
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.
- sql server/system restart
- dbcc dropcleanbuffers statement execute
- Memory pressure.
i hope this would help you to understand.
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.
- sql server/system restart
- dbcc dropcleanbuffers statement execute
- Memory pressure.
i hope this would help you to understand.
answered yesterday
Vinod NarwalVinod Narwal
493
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.
- sql server/system restart
- dbcc dropcleanbuffers statement execute
- Memory pressure.
i hope this would help you to understand.
answered yesterday
Vinod NarwalVinod Narwal
493
answered yesterday
Vinod NarwalVinod Narwal
493
answered yesterday
Vinod NarwalVinod Narwal
493
answered yesterday
Vinod NarwalVinod Narwal
493
493
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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
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
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
