database 'master4IDR' in load msg in log

I am seeing the message 'bypassing recovery of database master4IDR because it is marked IN LOAD' in my sql log. But I have not restored any of the sys dbs. The msg is appearing every day, but everything else seems to be fine.

What is causing it? Should I worry about it ? (I do) and what can I do about it?

I just searched a bit and found this discussion:

http://www.sqlteam.com/Forums/post.asp?method=ReplyQuote&REPLY_ID=162149&TOPIC_ID=48723&FORUM_ID=6

From this post:

"I found that the model4IDR thing is the database created by Veritas BackExec which has the Interlligent Disaster Recovery option. The Veritas backup process will use these databases during backup. "

Thanks,
Sam Lester (MSFT)

database 'master4IDR' in load msg in log

I am seeing the message 'bypassing recovery of database master4IDR because it is marked IN LOAD' in my sql log. But I have not restored any of the sys dbs. The msg is appearing every day, but everything else seems to be fine.

What is causing it? Should I worry about it ? (I do) and what can I do about it?

I just searched a bit and found this discussion:

http://www.sqlteam.com/Forums/post.asp?method=ReplyQuote&REPLY_ID=162149&TOPIC_ID=48723&FORUM_ID=6

From this post:

"I found that the model4IDR thing is the database created by Veritas BackExec which has the Interlligent Disaster Recovery option. The Veritas backup process will use these databases during backup. "

Thanks,
Sam Lester (MSFT)

Database Marked suspect with Error 3314

Hi,
I have a database that is constantly marked suspect with Error: 3314,
Severity: 21, State: 4 and it says "Error while undoing logged operation in
database 'prod_db'. Error at log record ID (81:7137:38)..". This database is
on SQL Server 2000 with SP3 on WIN2k AS with SP2. After it prints this
error, I also get Error: 9001, Severity: 21, State: 1 that states " The log
for database 'prod_db' is not available.. After I get the error message, the
database recovers itself and comes back on line. I have run DBCC commands
including the CHECKFILEGROUP and they are all coming up clean.
If someone can throw some light, on this I will greatly appreciate it.
Database is less than 1 GB and for data and log, it has unlimited file
growth. Recovery model for the database is "Full."
Thanks,
Sanjay.Are those the only errormessages? Is autoclose on for the database?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Sanjay" <s_dhakhwa@.hotmail.com> wrote in message news:eF$UJlylDHA.684@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a database that is constantly marked suspect with Error: 3314,
> Severity: 21, State: 4 and it says "Error while undoing logged operation in
> database 'prod_db'. Error at log record ID (81:7137:38)..". This database is
> on SQL Server 2000 with SP3 on WIN2k AS with SP2. After it prints this
> error, I also get Error: 9001, Severity: 21, State: 1 that states " The log
> for database 'prod_db' is not available.. After I get the error message, the
> database recovers itself and comes back on line. I have run DBCC commands
> including the CHECKFILEGROUP and they are all coming up clean.
> If someone can throw some light, on this I will greatly appreciate it.
> Database is less than 1 GB and for data and log, it has unlimited file
> growth. Recovery model for the database is "Full."
> Thanks,
> Sanjay.
>|||Hi Sanjay,
According to my research, this issue is mostly like a database corruption
problem. Do you have any backup of this database? If so, I would like you
to try to restore the database from the backup.
For additional information regarding restoring the database, please refer
to the following article on SQL Server Books Online.
Topic:"RESTORE"
Topic:"How to restore a database backup (Transact-SQL)"
Also, due to the complexity of this issue, it would be best to contact
Microsoft Product Support Services via telephone so that a dedicated
Support Professional can assist with your request. To obtain the phone
numbers for specific technology requests please take a look at the web site
listed below.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Thank you for all your suggestion.
I can restore from the backups of the database on to a different server and
all the DBCCs come out clean. The database does not have "Auto close" option
turned on. We opened up the case with microsoft since this is a really
wiered case.
Again, thanks a lot for your help.
"Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
news:Y9gqoj6lDHA.1548@.cpmsftngxa06.phx.gbl...
> Hi Sanjay,
> According to my research, this issue is mostly like a database corruption
> problem. Do you have any backup of this database? If so, I would like you
> to try to restore the database from the backup.
> For additional information regarding restoring the database, please refer
> to the following article on SQL Server Books Online.
> Topic:"RESTORE"
> Topic:"How to restore a database backup (Transact-SQL)"
> Also, due to the complexity of this issue, it would be best to contact
> Microsoft Product Support Services via telephone so that a dedicated
> Support Professional can assist with your request. To obtain the phone
> numbers for specific technology requests please take a look at the web
site
> listed below.
> http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS
> Regards,
> Michael Shao
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
>

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:
|||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 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.googlegro ups.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.googlegr oups.com...[vbcol=seagreen]
> 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
>
|||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...[vbcol=seagreen]
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
>

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:
> 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.
> >> > > >> >
> >> > > >> >
> >> > > >
> >>
> >
> >
>

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]
>

