Hey Guys,
Looking to see if anyone has experienced this and if there is something
we are doing wrong. We have a weekly process to reboot our windows
servers (we have 100's). The process is a normal windows shutdown.
Yet weekly one of our servers will come back up and have a 'Torn Page'
error, then get marked suspect. In investigating this I notice that
the when the process works, there is a message in the SQL Server error
log that says 'SQL Server is terminating due to Server shutdown'.
Yet in the cases where it ends up suspect the message is not in the
log. So to me it looks like the SQL Server is getting shutdown hard,
but why?
I end up just restoring the 'Suspect' database, but I'm wondering why
the process sporatically fails on some servers. Is there a setting I'm
missing?
Some specifics about the environment is that these are all Windows 2003
servers running SQL Server 2000 SP3 (it also occurs on Desktop
Edition).
Thanks in advance for any assistance.Hi
It could be that SQL Server is not responding in time to the request to
terminate.
Could you do a NET STOP on the SQL Server Services before shutting down.
Why do you need to shut down each week?
John
"paul.esposito@.comcast.net" wrote:
> Hey Guys,
> Looking to see if anyone has experienced this and if there is something
> we are doing wrong. We have a weekly process to reboot our windows
> servers (we have 100's). The process is a normal windows shutdown.
> Yet weekly one of our servers will come back up and have a 'Torn Page'
> error, then get marked suspect. In investigating this I notice that
> the when the process works, there is a message in the SQL Server error
> log that says 'SQL Server is terminating due to Server shutdown'.
> Yet in the cases where it ends up suspect the message is not in the
> log. So to me it looks like the SQL Server is getting shutdown hard,
> but why?
> I end up just restoring the 'Suspect' database, but I'm wondering why
> the process sporatically fails on some servers. Is there a setting I'm
> missing?
> Some specifics about the environment is that these are all Windows 2003
> servers running SQL Server 2000 SP3 (it also occurs on Desktop
> Edition).
> Thanks in advance for any assistance.
>|||Well, corporate standard is one reason and other systems reside on the
server and need to be rebooted.
I'm going to incorporate a netstop into the procedures. I was just
wondering if this was a common problem.
Regardless of reason, whether I reboot the server once a week or once
every year, if the database corrupts on that shutdown there seems to be
an issue. I say this because I was told that Microsoft says W2K3 and
SQL Server are tuned not to need a reboot and that they should be left
running. Well some of our servers can't be left running all the time
Hurricanes, power outages ... Just seems like a bug to me.
Thanks for the input though. I won't fight it, I'll just do a net
stop.
John Bell wrote:
> Hi
> It could be that SQL Server is not responding in time to the request to
> terminate.
> Could you do a NET STOP on the SQL Server Services before shutting down.
> Why do you need to shut down each week?
> John
> "paul.esposito@.comcast.net" wrote:
> > Hey Guys,
> >
> > Looking to see if anyone has experienced this and if there is something
> > we are doing wrong. We have a weekly process to reboot our windows
> > servers (we have 100's). The process is a normal windows shutdown.
> > Yet weekly one of our servers will come back up and have a 'Torn Page'
> > error, then get marked suspect. In investigating this I notice that
> > the when the process works, there is a message in the SQL Server error
> > log that says 'SQL Server is terminating due to Server shutdown'.
> > Yet in the cases where it ends up suspect the message is not in the
> > log. So to me it looks like the SQL Server is getting shutdown hard,
> > but why?
> >
> > I end up just restoring the 'Suspect' database, but I'm wondering why
> > the process sporatically fails on some servers. Is there a setting I'm
> > missing?
> >
> > Some specifics about the environment is that these are all Windows 2003
> > servers running SQL Server 2000 SP3 (it also occurs on Desktop
> > Edition).
> >
> > Thanks in advance for any assistance.
> >
> >|||The problem is in the nature of hardware. A page it 8KB. The disks typically uses a sector as the
level of atomic I/O. A sector is typically 512 bytes. I.e., a page is typically 16 sectors. A power
failure (or hard stop) in the middle of a page write will cause a partially written page (a torn
page). Having HW write cache *with a properly implemented battery backup* will minimize the risk of
a torn page (as long as the battery backup does its job).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<paul.esposito@.comcast.net> wrote in message
news:1164056550.793217.94860@.h54g2000cwb.googlegroups.com...
> Well, corporate standard is one reason and other systems reside on the
> server and need to be rebooted.
> I'm going to incorporate a netstop into the procedures. I was just
> wondering if this was a common problem.
> Regardless of reason, whether I reboot the server once a week or once
> every year, if the database corrupts on that shutdown there seems to be
> an issue. I say this because I was told that Microsoft says W2K3 and
> SQL Server are tuned not to need a reboot and that they should be left
> running. Well some of our servers can't be left running all the time
> Hurricanes, power outages ... Just seems like a bug to me.
> Thanks for the input though. I won't fight it, I'll just do a net
> stop.
>
> John Bell wrote:
>> Hi
>> It could be that SQL Server is not responding in time to the request to
>> terminate.
>> Could you do a NET STOP on the SQL Server Services before shutting down.
>> Why do you need to shut down each week?
>> John
>> "paul.esposito@.comcast.net" wrote:
>> > Hey Guys,
>> >
>> > Looking to see if anyone has experienced this and if there is something
>> > we are doing wrong. We have a weekly process to reboot our windows
>> > servers (we have 100's). The process is a normal windows shutdown.
>> > Yet weekly one of our servers will come back up and have a 'Torn Page'
>> > error, then get marked suspect. In investigating this I notice that
>> > the when the process works, there is a message in the SQL Server error
>> > log that says 'SQL Server is terminating due to Server shutdown'.
>> > Yet in the cases where it ends up suspect the message is not in the
>> > log. So to me it looks like the SQL Server is getting shutdown hard,
>> > but why?
>> >
>> > I end up just restoring the 'Suspect' database, but I'm wondering why
>> > the process sporatically fails on some servers. Is there a setting I'm
>> > missing?
>> >
>> > Some specifics about the environment is that these are all Windows 2003
>> > servers running SQL Server 2000 SP3 (it also occurs on Desktop
>> > Edition).
>> >
>> > Thanks in advance for any assistance.
>> >
>> >
>|||Tibor,
Thanks for the response. But according to the user and the event logs,
a normal shutdown was requested. i.e. The user requested Windows to
shutdown. This should have sent a message to all the services to stop.
Yet I don't see that request in the SQL Server log. But the event log
does show that a normal shutdown was requested of the OS.
Thanks again.
Tibor Karaszi wrote:
> The problem is in the nature of hardware. A page it 8KB. The disks typically uses a sector as the
> level of atomic I/O. A sector is typically 512 bytes. I.e., a page is typically 16 sectors. A power
> failure (or hard stop) in the middle of a page write will cause a partially written page (a torn
> page). Having HW write cache *with a properly implemented battery backup* will minimize the risk of
> a torn page (as long as the battery backup does its job).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <paul.esposito@.comcast.net> wrote in message
> news:1164056550.793217.94860@.h54g2000cwb.googlegroups.com...
> > Well, corporate standard is one reason and other systems reside on the
> > server and need to be rebooted.
> >
> > I'm going to incorporate a netstop into the procedures. I was just
> > wondering if this was a common problem.
> >
> > Regardless of reason, whether I reboot the server once a week or once
> > every year, if the database corrupts on that shutdown there seems to be
> > an issue. I say this because I was told that Microsoft says W2K3 and
> > SQL Server are tuned not to need a reboot and that they should be left
> > running. Well some of our servers can't be left running all the time
> > Hurricanes, power outages ... Just seems like a bug to me.
> >
> > Thanks for the input though. I won't fight it, I'll just do a net
> > stop.
> >
> >
> > John Bell wrote:
> >> Hi
> >>
> >> It could be that SQL Server is not responding in time to the request to
> >> terminate.
> >> Could you do a NET STOP on the SQL Server Services before shutting down.
> >> Why do you need to shut down each week?
> >>
> >> John
> >>
> >> "paul.esposito@.comcast.net" wrote:
> >>
> >> > Hey Guys,
> >> >
> >> > Looking to see if anyone has experienced this and if there is something
> >> > we are doing wrong. We have a weekly process to reboot our windows
> >> > servers (we have 100's). The process is a normal windows shutdown.
> >> > Yet weekly one of our servers will come back up and have a 'Torn Page'
> >> > error, then get marked suspect. In investigating this I notice that
> >> > the when the process works, there is a message in the SQL Server error
> >> > log that says 'SQL Server is terminating due to Server shutdown'.
> >> > Yet in the cases where it ends up suspect the message is not in the
> >> > log. So to me it looks like the SQL Server is getting shutdown hard,
> >> > but why?
> >> >
> >> > I end up just restoring the 'Suspect' database, but I'm wondering why
> >> > the process sporatically fails on some servers. Is there a setting I'm
> >> > missing?
> >> >
> >> > Some specifics about the environment is that these are all Windows 2003
> >> > servers running SQL Server 2000 SP3 (it also occurs on Desktop
> >> > Edition).
> >> >
> >> > Thanks in advance for any assistance.
> >> >
> >> >
> >|||What sort of hardware are you using? How are your disks configured? Are
there any events or issues with your I/O controllers? How is your I/O
solution utilizing it's write cache?
I agree that you should be able to restart whenver you want. I have
several SQL installations in my environment and have never had an issue
with a normal shut down routine. Can you do a manual reboot of the
server watching it (by manual I mean not automated but going through
start/shutdown) and see what happens?
Just a point, while you should be able to reboot whenver you want
(gracefully and even safely under some not so graceful situations
should not be a problem and has not in my experience with my servers),
it is generally not always a best practice to reboot your database
servers.
For one thing there is no need to if you have a secure server and have
only SQL Server running on it. In most of the configurations out there
with only SQL running, you won't see a memory leak or any other issue
requiring reboot. Also SQL works hard to build it's caches with data
and compiled query execution plans that are optimized for your data and
workload. Everytime you reboot you clear this cache meaning that after
restart the server needs to first optimize and save all the plans again
as well as add the frequently accessed data into the data cache. Not a
huge deal but just some items to think about.
Please give more details regarding my above questions and we will see
what we can offer.
-Mike Walsh
SQL DBA
paul.esposito@.comcast.net wrote:
> Tibor,
> Thanks for the response. But according to the user and the event logs,
> a normal shutdown was requested. i.e. The user requested Windows to
> shutdown. This should have sent a message to all the services to stop.
> Yet I don't see that request in the SQL Server log. But the event log
> does show that a normal shutdown was requested of the OS.
> Thanks again.
>
> Tibor Karaszi wrote:
> > The problem is in the nature of hardware. A page it 8KB. The disks typically uses a sector as the
> > level of atomic I/O. A sector is typically 512 bytes. I.e., a page is typically 16 sectors. A power
> > failure (or hard stop) in the middle of a page write will cause a partially written page (a torn
> > page). Having HW write cache *with a properly implemented battery backup* will minimize the risk of
> > a torn page (as long as the battery backup does its job).
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > <paul.esposito@.comcast.net> wrote in message
> > news:1164056550.793217.94860@.h54g2000cwb.googlegroups.com...
> > > Well, corporate standard is one reason and other systems reside on the
> > > server and need to be rebooted.
> > >
> > > I'm going to incorporate a netstop into the procedures. I was just
> > > wondering if this was a common problem.
> > >
> > > Regardless of reason, whether I reboot the server once a week or once
> > > every year, if the database corrupts on that shutdown there seems to be
> > > an issue. I say this because I was told that Microsoft says W2K3 and
> > > SQL Server are tuned not to need a reboot and that they should be left
> > > running. Well some of our servers can't be left running all the time
> > > Hurricanes, power outages ... Just seems like a bug to me.
> > >
> > > Thanks for the input though. I won't fight it, I'll just do a net
> > > stop.
> > >
> > >
> > > John Bell wrote:
> > >> Hi
> > >>
> > >> It could be that SQL Server is not responding in time to the request to
> > >> terminate.
> > >> Could you do a NET STOP on the SQL Server Services before shutting down.
> > >> Why do you need to shut down each week?
> > >>
> > >> John
> > >>
> > >> "paul.esposito@.comcast.net" wrote:
> > >>
> > >> > Hey Guys,
> > >> >
> > >> > Looking to see if anyone has experienced this and if there is something
> > >> > we are doing wrong. We have a weekly process to reboot our windows
> > >> > servers (we have 100's). The process is a normal windows shutdown.
> > >> > Yet weekly one of our servers will come back up and have a 'Torn Page'
> > >> > error, then get marked suspect. In investigating this I notice that
> > >> > the when the process works, there is a message in the SQL Server error
> > >> > log that says 'SQL Server is terminating due to Server shutdown'.
> > >> > Yet in the cases where it ends up suspect the message is not in the
> > >> > log. So to me it looks like the SQL Server is getting shutdown hard,
> > >> > but why?
> > >> >
> > >> > I end up just restoring the 'Suspect' database, but I'm wondering why
> > >> > the process sporatically fails on some servers. Is there a setting I'm
> > >> > missing?
> > >> >
> > >> > Some specifics about the environment is that these are all Windows 2003
> > >> > servers running SQL Server 2000 SP3 (it also occurs on Desktop
> > >> > Edition).
> > >> >
> > >> > Thanks in advance for any assistance.
> > >> >
> > >> >
> > >|||Unfortunately, a Server Shutdown request does exactly this: a request for
NET STOP on every running service. The problem is that there exists a time
out value. When SQL Server receives this message, it begins its shutdown
phase, which issues stops all new connections and issues a hard CHECKPOINT
of all databases.
Typically during the maintenance windows (especially the database REORGs),
this stop request could take quite a bit of time (even more on the start up
and recovery process).
Nearly the only way you are going to get a torn page from this (as opposed
to a lot of recovery rollback operations) is that the disks were powered
down before the cache committed.
1. Plan your database maintenance around these weekly system restarts.
2. Disable controller and Windows disk caching unless you can guarantee
controller power protection.
I would also agree with most everyone else that a DBMS should be on a
dedicated system, and, which case, should be very little justification for
weekly restarts.
Now, in our Data Center, we have monthly OS updates that are deployed. For
this, we (the DBA team) have demanded subsequent system restarts (mainly
because we believe patches leave PendingFileRename operations), but that is
only on a monthly basis, and we shut down database service before the server
team is allowed to begin.
Sincerely,
Anthony Thomas
"MikeWalsh" <mwalsh9815@.gmail.com> wrote in message
news:1164073896.590746.191330@.h48g2000cwc.googlegroups.com...
> What sort of hardware are you using? How are your disks configured? Are
> there any events or issues with your I/O controllers? How is your I/O
> solution utilizing it's write cache?
> I agree that you should be able to restart whenver you want. I have
> several SQL installations in my environment and have never had an issue
> with a normal shut down routine. Can you do a manual reboot of the
> server watching it (by manual I mean not automated but going through
> start/shutdown) and see what happens?
> Just a point, while you should be able to reboot whenver you want
> (gracefully and even safely under some not so graceful situations
> should not be a problem and has not in my experience with my servers),
> it is generally not always a best practice to reboot your database
> servers.
> For one thing there is no need to if you have a secure server and have
> only SQL Server running on it. In most of the configurations out there
> with only SQL running, you won't see a memory leak or any other issue
> requiring reboot. Also SQL works hard to build it's caches with data
> and compiled query execution plans that are optimized for your data and
> workload. Everytime you reboot you clear this cache meaning that after
> restart the server needs to first optimize and save all the plans again
> as well as add the frequently accessed data into the data cache. Not a
> huge deal but just some items to think about.
> Please give more details regarding my above questions and we will see
> what we can offer.
> -Mike Walsh
> SQL DBA
>
> paul.esposito@.comcast.net wrote:
> > Tibor,
> >
> > Thanks for the response. But according to the user and the event logs,
> > a normal shutdown was requested. i.e. The user requested Windows to
> > shutdown. This should have sent a message to all the services to stop.
> > Yet I don't see that request in the SQL Server log. But the event log
> > does show that a normal shutdown was requested of the OS.
> >
> > Thanks again.
> >
> >
> >
> > Tibor Karaszi wrote:
> > > The problem is in the nature of hardware. A page it 8KB. The disks
typically uses a sector as the
> > > level of atomic I/O. A sector is typically 512 bytes. I.e., a page is
typically 16 sectors. A power
> > > failure (or hard stop) in the middle of a page write will cause a
partially written page (a torn
> > > page). Having HW write cache *with a properly implemented battery
backup* will minimize the risk of
> > > a torn page (as long as the battery backup does its job).
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> > >
> > > <paul.esposito@.comcast.net> wrote in message
> > > news:1164056550.793217.94860@.h54g2000cwb.googlegroups.com...
> > > > Well, corporate standard is one reason and other systems reside on
the
> > > > server and need to be rebooted.
> > > >
> > > > I'm going to incorporate a netstop into the procedures. I was just
> > > > wondering if this was a common problem.
> > > >
> > > > Regardless of reason, whether I reboot the server once a week or
once
> > > > every year, if the database corrupts on that shutdown there seems to
be
> > > > an issue. I say this because I was told that Microsoft says W2K3
and
> > > > SQL Server are tuned not to need a reboot and that they should be
left
> > > > running. Well some of our servers can't be left running all the
time
> > > > Hurricanes, power outages ... Just seems like a bug to me.
> > > >
> > > > Thanks for the input though. I won't fight it, I'll just do a net
> > > > stop.
> > > >
> > > >
> > > > John Bell wrote:
> > > >> Hi
> > > >>
> > > >> It could be that SQL Server is not responding in time to the
request to
> > > >> terminate.
> > > >> Could you do a NET STOP on the SQL Server Services before shutting
down.
> > > >> Why do you need to shut down each week?
> > > >>
> > > >> John
> > > >>
> > > >> "paul.esposito@.comcast.net" wrote:
> > > >>
> > > >> > Hey Guys,
> > > >> >
> > > >> > Looking to see if anyone has experienced this and if there is
something
> > > >> > we are doing wrong. We have a weekly process to reboot our
windows
> > > >> > servers (we have 100's). The process is a normal windows
shutdown.
> > > >> > Yet weekly one of our servers will come back up and have a 'Torn
Page'
> > > >> > error, then get marked suspect. In investigating this I notice
that
> > > >> > the when the process works, there is a message in the SQL Server
error
> > > >> > log that says 'SQL Server is terminating due to Server shutdown'.
> > > >> > Yet in the cases where it ends up suspect the message is not in
the
> > > >> > log. So to me it looks like the SQL Server is getting shutdown
hard,
> > > >> > but why?
> > > >> >
> > > >> > I end up just restoring the 'Suspect' database, but I'm wondering
why
> > > >> > the process sporatically fails on some servers. Is there a
setting I'm
> > > >> > missing?
> > > >> >
> > > >> > Some specifics about the environment is that these are all
Windows 2003
> > > >> > servers running SQL Server 2000 SP3 (it also occurs on Desktop
> > > >> > Edition).
> > > >> >
> > > >> > Thanks in advance for any assistance.
> > > >> >
> > > >> >
> > > >
>|||Perhaps this SQL Server instance has been configured with a high value for sp_configure, recovery
interval? Less frequent checkpoint should mean more work for each checkpoint, including the one
submitted for a shutdown...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message news:uRmRmATDHHA.952@.TK2MSFTNGP03.phx.gbl...
> Unfortunately, a Server Shutdown request does exactly this: a request for
> NET STOP on every running service. The problem is that there exists a time
> out value. When SQL Server receives this message, it begins its shutdown
> phase, which issues stops all new connections and issues a hard CHECKPOINT
> of all databases.
> Typically during the maintenance windows (especially the database REORGs),
> this stop request could take quite a bit of time (even more on the start up
> and recovery process).
> Nearly the only way you are going to get a torn page from this (as opposed
> to a lot of recovery rollback operations) is that the disks were powered
> down before the cache committed.
> 1. Plan your database maintenance around these weekly system restarts.
> 2. Disable controller and Windows disk caching unless you can guarantee
> controller power protection.
> I would also agree with most everyone else that a DBMS should be on a
> dedicated system, and, which case, should be very little justification for
> weekly restarts.
> Now, in our Data Center, we have monthly OS updates that are deployed. For
> this, we (the DBA team) have demanded subsequent system restarts (mainly
> because we believe patches leave PendingFileRename operations), but that is
> only on a monthly basis, and we shut down database service before the server
> team is allowed to begin.
> Sincerely,
>
> Anthony Thomas
>
> --
> "MikeWalsh" <mwalsh9815@.gmail.com> wrote in message
> news:1164073896.590746.191330@.h48g2000cwc.googlegroups.com...
>> What sort of hardware are you using? How are your disks configured? Are
>> there any events or issues with your I/O controllers? How is your I/O
>> solution utilizing it's write cache?
>> I agree that you should be able to restart whenver you want. I have
>> several SQL installations in my environment and have never had an issue
>> with a normal shut down routine. Can you do a manual reboot of the
>> server watching it (by manual I mean not automated but going through
>> start/shutdown) and see what happens?
>> Just a point, while you should be able to reboot whenver you want
>> (gracefully and even safely under some not so graceful situations
>> should not be a problem and has not in my experience with my servers),
>> it is generally not always a best practice to reboot your database
>> servers.
>> For one thing there is no need to if you have a secure server and have
>> only SQL Server running on it. In most of the configurations out there
>> with only SQL running, you won't see a memory leak or any other issue
>> requiring reboot. Also SQL works hard to build it's caches with data
>> and compiled query execution plans that are optimized for your data and
>> workload. Everytime you reboot you clear this cache meaning that after
>> restart the server needs to first optimize and save all the plans again
>> as well as add the frequently accessed data into the data cache. Not a
>> huge deal but just some items to think about.
>> Please give more details regarding my above questions and we will see
>> what we can offer.
>> -Mike Walsh
>> SQL DBA
>>
>> paul.esposito@.comcast.net wrote:
>> > Tibor,
>> >
>> > Thanks for the response. But according to the user and the event logs,
>> > a normal shutdown was requested. i.e. The user requested Windows to
>> > shutdown. This should have sent a message to all the services to stop.
>> > Yet I don't see that request in the SQL Server log. But the event log
>> > does show that a normal shutdown was requested of the OS.
>> >
>> > Thanks again.
>> >
>> >
>> >
>> > Tibor Karaszi wrote:
>> > > The problem is in the nature of hardware. A page it 8KB. The disks
> typically uses a sector as the
>> > > level of atomic I/O. A sector is typically 512 bytes. I.e., a page is
> typically 16 sectors. A power
>> > > failure (or hard stop) in the middle of a page write will cause a
> partially written page (a torn
>> > > page). Having HW write cache *with a properly implemented battery
> backup* will minimize the risk of
>> > > a torn page (as long as the battery backup does its job).
>> > >
>> > > --
>> > > Tibor Karaszi, SQL Server MVP
>> > > http://www.karaszi.com/sqlserver/default.asp
>> > > http://www.solidqualitylearning.com/
>> > >
>> > >
>> > > <paul.esposito@.comcast.net> wrote in message
>> > > news:1164056550.793217.94860@.h54g2000cwb.googlegroups.com...
>> > > > Well, corporate standard is one reason and other systems reside on
> the
>> > > > server and need to be rebooted.
>> > > >
>> > > > I'm going to incorporate a netstop into the procedures. I was just
>> > > > wondering if this was a common problem.
>> > > >
>> > > > Regardless of reason, whether I reboot the server once a week or
> once
>> > > > every year, if the database corrupts on that shutdown there seems to
> be
>> > > > an issue. I say this because I was told that Microsoft says W2K3
> and
>> > > > SQL Server are tuned not to need a reboot and that they should be
> left
>> > > > running. Well some of our servers can't be left running all the
> time
>> > > > Hurricanes, power outages ... Just seems like a bug to me.
>> > > >
>> > > > Thanks for the input though. I won't fight it, I'll just do a net
>> > > > stop.
>> > > >
>> > > >
>> > > > John Bell wrote:
>> > > >> Hi
>> > > >>
>> > > >> It could be that SQL Server is not responding in time to the
> request to
>> > > >> terminate.
>> > > >> Could you do a NET STOP on the SQL Server Services before shutting
> down.
>> > > >> Why do you need to shut down each week?
>> > > >>
>> > > >> John
>> > > >>
>> > > >> "paul.esposito@.comcast.net" wrote:
>> > > >>
>> > > >> > Hey Guys,
>> > > >> >
>> > > >> > Looking to see if anyone has experienced this and if there is
> something
>> > > >> > we are doing wrong. We have a weekly process to reboot our
> windows
>> > > >> > servers (we have 100's). The process is a normal windows
> shutdown.
>> > > >> > Yet weekly one of our servers will come back up and have a 'Torn
> Page'
>> > > >> > error, then get marked suspect. In investigating this I notice
> that
>> > > >> > the when the process works, there is a message in the SQL Server
> error
>> > > >> > log that says 'SQL Server is terminating due to Server shutdown'.
>> > > >> > Yet in the cases where it ends up suspect the message is not in
> the
>> > > >> > log. So to me it looks like the SQL Server is getting shutdown
> hard,
>> > > >> > but why?
>> > > >> >
>> > > >> > I end up just restoring the 'Suspect' database, but I'm wondering
> why
>> > > >> > the process sporatically fails on some servers. Is there a
> setting I'm
>> > > >> > missing?
>> > > >> >
>> > > >> > Some specifics about the environment is that these are all
> Windows 2003
>> > > >> > servers running SQL Server 2000 SP3 (it also occurs on Desktop
>> > > >> > Edition).
>> > > >> >
>> > > >> > Thanks in advance for any assistance.
>> > > >> >
>> > > >> >
>> > > >
>|||Certainly a good place to examine. But, more often than not, especially if
this is in the evening on a weekend/Friday evening, the Index Rebuilds are
racking up large transactions. If the database(s) is(are) of any
appreciable size, then it could "hang" for awhile before letting go.
Anthony Thomas
--
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23dwmyoYDHHA.4144@.TK2MSFTNGP06.phx.gbl...
> Perhaps this SQL Server instance has been configured with a high value for
sp_configure, recovery
> interval? Less frequent checkpoint should mean more work for each
checkpoint, including the one
> submitted for a shutdown...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:uRmRmATDHHA.952@.TK2MSFTNGP03.phx.gbl...
> > Unfortunately, a Server Shutdown request does exactly this: a request
for
> > NET STOP on every running service. The problem is that there exists a
time
> > out value. When SQL Server receives this message, it begins its
shutdown
> > phase, which issues stops all new connections and issues a hard
CHECKPOINT
> > of all databases.
> >
> > Typically during the maintenance windows (especially the database
REORGs),
> > this stop request could take quite a bit of time (even more on the start
up
> > and recovery process).
> >
> > Nearly the only way you are going to get a torn page from this (as
opposed
> > to a lot of recovery rollback operations) is that the disks were powered
> > down before the cache committed.
> >
> > 1. Plan your database maintenance around these weekly system restarts.
> > 2. Disable controller and Windows disk caching unless you can guarantee
> > controller power protection.
> >
> > I would also agree with most everyone else that a DBMS should be on a
> > dedicated system, and, which case, should be very little justification
for
> > weekly restarts.
> >
> > Now, in our Data Center, we have monthly OS updates that are deployed.
For
> > this, we (the DBA team) have demanded subsequent system restarts (mainly
> > because we believe patches leave PendingFileRename operations), but that
is
> > only on a monthly basis, and we shut down database service before the
server
> > team is allowed to begin.
> >
> > Sincerely,
> >
> >
> > Anthony Thomas
> >
> >
> > --
> >
> > "MikeWalsh" <mwalsh9815@.gmail.com> wrote in message
> > news:1164073896.590746.191330@.h48g2000cwc.googlegroups.com...
> >> What sort of hardware are you using? How are your disks configured? Are
> >> there any events or issues with your I/O controllers? How is your I/O
> >> solution utilizing it's write cache?
> >>
> >> I agree that you should be able to restart whenver you want. I have
> >> several SQL installations in my environment and have never had an issue
> >> with a normal shut down routine. Can you do a manual reboot of the
> >> server watching it (by manual I mean not automated but going through
> >> start/shutdown) and see what happens?
> >>
> >> Just a point, while you should be able to reboot whenver you want
> >> (gracefully and even safely under some not so graceful situations
> >> should not be a problem and has not in my experience with my servers),
> >> it is generally not always a best practice to reboot your database
> >> servers.
> >>
> >> For one thing there is no need to if you have a secure server and have
> >> only SQL Server running on it. In most of the configurations out there
> >> with only SQL running, you won't see a memory leak or any other issue
> >> requiring reboot. Also SQL works hard to build it's caches with data
> >> and compiled query execution plans that are optimized for your data and
> >> workload. Everytime you reboot you clear this cache meaning that after
> >> restart the server needs to first optimize and save all the plans again
> >> as well as add the frequently accessed data into the data cache. Not a
> >> huge deal but just some items to think about.
> >>
> >> Please give more details regarding my above questions and we will see
> >> what we can offer.
> >>
> >> -Mike Walsh
> >> SQL DBA
> >>
> >>
> >> paul.esposito@.comcast.net wrote:
> >> > Tibor,
> >> >
> >> > Thanks for the response. But according to the user and the event
logs,
> >> > a normal shutdown was requested. i.e. The user requested Windows to
> >> > shutdown. This should have sent a message to all the services to
stop.
> >> > Yet I don't see that request in the SQL Server log. But the event
log
> >> > does show that a normal shutdown was requested of the OS.
> >> >
> >> > Thanks again.
> >> >
> >> >
> >> >
> >> > Tibor Karaszi wrote:
> >> > > The problem is in the nature of hardware. A page it 8KB. The disks
> > typically uses a sector as the
> >> > > level of atomic I/O. A sector is typically 512 bytes. I.e., a page
is
> > typically 16 sectors. A power
> >> > > failure (or hard stop) in the middle of a page write will cause a
> > partially written page (a torn
> >> > > page). Having HW write cache *with a properly implemented battery
> > backup* will minimize the risk of
> >> > > a torn page (as long as the battery backup does its job).
> >> > >
> >> > > --
> >> > > Tibor Karaszi, SQL Server MVP
> >> > > http://www.karaszi.com/sqlserver/default.asp
> >> > > http://www.solidqualitylearning.com/
> >> > >
> >> > >
> >> > > <paul.esposito@.comcast.net> wrote in message
> >> > > news:1164056550.793217.94860@.h54g2000cwb.googlegroups.com...
> >> > > > Well, corporate standard is one reason and other systems reside
on
> > the
> >> > > > server and need to be rebooted.
> >> > > >
> >> > > > I'm going to incorporate a netstop into the procedures. I was
just
> >> > > > wondering if this was a common problem.
> >> > > >
> >> > > > Regardless of reason, whether I reboot the server once a week or
> > once
> >> > > > every year, if the database corrupts on that shutdown there seems
to
> > be
> >> > > > an issue. I say this because I was told that Microsoft says W2K3
> > and
> >> > > > SQL Server are tuned not to need a reboot and that they should be
> > left
> >> > > > running. Well some of our servers can't be left running all the
> > time
> >> > > > Hurricanes, power outages ... Just seems like a bug to me.
> >> > > >
> >> > > > Thanks for the input though. I won't fight it, I'll just do a
net
> >> > > > stop.
> >> > > >
> >> > > >
> >> > > > John Bell wrote:
> >> > > >> Hi
> >> > > >>
> >> > > >> It could be that SQL Server is not responding in time to the
> > request to
> >> > > >> terminate.
> >> > > >> Could you do a NET STOP on the SQL Server Services before
shutting
> > down.
> >> > > >> Why do you need to shut down each week?
> >> > > >>
> >> > > >> John
> >> > > >>
> >> > > >> "paul.esposito@.comcast.net" wrote:
> >> > > >>
> >> > > >> > Hey Guys,
> >> > > >> >
> >> > > >> > Looking to see if anyone has experienced this and if there is
> > something
> >> > > >> > we are doing wrong. We have a weekly process to reboot our
> > windows
> >> > > >> > servers (we have 100's). The process is a normal windows
> > shutdown.
> >> > > >> > Yet weekly one of our servers will come back up and have a
'Torn
> > Page'
> >> > > >> > error, then get marked suspect. In investigating this I
notice
> > that
> >> > > >> > the when the process works, there is a message in the SQL
Server
> > error
> >> > > >> > log that says 'SQL Server is terminating due to Server
shutdown'.
> >> > > >> > Yet in the cases where it ends up suspect the message is not
in
> > the
> >> > > >> > log. So to me it looks like the SQL Server is getting
shutdown
> > hard,
> >> > > >> > but why?
> >> > > >> >
> >> > > >> > I end up just restoring the 'Suspect' database, but I'm
wondering
> > why
> >> > > >> > the process sporatically fails on some servers. Is there a
> > setting I'm
> >> > > >> > missing?
> >> > > >> >
> >> > > >> > Some specifics about the environment is that these are all
> > Windows 2003
> >> > > >> > servers running SQL Server 2000 SP3 (it also occurs on Desktop
> >> > > >> > Edition).
> >> > > >> >
> >> > > >> > Thanks in advance for any assistance.
> >> > > >> >
> >> > > >> >
> >> > > >
> >>
> >
> >
>
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment