Database marked Suspect after normabl Windows Shutdown

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:[vbcol=seagreen]
> 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:
>|||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 typica
lly 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* wi
ll 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:
>|||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:[vbcol=seagreen]
> The problem is in the nature of hardware. A page it 8KB. The disks typical
ly uses a sector as the
> level of atomic I/O. A sector is typically 512 bytes. I.e., a page is typi
cally 16 sectors. A power
> failure (or hard stop) in the middle of a page write will cause a partiall
y 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...|||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:[vbcol=seagreen]
> 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:|||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:
typically uses a sector as the[vbcol=seagreen]
typically 16 sectors. A power[vbcol=seagreen]
partially written page (a torn[vbcol=seagreen]
backup* will minimize the risk of[vbcol=seagreen]
the[vbcol=seagreen]
once[vbcol=seagreen]
be[vbcol=seagreen]
and[vbcol=seagreen]
left[vbcol=seagreen]
time[vbcol=seagreen]
request to[vbcol=seagreen]
down.[vbcol=seagreen]
something[vbcol=seagreen]
windows[vbcol=seagreen]
shutdown.[vbcol=seagreen]
Page'[vbcol=seagreen]
that[vbcol=seagreen]
error[vbcol=seagreen]
the[vbcol=seagreen]
hard,[vbcol=seagreen]
why[vbcol=seagreen]
setting I'm[vbcol=seagreen]
Windows 2003[vbcol=seagreen]
>|||Perhaps this SQL Server instance has been configured with a high value for s
p_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 tim
e
> 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 u
p
> 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. Fo
r
> this, we (the DBA team) have demanded subsequent system restarts (mainly
> because we believe patches leave PendingFileRename operations), but that i
s
> only on a monthly basis, and we shut down database service before the serv
er
> team is allowed to begin.
> Sincerely,
>
> Anthony Thomas
>
> --
> "MikeWalsh" <mwalsh9815@.gmail.com> wrote in message
> news:1164073896.590746.191330@.h48g2000cwc.googlegroups.com...
> typically uses a sector as the
> typically 16 sectors. A power
> partially written page (a torn
> backup* will minimize the risk of
> the
> once
> be
> and
> left
> time
> request to
> down.
> something
> windows
> shutdown.
> Page'
> that
> error
> the
> hard,
> why
> setting I'm
> Windows 2003
>|||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...
for[vbcol=seagreen]
time[vbcol=seagreen]
shutdown[vbcol=seagreen]
CHECKPOINT[vbcol=seagreen]
REORGs),[vbcol=seagreen]
up[vbcol=seagreen]
opposed[vbcol=seagreen]
for[vbcol=seagreen]
For[vbcol=seagreen]
is[vbcol=seagreen]
server[vbcol=seagreen]
logs,[vbcol=seagreen]
stop.[vbcol=seagreen]
log[vbcol=seagreen]
is[vbcol=seagreen]
on[vbcol=seagreen]
just[vbcol=seagreen]
to[vbcol=seagreen]
net[vbcol=seagreen]
shutting[vbcol=seagreen]
'Torn[vbcol=seagreen]
notice[vbcol=seagreen]
Server[vbcol=seagreen]
shutdown'.[vbcol=seagreen]
in[vbcol=seagreen]
shutdown[vbcol=seagreen]
wondering[vbcol=seagreen]
>