Database marked suspect

I have a database server and a 3 workstations running on
Windows 2000. Due to a power failure (UPS included), the
whole system rebooted. However, the workstations cannot
acces the database. Upon checking the server directory,
the database is there (C:\Program Files\Microsoft SQL
Server\MSSQL\Data). Before attempting anything else we
tried to make a backup copy of the directory; all files
copy except ContinuumLog.ldf, which shows a CRC error and
cannot copy.
Using the Enterprise Manger we tried to look at the
database and found that it is marked "Suspect" by SQL.
Checking the Help menu, there seem to be a number of
procedures to change the "Suspect" mark and recover the
database. As you may have guessed by now, there are no
backups in the disk.
Can anyone help us? Ideally we need a procedure, or,
failing that, where can we look up a procedure to recover
the database.
Thank you all,
TaliaFrom EM or Query Analyzer, are you able to do anything ? If you can, create
a new database and copy all the data from the bad db to the new db.
-Jimmy
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...|||Hi,
Please restore from good backup if you have CRC errors. If you do not have
the Backup try the below steps.
1. Update the sysdatabases to update to Emergency mode. This will not use
LOG files in start up
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
2. Restart sql server. now the database will be in emergency mode ( You
could access the database)
3. Create a new database and use DTS to transfer the data and object to the
new database.
4. Verify all the objects and data is available in new database.
Thanks
Hari
MCDBA
"Talia Sara-Lafosse" <saralafosse.t@.pucp.edu.pe> wrote in message
news:138a01c4853a$21db8b10$a301280a@.phx.gbl...
> I have a database server and a 3 workstations running on
> Windows 2000. Due to a power failure (UPS included), the
> whole system rebooted. However, the workstations cannot
> acces the database. Upon checking the server directory,
> the database is there (C:\Program Files\Microsoft SQL
> Server\MSSQL\Data). Before attempting anything else we
> tried to make a backup copy of the directory; all files
> copy except ContinuumLog.ldf, which shows a CRC error and
> cannot copy.
> Using the Enterprise Manger we tried to look at the
> database and found that it is marked "Suspect" by SQL.
> Checking the Help menu, there seem to be a number of
> procedures to change the "Suspect" mark and recover the
> database. As you may have guessed by now, there are no
> backups in the disk.
> Can anyone help us? Ideally we need a procedure, or,
> failing that, where can we look up a procedure to recover
> the database.
> Thank you all,
> Talia
>|||The problem is that i can not copy the damaged file; and i
dont't know what to do with the data with the LDF file
damaged.

>--Original Message--
>From EM or Query Analyzer, are you able to do anything ?
If you can, create a new database and copy all the data
from the bad db to the new db.
>-Jimmy
> ****************************************
******************
************
>Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
>Comprehensive, categorised, searchable collection of
links to ASP & ASP.NET resources...
>.
>

Database Marked Suspect

Hi,
What does a database marked suspect for recovery means and
how could I recover from this?
Thanks in advance
TengHi,
There are many possibilities for suspect status,
1. File being used by another processes during SQL server service start up
(mostly backup process)
2. Transaction Log file corruption
3. Data integrity issue
Solutions:
1. First one can be identified by SQL server logs, "it say file being used
by another process". In this case you case use
sp_resetstatus <dbname> procedure to reset the status and restart sql
server. Now the database wil be online
2. Second case, you can start the sql server in Emergency mode (Update the
sysdatabase table .. Status column to 32768 for the affected database)
3. 3rd case try to execute DBCC CHeckDB with repair_rebuild option. If not
rectified contact Microsoft PSS (Support)
Thanks
Hari
MCDBA
"teng" <anonymous@.discussions.microsoft.com> wrote in message
news:388801c3fdb8$68782560$a301280a@.phx.gbl...
> Hi,
> What does a database marked suspect for recovery means and
> how could I recover from this?
> Thanks in advance
> Teng|||Hi Hari,
Thanks for the reply.
Does these also apply to sql 7.0? I'm trying to do the
second option but can't find the sysdatabase table. Where
should I look for this table.
After starting the server in emergency mode, will it
correct the problem now? Will I be able to start it
normally the next time around?
Teng
>--Original Message--
>Hi,
>There are many possibilities for suspect status,
>1. File being used by another processes during SQL server
service start up
>(mostly backup process)
>2. Transaction Log file corruption
>3. Data integrity issue
>Solutions:
>1. First one can be identified by SQL server logs, "it
say file being used
>by another process". In this case you case use
>sp_resetstatus <dbname> procedure to reset the status and
restart sql
>server. Now the database wil be online
>2. Second case, you can start the sql server in Emergency
mode (Update the
>sysdatabase table .. Status column to 32768 for the
affected database)
>3. 3rd case try to execute DBCC CHeckDB with
repair_rebuild option. If not
>rectified contact Microsoft PSS (Support)
>Thanks
>Hari
>MCDBA
>
>
>"teng" <anonymous@.discussions.microsoft.com> wrote in
message
>news:388801c3fdb8$68782560$a301280a@.phx.gbl...
and
>
>.
>|||Hi,
1. Does these also apply to sql 7.0?
Yes.
2. Where should I look for this table.
Sysdatabases table is in Master database.
Before updating sysdatabases table, Check whether your filed is full. That
also will create a suspect status.
a. If the data file (MDF) is fiull then, you can execute procedure
"sp_add_data_file_recover_suspect_db" (Please refer Books online)
b. If the Log file (LDF)ull then, you can execute procedure
"sp_add_log_file_recover_suspect_db" (Please refer Books online)
Incase above steps fails then do,
Will I be able to start it normally the next time around?
No, You may need pull all the objects and Data into a new database using
DTS.
Thanks
Hari
MCDBA
"teng" <anonymous@.discussions.microsoft.com> wrote in message
news:381401c3fdd6$193331c0$a401280a@.phx.gbl...
> Hi Hari,
> Thanks for the reply.
> Does these also apply to sql 7.0? I'm trying to do the
> second option but can't find the sysdatabase table. Where
> should I look for this table.
> After starting the server in emergency mode, will it
> correct the problem now? Will I be able to start it
> normally the next time around?
> Teng
> service start up
> say file being used
> restart sql
> mode (Update the
> affected database)
> repair_rebuild option. If not
> message
> and|||Here are the general recommendations for handling a suspect or corrupt
database:
0. Ensure you have a backup strategy that you can use to recover from
hardware failures (including corruption). I recommend performing both
database and log backup in most situations.
1. If you can run DBCC CHECKDB against the database: Search Books Online and
KB for the error numbers that CHECKDB gives you. There might be specific
info for that type of error.
2. Find out why this happened. Check eventlog, do HW diagnostics etc.;
search Books Online and KB for those errors. You don't want this to happen
again! If the database is suspect, the file might have been in use by for
instance an anti-virus program and restarting SQL Server might be all that
is needed - but you still want to read logs etc to find out what happened.
3. If there is a hardware problem, ensure the faulty hardware is replaced.
4. Backup the log. This assumes that log backup schedule is in place, of
course. If the database is suspect, then the NO_TRUNCATE option for the
RESTORE command must be used. Also, you might want to do a file backup of
the mdf and ldf files, for extra safety.
5. Restore is the best thing to do now. If you managed to backup log as per
step 4, then you will most probably have zero dataloss. You should restore
the latest clean database backup and the subsequent log backups including
the one taken in above step.
If the database isn't suspect, then DBCC with a REPAIR option might be a
secondary option but this will often result in loss of data. Additional
solutions, depending on the errors, may be to manually rebuild non-clustered
indexes, manually drop and reload a table if the data is static, and so on.
If the database is suspect, a secondary option can be to try to "un-suspect"
the database using sp_resetstatus. Read about it (books online, KB, google
etc). It might help but if the database is too damaged, it might just pop
back to suspect again. There's also something called "emergency mode" which
is a "panic" status you can set in order to try to get data out of a damaged
database. I think the name of that option speaks for itself. Again search
the net for info.
If you feel uncertain with above steps, I recommend letting MS hand-hold you
through the steps appropriate for your particular situation.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"teng" <anonymous@.discussions.microsoft.com> wrote in message
news:388801c3fdb8$68782560$a301280a@.phx.gbl...
> Hi,
> What does a database marked suspect for recovery means and
> how could I recover from this?
> Thanks in advance
> Teng

