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

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

Database maintenance using DBCC DBREINDEX

Hi Jo,
The command sp_msForEachTable is no longer supported my
Microsoft. This means on the next release of SQL Server it
may or may not be part of it.
If its not supported then there are no BOL's for it.
J

>--Original Message--
>Hi,
>Currently, I use sp_msForEachTable to reindex every table
in one of my
>databases. However I did not find any information
regarding this stored
>procedure in BOL. Is it safe to use, or why isn't it
documented?
>EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
>Yours sincerely,
>Jo Segers.
>
>.
>Thanks,
Do you know if there is a similar stored procedure that is supported?
Jo.
"Julie" <anonymous@.discussions.microsoft.com> schreef in bericht
news:201f01c42789$52e4ddb0$a601280a@.phx.gbl...[vbcol=seagreen]
> Hi Jo,
> The command sp_msForEachTable is no longer supported my
> Microsoft. This means on the next release of SQL Server it
> may or may not be part of it.
> If its not supported then there are no BOL's for it.
> J
>
> in one of my
> regarding this stored
> documented?|||I'm sure there exists such on the Net, but if they are free, I doubt that yo
u will have formal support form
the one who wrote it. AFAIK, there is no such in SQL Server. But, again, it
is not difficult to write one
yourself, using a cursor, if you have a little bit if TSQL programming exper
ience.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jo Segers" <segers_jo@.hotmail.com> wrote in message news:%23lKZD$4JEHA.2624@.TK2MSFTNGP09.ph
x.gbl...
> Thanks,
> Do you know if there is a similar stored procedure that is supported?
> Jo.
> "Julie" <anonymous@.discussions.microsoft.com> schreef in bericht
> news:201f01c42789$52e4ddb0$a601280a@.phx.gbl...
>|||Ok,
Thanks for the information, I will write one myself. I wonder why they
didn't implement this sp in SQLserver 2000 because it is quite usefull.
Thanks for the support,
Jo.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schreef
in bericht news:uYzs9B5JEHA.208@.tk2msftngp13.phx.gbl...
> I'm sure there exists such on the Net, but if they are free, I doubt that
you will have formal support form
> the one who wrote it. AFAIK, there is no such in SQL Server. But, again,
it is not difficult to write one
> yourself, using a cursor, if you have a little bit if TSQL programming
experience.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Jo Segers" <segers_jo@.hotmail.com> wrote in message
news:%23lKZD$4JEHA.2624@.TK2MSFTNGP09.phx.gbl...
>|||As far as I know there is no Microsoft official version
that does the same, sorry.
However you can get all the user tables from your database
by doing
select Name from sysobjects where xtype = 'U'
J

>--Original Message--
>Thanks,
>Do you know if there is a similar stored procedure that
is supported?
>Jo.
>"Julie" <anonymous@.discussions.microsoft.com> schreef in
bericht
>news:201f01c42789$52e4ddb0$a601280a@.phx.gbl...
it[vbcol=seagreen]
table[vbcol=seagreen]
>
>.
>|||Thanks for the help.
"Julie" <anonymous@.discussions.microsoft.com> schreef in bericht
news:216f01c427a7$9b1e6a60$a601280a@.phx.gbl...[vbcol=seagreen]
> As far as I know there is no Microsoft official version
> that does the same, sorry.
> However you can get all the user tables from your database
> by doing
> select Name from sysobjects where xtype = 'U'
> J
>
> is supported?
> bericht
> it
> table