Database marked suspect

I have a database server and a 3 workstations running on
Windows 2000. Due to a power failure (UPS included), the
whole system rebooted. However, the workstations cannot
acces the database. Upon checking the server directory,
the database is there (C:\Program Files\Microsoft SQL
Server\MSSQL\Data). Before attempting anything else we
tried to make a backup copy of the directory; all files
copy except ContinuumLog.ldf, which shows a CRC error and
cannot copy.
Using the Enterprise Manger we tried to look at the
database and found that it is marked "Suspect" by SQL.
Checking the Help menu, there seem to be a number of
procedures to change the "Suspect" mark and recover the
database. As you may have guessed by now, there are no
backups in the disk.
Can anyone help us? Ideally we need a procedure, or,
failing that, where can we look up a procedure to recover
the database.
Thank you all,
Talia
From EM or Query Analyzer, are you able to do anything ? If you can, create a new database and copy all the data from the bad db to the new db.
-Jimmy
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||Hi,
Please restore from good backup if you have CRC errors. If you do not have
the Backup try the below steps.
1. Update the sysdatabases to update to Emergency mode. This will not use
LOG files in start up
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
2. Restart sql server. now the database will be in emergency mode ( You
could access the database)
3. Create a new database and use DTS to transfer the data and object to the
new database.
4. Verify all the objects and data is available in new database.
Thanks
Hari
MCDBA
"Talia Sara-Lafosse" <saralafosse.t@.pucp.edu.pe> wrote in message
news:138a01c4853a$21db8b10$a301280a@.phx.gbl...
> I have a database server and a 3 workstations running on
> Windows 2000. Due to a power failure (UPS included), the
> whole system rebooted. However, the workstations cannot
> acces the database. Upon checking the server directory,
> the database is there (C:\Program Files\Microsoft SQL
> Server\MSSQL\Data). Before attempting anything else we
> tried to make a backup copy of the directory; all files
> copy except ContinuumLog.ldf, which shows a CRC error and
> cannot copy.
> Using the Enterprise Manger we tried to look at the
> database and found that it is marked "Suspect" by SQL.
> Checking the Help menu, there seem to be a number of
> procedures to change the "Suspect" mark and recover the
> database. As you may have guessed by now, there are no
> backups in the disk.
> Can anyone help us? Ideally we need a procedure, or,
> failing that, where can we look up a procedure to recover
> the database.
> Thank you all,
> Talia
>
|||The problem is that i can not copy the damaged file; and i
dont't know what to do with the data with the LDF file
damaged.

>--Original Message--
>From EM or Query Analyzer, are you able to do anything ?
If you can, create a new database and copy all the data
from the bad db to the new db.
>-Jimmy
>************************************************* *********
************
>Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
>Comprehensive, categorised, searchable collection of
links to ASP & ASP.NET resources...
>.
>

Database marked suspect

My primary database is currently marked suspect. I
believe it could be because the transaction log was
deleted. How can I restore the database to be
operational?
Any assistance is greatly appreciated!use this one sp_resetstatus [ @.DBName =3D ] 'database'
See BOL for more information om the same ...
-- HTH,
Vinod Kumar
MCSE, DBA, MCAD
http://www.extremeexperts.com/
"Michelle" <msarna@.starpower.net> wrote in message =news:042c01c3588e$c42cda60$a401280a@.phx.gbl...
> My primary database is currently marked suspect. I > believe it could be because the transaction log was > deleted. How can I restore the database to be > operational?
> > Any assistance is greatly appreciated!

Database Marked Suspect

Hi,
What does a database marked suspect for recovery means and
how could I recover from this?
Thanks in advance
TengHi,
There are many possibilities for suspect status,
1. File being used by another processes during SQL server service start up
(mostly backup process)
2. Transaction Log file corruption
3. Data integrity issue
Solutions:
1. First one can be identified by SQL server logs, "it say file being used
by another process". In this case you case use
sp_resetstatus <dbname> procedure to reset the status and restart sql
server. Now the database wil be online
2. Second case, you can start the sql server in Emergency mode (Update the
sysdatabase table .. Status column to 32768 for the affected database)
3. 3rd case try to execute DBCC CHeckDB with repair_rebuild option. If not
rectified contact Microsoft PSS (Support)
Thanks
Hari
MCDBA
"teng" <anonymous@.discussions.microsoft.com> wrote in message
news:388801c3fdb8$68782560$a301280a@.phx.gbl...
> Hi,
> What does a database marked suspect for recovery means and
> how could I recover from this?
> Thanks in advance
> Teng|||Hi Hari,
Thanks for the reply.
Does these also apply to sql 7.0? I'm trying to do the
second option but can't find the sysdatabase table. Where
should I look for this table.
After starting the server in emergency mode, will it
correct the problem now? Will I be able to start it
normally the next time around?
Teng
>--Original Message--
>Hi,
>There are many possibilities for suspect status,
>1. File being used by another processes during SQL server
service start up
>(mostly backup process)
>2. Transaction Log file corruption
>3. Data integrity issue
>Solutions:
>1. First one can be identified by SQL server logs, "it
say file being used
>by another process". In this case you case use
>sp_resetstatus <dbname> procedure to reset the status and
restart sql
>server. Now the database wil be online
>2. Second case, you can start the sql server in Emergency
mode (Update the
>sysdatabase table .. Status column to 32768 for the
affected database)
>3. 3rd case try to execute DBCC CHeckDB with
repair_rebuild option. If not
>rectified contact Microsoft PSS (Support)
>Thanks
>Hari
>MCDBA
>
>
>"teng" <anonymous@.discussions.microsoft.com> wrote in
message
>news:388801c3fdb8$68782560$a301280a@.phx.gbl...
>> Hi,
>> What does a database marked suspect for recovery means
and
>> how could I recover from this?
>> Thanks in advance
>> Teng
>
>.
>|||Hi,
1. Does these also apply to sql 7.0?
Yes.
2. Where should I look for this table.
Sysdatabases table is in Master database.
Before updating sysdatabases table, Check whether your filed is full. That
also will create a suspect status.
a. If the data file (MDF) is fiull then, you can execute procedure
"sp_add_data_file_recover_suspect_db" (Please refer Books online)
b. If the Log file (LDF)ull then, you can execute procedure
"sp_add_log_file_recover_suspect_db" (Please refer Books online)
Incase above steps fails then do,
Will I be able to start it normally the next time around?
No, You may need pull all the objects and Data into a new database using
DTS.
Thanks
Hari
MCDBA
"teng" <anonymous@.discussions.microsoft.com> wrote in message
news:381401c3fdd6$193331c0$a401280a@.phx.gbl...
> Hi Hari,
> Thanks for the reply.
> Does these also apply to sql 7.0? I'm trying to do the
> second option but can't find the sysdatabase table. Where
> should I look for this table.
> After starting the server in emergency mode, will it
> correct the problem now? Will I be able to start it
> normally the next time around?
> Teng
> >--Original Message--
> >Hi,
> >
> >There are many possibilities for suspect status,
> >
> >1. File being used by another processes during SQL server
> service start up
> >(mostly backup process)
> >
> >2. Transaction Log file corruption
> >
> >3. Data integrity issue
> >
> >Solutions:
> >
> >1. First one can be identified by SQL server logs, "it
> say file being used
> >by another process". In this case you case use
> >sp_resetstatus <dbname> procedure to reset the status and
> restart sql
> >server. Now the database wil be online
> >
> >2. Second case, you can start the sql server in Emergency
> mode (Update the
> >sysdatabase table .. Status column to 32768 for the
> affected database)
> >
> >3. 3rd case try to execute DBCC CHeckDB with
> repair_rebuild option. If not
> >rectified contact Microsoft PSS (Support)
> >
> >Thanks
> >Hari
> >MCDBA
> >
> >
> >
> >
> >
> >"teng" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:388801c3fdb8$68782560$a301280a@.phx.gbl...
> >> Hi,
> >>
> >> What does a database marked suspect for recovery means
> and
> >> how could I recover from this?
> >>
> >> Thanks in advance
> >> Teng
> >
> >
> >.
> >|||Here are the general recommendations for handling a suspect or corrupt
database:
0. Ensure you have a backup strategy that you can use to recover from
hardware failures (including corruption). I recommend performing both
database and log backup in most situations.
1. If you can run DBCC CHECKDB against the database: Search Books Online and
KB for the error numbers that CHECKDB gives you. There might be specific
info for that type of error.
2. Find out why this happened. Check eventlog, do HW diagnostics etc.;
search Books Online and KB for those errors. You don't want this to happen
again! If the database is suspect, the file might have been in use by for
instance an anti-virus program and restarting SQL Server might be all that
is needed - but you still want to read logs etc to find out what happened.
3. If there is a hardware problem, ensure the faulty hardware is replaced.
4. Backup the log. This assumes that log backup schedule is in place, of
course. If the database is suspect, then the NO_TRUNCATE option for the
RESTORE command must be used. Also, you might want to do a file backup of
the mdf and ldf files, for extra safety.
5. Restore is the best thing to do now. If you managed to backup log as per
step 4, then you will most probably have zero dataloss. You should restore
the latest clean database backup and the subsequent log backups including
the one taken in above step.
If the database isn't suspect, then DBCC with a REPAIR option might be a
secondary option but this will often result in loss of data. Additional
solutions, depending on the errors, may be to manually rebuild non-clustered
indexes, manually drop and reload a table if the data is static, and so on.
If the database is suspect, a secondary option can be to try to "un-suspect"
the database using sp_resetstatus. Read about it (books online, KB, google
etc). It might help but if the database is too damaged, it might just pop
back to suspect again. There's also something called "emergency mode" which
is a "panic" status you can set in order to try to get data out of a damaged
database. I think the name of that option speaks for itself. Again search
the net for info.
If you feel uncertain with above steps, I recommend letting MS hand-hold you
through the steps appropriate for your particular situation.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"teng" <anonymous@.discussions.microsoft.com> wrote in message
news:388801c3fdb8$68782560$a301280a@.phx.gbl...
> Hi,
> What does a database marked suspect for recovery means and
> how could I recover from this?
> Thanks in advance
> Teng|||HI Dear
It means there is no space avaialable for the database to
recover the database.
Sol.-- create some space on the same drive where ur
database is, alter the database and add a new file on it.
First of all detach ur database and restart the system,
then attach it again the add the file.
>--Original Message--
>Hi,
>What does a database marked suspect for recovery means
and
>how could I recover from this?
>Thanks in advance
>Teng
>.
>