Hi newsgroup,
in the last days we had a lot of trouble with our SQL-Server2000 database.
We moved to complete new Hardware. Now it's solved (i hope so),
but we want to be prepared for the future. Therefore i've a few questions:
During the normal maintenance of the database some consistency errors were d
etected.
The REPAIR_ALLOW_DATA_LOSS option was suggested by dbcc checkdb.
We had the luck, that not data loss was mentioned in the log file.
So, can we be really sure that there was no data loss?
If there's a data loss, how detailed is the information about the lost data?
After the first run with the REPAIR_ALLOW_DATA_LOSS option the next checkdb
detected still errors. (The first job wrote to the log file that all errors
were corrected)
The check suggested the REPAIR_REBUILD option.
For me the question is, why are there still errors after the first run with
the
REPAIR_ALLOW_DATA_LOSS option? How can we avoid to run the job with a REPAIR
_XXXXX option more
than one time.
Thank you & Regards,
Sven KrampeHi Sven,
The reason it sometimes needs to be run twice or more is that some errors
can mask other errors. There are many different integrity checks performed
on the various structures in the database. If a problem is detected with a
higher level structure, this may prevent lower-level integrity checks from
running. Once the higher-level roblem is fixed, a subsequent check may
discover these masked errors.
We have made great strides in reducing this phenomenon in the upcoming Yukon
release.
As a side note, you should always endeavor to use your backups to recover
from corruption issues rather than running repair. The
repair_allow_data_loss option is aptly and deliberately named as it may need
to delete some of your data to remove corruption and does nothing to prevent
further hardware caused corruption (how could it?). You should assume that
data was lost if you had to use this option and so your business or
application logic may no longer be correct (constraints etc).
Let me know if you have any further questions.
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"skrampe" <anonymous@.discussions.microsoft.com> wrote in message
news:49F9BF8C-7B8B-4B10-BCB1-C60295BA60C8@.microsoft.com...
> Hi newsgroup,
> in the last days we had a lot of trouble with our SQL-Server2000 database.
> We moved to complete new Hardware. Now it's solved (i hope so),
> but we want to be prepared for the future. Therefore i've a few questions:
> During the normal maintenance of the database some consistency errors were
detected.
> The REPAIR_ALLOW_DATA_LOSS option was suggested by dbcc checkdb.
> We had the luck, that not data loss was mentioned in the log file.
> So, can we be really sure that there was no data loss?
> If there's a data loss, how detailed is the information about the lost
data?
> After the first run with the REPAIR_ALLOW_DATA_LOSS option the next
checkdb
> detected still errors. (The first job wrote to the log file that all
errors were corrected)
> The check suggested the REPAIR_REBUILD option.
> For me the question is, why are there still errors after the first run
with the
> REPAIR_ALLOW_DATA_LOSS option? How can we avoid to run the job with a
REPAIR_XXXXX option more
> than one time.
>
> Thank you & Regards,
> Sven Krampe|||...
You should assume that
data was lost if you had to use this option and so your business or
application logic may no longer be correct (constraints etc).
...
Just to be sure: You mean, that it's possible that data is lost and we do no
t have any information about this in the Log-File of the repair job?
We check our database every night.
So, what would you suggest if we'll find consistency errors again (with the
suggestion repair_allow_data_loss)?
Go back to the last complete backup? (lose 24 h work of the users)!
Have a system downtime and try to repair the database?
Thank you very much for your help!
Regards,
Sven Krampe|||If you do regular transaction log backups, you can do a log backup after you
discover the corruption, and then
restore latest clean db backup and all subsequent log backups. Most probably
, the corruption will not re-occur
when you restore the log backups and you'll have no data loss. Below are my
general recommendations, btw:
http://www.karaszi.com/sqlserver/in..._suspect_db.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"skrampe" <anonymous@.discussions.microsoft.com> wrote in message
news:73BA7F69-450B-4CE9-8C7C-1D8DDB0F10AF@.microsoft.com...
> ...
> You should assume that
> data was lost if you had to use this option and so your business or
> application logic may no longer be correct (constraints etc).
> ...
> Just to be sure: You mean, that it's possible that data is lost and we do not have
any information about
this in the Log-File of the repair job?
> We check our database every night.
> So, what would you suggest if we'll find consistency errors again (with the sugges
tion
repair_allow_data_loss)?
> Go back to the last complete backup? (lose 24 h work of the users)!
> Have a system downtime and try to repair the database?
> Thank you very much for your help!
> Regards,
> Sven Krampe
>|||> Just to be sure: You mean, that it's possible that data is lost and we do
not have any information about this in the Log-File of the repair job?
I'm not sure what's captured in the log file but DBCC always provides
output.
> So, what would you suggest if we'll find consistency errors again (with
the suggestion repair_allow_data_loss)?
I would suggest you have a hardware issue if you see it again - in fact, I'd
suggest you do root-cause analysis to make sure you hardware is sound anyway
just because you saw this corruption.
> Go back to the last complete backup? (lose 24 h work of the users)!
> Have a system downtime and try to repair the database?
>
You should always be able to restore from your backups - repair should
always be a last resort. How often you take them is up to you - you must ask
yourself 'how much data can my business afford to lose?'. If the answer is
zero, then you need to beef up your backup strategy and put in place a
disaster recovery strategy (maybe use clustering, log shipping etc). I've
seen catastophic failures where the loss has only been 15 minutes or less
with a daily full backup and log backups every 15 mins.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"skrampe" <anonymous@.discussions.microsoft.com> wrote in message
news:73BA7F69-450B-4CE9-8C7C-1D8DDB0F10AF@.microsoft.com...
> ...
> You should assume that
> data was lost if you had to use this option and so your business or
> application logic may no longer be correct (constraints etc).
> ...
> Just to be sure: You mean, that it's possible that data is lost and we do
not have any information about this in the Log-File of the repair job?
> We check our database every night.
> So, what would you suggest if we'll find consistency errors again (with
the suggestion repair_allow_data_loss)?
> Go back to the last complete backup? (lose 24 h work of the users)!
> Have a system downtime and try to repair the database?
> Thank you very much for your help!
> Regards,
> Sven Krampe
>sql
Showing posts with label newsgroup. Show all posts
Showing posts with label newsgroup. Show all posts
Thursday, March 22, 2012
dbcc repair_allow_data_loss
Hi newsgroup,
in the last days we had a lot of trouble with our SQL-Server2000 database.
We moved to complete new Hardware. Now it's solved (i hope so),
but we want to be prepared for the future. Therefore i've a few questions:
During the normal maintenance of the database some consistency errors were detected.
The REPAIR_ALLOW_DATA_LOSS option was suggested by dbcc checkdb.
We had the luck, that not data loss was mentioned in the log file.
So, can we be really sure that there was no data loss?
If there's a data loss, how detailed is the information about the lost data?
After the first run with the REPAIR_ALLOW_DATA_LOSS option the next checkdb
detected still errors. (The first job wrote to the log file that all errors were corrected)
The check suggested the REPAIR_REBUILD option.
For me the question is, why are there still errors after the first run with the
REPAIR_ALLOW_DATA_LOSS option? How can we avoid to run the job with a REPAIR_XXXXX option more
than one time.
Thank you & Regards,
Sven Krampe
Hi Sven,
The reason it sometimes needs to be run twice or more is that some errors
can mask other errors. There are many different integrity checks performed
on the various structures in the database. If a problem is detected with a
higher level structure, this may prevent lower-level integrity checks from
running. Once the higher-level roblem is fixed, a subsequent check may
discover these masked errors.
We have made great strides in reducing this phenomenon in the upcoming Yukon
release.
As a side note, you should always endeavor to use your backups to recover
from corruption issues rather than running repair. The
repair_allow_data_loss option is aptly and deliberately named as it may need
to delete some of your data to remove corruption and does nothing to prevent
further hardware caused corruption (how could it?). You should assume that
data was lost if you had to use this option and so your business or
application logic may no longer be correct (constraints etc).
Let me know if you have any further questions.
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"skrampe" <anonymous@.discussions.microsoft.com> wrote in message
news:49F9BF8C-7B8B-4B10-BCB1-C60295BA60C8@.microsoft.com...
> Hi newsgroup,
> in the last days we had a lot of trouble with our SQL-Server2000 database.
> We moved to complete new Hardware. Now it's solved (i hope so),
> but we want to be prepared for the future. Therefore i've a few questions:
> During the normal maintenance of the database some consistency errors were
detected.
> The REPAIR_ALLOW_DATA_LOSS option was suggested by dbcc checkdb.
> We had the luck, that not data loss was mentioned in the log file.
> So, can we be really sure that there was no data loss?
> If there's a data loss, how detailed is the information about the lost
data?
> After the first run with the REPAIR_ALLOW_DATA_LOSS option the next
checkdb
> detected still errors. (The first job wrote to the log file that all
errors were corrected)
> The check suggested the REPAIR_REBUILD option.
> For me the question is, why are there still errors after the first run
with the
> REPAIR_ALLOW_DATA_LOSS option? How can we avoid to run the job with a
REPAIR_XXXXX option more
> than one time.
>
> Thank you & Regards,
> Sven Krampe
|||...
You should assume that
data was lost if you had to use this option and so your business or
application logic may no longer be correct (constraints etc).
....
Just to be sure: You mean, that it's possible that data is lost and we do not have any information about this in the Log-File of the repair job?
We check our database every night.
So, what would you suggest if we'll find consistency errors again (with the suggestion repair_allow_data_loss)?
Go back to the last complete backup? (lose 24 h work of the users)!
Have a system downtime and try to repair the database?
Thank you very much for your help!
Regards,
Sven Krampe
|||If you do regular transaction log backups, you can do a log backup after you discover the corruption, and then
restore latest clean db backup and all subsequent log backups. Most probably, the corruption will not re-occur
when you restore the log backups and you'll have no data loss. Below are my general recommendations, btw:
http://www.karaszi.com/sqlserver/inf...suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"skrampe" <anonymous@.discussions.microsoft.com> wrote in message
news:73BA7F69-450B-4CE9-8C7C-1D8DDB0F10AF@.microsoft.com...
> ...
> You should assume that
> data was lost if you had to use this option and so your business or
> application logic may no longer be correct (constraints etc).
> ...
> Just to be sure: You mean, that it's possible that data is lost and we do not have any information about
this in the Log-File of the repair job?
> We check our database every night.
> So, what would you suggest if we'll find consistency errors again (with the suggestion
repair_allow_data_loss)?
> Go back to the last complete backup? (lose 24 h work of the users)!
> Have a system downtime and try to repair the database?
> Thank you very much for your help!
> Regards,
> Sven Krampe
>
|||> Just to be sure: You mean, that it's possible that data is lost and we do
not have any information about this in the Log-File of the repair job?
I'm not sure what's captured in the log file but DBCC always provides
output.
> So, what would you suggest if we'll find consistency errors again (with
the suggestion repair_allow_data_loss)?
I would suggest you have a hardware issue if you see it again - in fact, I'd
suggest you do root-cause analysis to make sure you hardware is sound anyway
just because you saw this corruption.
> Go back to the last complete backup? (lose 24 h work of the users)!
> Have a system downtime and try to repair the database?
>
You should always be able to restore from your backups - repair should
always be a last resort. How often you take them is up to you - you must ask
yourself 'how much data can my business afford to lose?'. If the answer is
zero, then you need to beef up your backup strategy and put in place a
disaster recovery strategy (maybe use clustering, log shipping etc). I've
seen catastophic failures where the loss has only been 15 minutes or less
with a daily full backup and log backups every 15 mins.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"skrampe" <anonymous@.discussions.microsoft.com> wrote in message
news:73BA7F69-450B-4CE9-8C7C-1D8DDB0F10AF@.microsoft.com...
> ...
> You should assume that
> data was lost if you had to use this option and so your business or
> application logic may no longer be correct (constraints etc).
> ...
> Just to be sure: You mean, that it's possible that data is lost and we do
not have any information about this in the Log-File of the repair job?
> We check our database every night.
> So, what would you suggest if we'll find consistency errors again (with
the suggestion repair_allow_data_loss)?
> Go back to the last complete backup? (lose 24 h work of the users)!
> Have a system downtime and try to repair the database?
> Thank you very much for your help!
> Regards,
> Sven Krampe
>
in the last days we had a lot of trouble with our SQL-Server2000 database.
We moved to complete new Hardware. Now it's solved (i hope so),
but we want to be prepared for the future. Therefore i've a few questions:
During the normal maintenance of the database some consistency errors were detected.
The REPAIR_ALLOW_DATA_LOSS option was suggested by dbcc checkdb.
We had the luck, that not data loss was mentioned in the log file.
So, can we be really sure that there was no data loss?
If there's a data loss, how detailed is the information about the lost data?
After the first run with the REPAIR_ALLOW_DATA_LOSS option the next checkdb
detected still errors. (The first job wrote to the log file that all errors were corrected)
The check suggested the REPAIR_REBUILD option.
For me the question is, why are there still errors after the first run with the
REPAIR_ALLOW_DATA_LOSS option? How can we avoid to run the job with a REPAIR_XXXXX option more
than one time.
Thank you & Regards,
Sven Krampe
Hi Sven,
The reason it sometimes needs to be run twice or more is that some errors
can mask other errors. There are many different integrity checks performed
on the various structures in the database. If a problem is detected with a
higher level structure, this may prevent lower-level integrity checks from
running. Once the higher-level roblem is fixed, a subsequent check may
discover these masked errors.
We have made great strides in reducing this phenomenon in the upcoming Yukon
release.
As a side note, you should always endeavor to use your backups to recover
from corruption issues rather than running repair. The
repair_allow_data_loss option is aptly and deliberately named as it may need
to delete some of your data to remove corruption and does nothing to prevent
further hardware caused corruption (how could it?). You should assume that
data was lost if you had to use this option and so your business or
application logic may no longer be correct (constraints etc).
Let me know if you have any further questions.
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"skrampe" <anonymous@.discussions.microsoft.com> wrote in message
news:49F9BF8C-7B8B-4B10-BCB1-C60295BA60C8@.microsoft.com...
> Hi newsgroup,
> in the last days we had a lot of trouble with our SQL-Server2000 database.
> We moved to complete new Hardware. Now it's solved (i hope so),
> but we want to be prepared for the future. Therefore i've a few questions:
> During the normal maintenance of the database some consistency errors were
detected.
> The REPAIR_ALLOW_DATA_LOSS option was suggested by dbcc checkdb.
> We had the luck, that not data loss was mentioned in the log file.
> So, can we be really sure that there was no data loss?
> If there's a data loss, how detailed is the information about the lost
data?
> After the first run with the REPAIR_ALLOW_DATA_LOSS option the next
checkdb
> detected still errors. (The first job wrote to the log file that all
errors were corrected)
> The check suggested the REPAIR_REBUILD option.
> For me the question is, why are there still errors after the first run
with the
> REPAIR_ALLOW_DATA_LOSS option? How can we avoid to run the job with a
REPAIR_XXXXX option more
> than one time.
>
> Thank you & Regards,
> Sven Krampe
|||...
You should assume that
data was lost if you had to use this option and so your business or
application logic may no longer be correct (constraints etc).
....
Just to be sure: You mean, that it's possible that data is lost and we do not have any information about this in the Log-File of the repair job?
We check our database every night.
So, what would you suggest if we'll find consistency errors again (with the suggestion repair_allow_data_loss)?
Go back to the last complete backup? (lose 24 h work of the users)!
Have a system downtime and try to repair the database?
Thank you very much for your help!
Regards,
Sven Krampe
|||If you do regular transaction log backups, you can do a log backup after you discover the corruption, and then
restore latest clean db backup and all subsequent log backups. Most probably, the corruption will not re-occur
when you restore the log backups and you'll have no data loss. Below are my general recommendations, btw:
http://www.karaszi.com/sqlserver/inf...suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"skrampe" <anonymous@.discussions.microsoft.com> wrote in message
news:73BA7F69-450B-4CE9-8C7C-1D8DDB0F10AF@.microsoft.com...
> ...
> You should assume that
> data was lost if you had to use this option and so your business or
> application logic may no longer be correct (constraints etc).
> ...
> Just to be sure: You mean, that it's possible that data is lost and we do not have any information about
this in the Log-File of the repair job?
> We check our database every night.
> So, what would you suggest if we'll find consistency errors again (with the suggestion
repair_allow_data_loss)?
> Go back to the last complete backup? (lose 24 h work of the users)!
> Have a system downtime and try to repair the database?
> Thank you very much for your help!
> Regards,
> Sven Krampe
>
|||> Just to be sure: You mean, that it's possible that data is lost and we do
not have any information about this in the Log-File of the repair job?
I'm not sure what's captured in the log file but DBCC always provides
output.
> So, what would you suggest if we'll find consistency errors again (with
the suggestion repair_allow_data_loss)?
I would suggest you have a hardware issue if you see it again - in fact, I'd
suggest you do root-cause analysis to make sure you hardware is sound anyway
just because you saw this corruption.
> Go back to the last complete backup? (lose 24 h work of the users)!
> Have a system downtime and try to repair the database?
>
You should always be able to restore from your backups - repair should
always be a last resort. How often you take them is up to you - you must ask
yourself 'how much data can my business afford to lose?'. If the answer is
zero, then you need to beef up your backup strategy and put in place a
disaster recovery strategy (maybe use clustering, log shipping etc). I've
seen catastophic failures where the loss has only been 15 minutes or less
with a daily full backup and log backups every 15 mins.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"skrampe" <anonymous@.discussions.microsoft.com> wrote in message
news:73BA7F69-450B-4CE9-8C7C-1D8DDB0F10AF@.microsoft.com...
> ...
> You should assume that
> data was lost if you had to use this option and so your business or
> application logic may no longer be correct (constraints etc).
> ...
> Just to be sure: You mean, that it's possible that data is lost and we do
not have any information about this in the Log-File of the repair job?
> We check our database every night.
> So, what would you suggest if we'll find consistency errors again (with
the suggestion repair_allow_data_loss)?
> Go back to the last complete backup? (lose 24 h work of the users)!
> Have a system downtime and try to repair the database?
> Thank you very much for your help!
> Regards,
> Sven Krampe
>
dbcc repair_allow_data_loss
Hi newsgroup
in the last days we had a lot of trouble with our SQL-Server2000 database
We moved to complete new Hardware. Now it's solved (i hope so)
but we want to be prepared for the future. Therefore i've a few questions
During the normal maintenance of the database some consistency errors were detected
The REPAIR_ALLOW_DATA_LOSS option was suggested by dbcc checkdb
We had the luck, that not data loss was mentioned in the log file
So, can we be really sure that there was no data loss
If there's a data loss, how detailed is the information about the lost data
After the first run with the REPAIR_ALLOW_DATA_LOSS option the next checkdb
detected still errors. (The first job wrote to the log file that all errors were corrected
The check suggested the REPAIR_REBUILD option
For me the question is, why are there still errors after the first run with th
REPAIR_ALLOW_DATA_LOSS option? How can we avoid to run the job with a REPAIR_XXXXX option mor
than one time
Thank you & Regards
Sven KrampeHi Sven,
The reason it sometimes needs to be run twice or more is that some errors
can mask other errors. There are many different integrity checks performed
on the various structures in the database. If a problem is detected with a
higher level structure, this may prevent lower-level integrity checks from
running. Once the higher-level roblem is fixed, a subsequent check may
discover these masked errors.
We have made great strides in reducing this phenomenon in the upcoming Yukon
release.
As a side note, you should always endeavor to use your backups to recover
from corruption issues rather than running repair. The
repair_allow_data_loss option is aptly and deliberately named as it may need
to delete some of your data to remove corruption and does nothing to prevent
further hardware caused corruption (how could it?). You should assume that
data was lost if you had to use this option and so your business or
application logic may no longer be correct (constraints etc).
Let me know if you have any further questions.
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"skrampe" <anonymous@.discussions.microsoft.com> wrote in message
news:49F9BF8C-7B8B-4B10-BCB1-C60295BA60C8@.microsoft.com...
> Hi newsgroup,
> in the last days we had a lot of trouble with our SQL-Server2000 database.
> We moved to complete new Hardware. Now it's solved (i hope so),
> but we want to be prepared for the future. Therefore i've a few questions:
> During the normal maintenance of the database some consistency errors were
detected.
> The REPAIR_ALLOW_DATA_LOSS option was suggested by dbcc checkdb.
> We had the luck, that not data loss was mentioned in the log file.
> So, can we be really sure that there was no data loss?
> If there's a data loss, how detailed is the information about the lost
data?
> After the first run with the REPAIR_ALLOW_DATA_LOSS option the next
checkdb
> detected still errors. (The first job wrote to the log file that all
errors were corrected)
> The check suggested the REPAIR_REBUILD option.
> For me the question is, why are there still errors after the first run
with the
> REPAIR_ALLOW_DATA_LOSS option? How can we avoid to run the job with a
REPAIR_XXXXX option more
> than one time.
>
> Thank you & Regards,
> Sven Krampe|||...
You should assume tha
data was lost if you had to use this option and so your business o
application logic may no longer be correct (constraints etc)
...
Just to be sure: You mean, that it's possible that data is lost and we do not have any information about this in the Log-File of the repair job
We check our database every night
So, what would you suggest if we'll find consistency errors again (with the suggestion repair_allow_data_loss)
Go back to the last complete backup? (lose 24 h work of the users)
Have a system downtime and try to repair the database
Thank you very much for your help
Regards
Sven Kramp|||If you do regular transaction log backups, you can do a log backup after you discover the corruption, and then
restore latest clean db backup and all subsequent log backups. Most probably, the corruption will not re-occur
when you restore the log backups and you'll have no data loss. Below are my general recommendations, btw:
http://www.karaszi.com/sqlserver/info_corrupt_suspect_db.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"skrampe" <anonymous@.discussions.microsoft.com> wrote in message
news:73BA7F69-450B-4CE9-8C7C-1D8DDB0F10AF@.microsoft.com...
> ...
> You should assume that
> data was lost if you had to use this option and so your business or
> application logic may no longer be correct (constraints etc).
> ...
> Just to be sure: You mean, that it's possible that data is lost and we do not have any information about
this in the Log-File of the repair job?
> We check our database every night.
> So, what would you suggest if we'll find consistency errors again (with the suggestion
repair_allow_data_loss)?
> Go back to the last complete backup? (lose 24 h work of the users)!
> Have a system downtime and try to repair the database?
> Thank you very much for your help!
> Regards,
> Sven Krampe
>|||> Just to be sure: You mean, that it's possible that data is lost and we do
not have any information about this in the Log-File of the repair job?
I'm not sure what's captured in the log file but DBCC always provides
output.
> So, what would you suggest if we'll find consistency errors again (with
the suggestion repair_allow_data_loss)?
I would suggest you have a hardware issue if you see it again - in fact, I'd
suggest you do root-cause analysis to make sure you hardware is sound anyway
just because you saw this corruption.
> Go back to the last complete backup? (lose 24 h work of the users)!
> Have a system downtime and try to repair the database?
>
You should always be able to restore from your backups - repair should
always be a last resort. How often you take them is up to you - you must ask
yourself 'how much data can my business afford to lose?'. If the answer is
zero, then you need to beef up your backup strategy and put in place a
disaster recovery strategy (maybe use clustering, log shipping etc). I've
seen catastophic failures where the loss has only been 15 minutes or less
with a daily full backup and log backups every 15 mins.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"skrampe" <anonymous@.discussions.microsoft.com> wrote in message
news:73BA7F69-450B-4CE9-8C7C-1D8DDB0F10AF@.microsoft.com...
> ...
> You should assume that
> data was lost if you had to use this option and so your business or
> application logic may no longer be correct (constraints etc).
> ...
> Just to be sure: You mean, that it's possible that data is lost and we do
not have any information about this in the Log-File of the repair job?
> We check our database every night.
> So, what would you suggest if we'll find consistency errors again (with
the suggestion repair_allow_data_loss)?
> Go back to the last complete backup? (lose 24 h work of the users)!
> Have a system downtime and try to repair the database?
> Thank you very much for your help!
> Regards,
> Sven Krampe
>
in the last days we had a lot of trouble with our SQL-Server2000 database
We moved to complete new Hardware. Now it's solved (i hope so)
but we want to be prepared for the future. Therefore i've a few questions
During the normal maintenance of the database some consistency errors were detected
The REPAIR_ALLOW_DATA_LOSS option was suggested by dbcc checkdb
We had the luck, that not data loss was mentioned in the log file
So, can we be really sure that there was no data loss
If there's a data loss, how detailed is the information about the lost data
After the first run with the REPAIR_ALLOW_DATA_LOSS option the next checkdb
detected still errors. (The first job wrote to the log file that all errors were corrected
The check suggested the REPAIR_REBUILD option
For me the question is, why are there still errors after the first run with th
REPAIR_ALLOW_DATA_LOSS option? How can we avoid to run the job with a REPAIR_XXXXX option mor
than one time
Thank you & Regards
Sven KrampeHi Sven,
The reason it sometimes needs to be run twice or more is that some errors
can mask other errors. There are many different integrity checks performed
on the various structures in the database. If a problem is detected with a
higher level structure, this may prevent lower-level integrity checks from
running. Once the higher-level roblem is fixed, a subsequent check may
discover these masked errors.
We have made great strides in reducing this phenomenon in the upcoming Yukon
release.
As a side note, you should always endeavor to use your backups to recover
from corruption issues rather than running repair. The
repair_allow_data_loss option is aptly and deliberately named as it may need
to delete some of your data to remove corruption and does nothing to prevent
further hardware caused corruption (how could it?). You should assume that
data was lost if you had to use this option and so your business or
application logic may no longer be correct (constraints etc).
Let me know if you have any further questions.
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"skrampe" <anonymous@.discussions.microsoft.com> wrote in message
news:49F9BF8C-7B8B-4B10-BCB1-C60295BA60C8@.microsoft.com...
> Hi newsgroup,
> in the last days we had a lot of trouble with our SQL-Server2000 database.
> We moved to complete new Hardware. Now it's solved (i hope so),
> but we want to be prepared for the future. Therefore i've a few questions:
> During the normal maintenance of the database some consistency errors were
detected.
> The REPAIR_ALLOW_DATA_LOSS option was suggested by dbcc checkdb.
> We had the luck, that not data loss was mentioned in the log file.
> So, can we be really sure that there was no data loss?
> If there's a data loss, how detailed is the information about the lost
data?
> After the first run with the REPAIR_ALLOW_DATA_LOSS option the next
checkdb
> detected still errors. (The first job wrote to the log file that all
errors were corrected)
> The check suggested the REPAIR_REBUILD option.
> For me the question is, why are there still errors after the first run
with the
> REPAIR_ALLOW_DATA_LOSS option? How can we avoid to run the job with a
REPAIR_XXXXX option more
> than one time.
>
> Thank you & Regards,
> Sven Krampe|||...
You should assume tha
data was lost if you had to use this option and so your business o
application logic may no longer be correct (constraints etc)
...
Just to be sure: You mean, that it's possible that data is lost and we do not have any information about this in the Log-File of the repair job
We check our database every night
So, what would you suggest if we'll find consistency errors again (with the suggestion repair_allow_data_loss)
Go back to the last complete backup? (lose 24 h work of the users)
Have a system downtime and try to repair the database
Thank you very much for your help
Regards
Sven Kramp|||If you do regular transaction log backups, you can do a log backup after you discover the corruption, and then
restore latest clean db backup and all subsequent log backups. Most probably, the corruption will not re-occur
when you restore the log backups and you'll have no data loss. Below are my general recommendations, btw:
http://www.karaszi.com/sqlserver/info_corrupt_suspect_db.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"skrampe" <anonymous@.discussions.microsoft.com> wrote in message
news:73BA7F69-450B-4CE9-8C7C-1D8DDB0F10AF@.microsoft.com...
> ...
> You should assume that
> data was lost if you had to use this option and so your business or
> application logic may no longer be correct (constraints etc).
> ...
> Just to be sure: You mean, that it's possible that data is lost and we do not have any information about
this in the Log-File of the repair job?
> We check our database every night.
> So, what would you suggest if we'll find consistency errors again (with the suggestion
repair_allow_data_loss)?
> Go back to the last complete backup? (lose 24 h work of the users)!
> Have a system downtime and try to repair the database?
> Thank you very much for your help!
> Regards,
> Sven Krampe
>|||> Just to be sure: You mean, that it's possible that data is lost and we do
not have any information about this in the Log-File of the repair job?
I'm not sure what's captured in the log file but DBCC always provides
output.
> So, what would you suggest if we'll find consistency errors again (with
the suggestion repair_allow_data_loss)?
I would suggest you have a hardware issue if you see it again - in fact, I'd
suggest you do root-cause analysis to make sure you hardware is sound anyway
just because you saw this corruption.
> Go back to the last complete backup? (lose 24 h work of the users)!
> Have a system downtime and try to repair the database?
>
You should always be able to restore from your backups - repair should
always be a last resort. How often you take them is up to you - you must ask
yourself 'how much data can my business afford to lose?'. If the answer is
zero, then you need to beef up your backup strategy and put in place a
disaster recovery strategy (maybe use clustering, log shipping etc). I've
seen catastophic failures where the loss has only been 15 minutes or less
with a daily full backup and log backups every 15 mins.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"skrampe" <anonymous@.discussions.microsoft.com> wrote in message
news:73BA7F69-450B-4CE9-8C7C-1D8DDB0F10AF@.microsoft.com...
> ...
> You should assume that
> data was lost if you had to use this option and so your business or
> application logic may no longer be correct (constraints etc).
> ...
> Just to be sure: You mean, that it's possible that data is lost and we do
not have any information about this in the Log-File of the repair job?
> We check our database every night.
> So, what would you suggest if we'll find consistency errors again (with
the suggestion repair_allow_data_loss)?
> Go back to the last complete backup? (lose 24 h work of the users)!
> Have a system downtime and try to repair the database?
> Thank you very much for your help!
> Regards,
> Sven Krampe
>
Sunday, February 19, 2012
DBCC CHECKDB REPAIR error 5244
Hi Luc,
Could you please let me know what the newsgroup in which you put the same
post was? Is it a managed newsgroup? If your original post was not posted
at a Managed newsgroup, it could not be monitored by Microsoft Newsgroup
Support Team.
For your issue, I understand that you were trying to recover your database
from the latest database backup. When you ran DBCC CHECKDB with REPAIR_FAST
option, you encountered the error.
If I have misunderstood, please let me know.
After you restore the latest version of your database, I recommend that you
first trying detaching your database and then attaching the database with
single file, such as:
exec sp_detach_db 'database name'
Go
exec sp_attach_single_file_db @.dbname='database name',@.physname=N'your mdf
file path'
Go
After that, run DBCC CHECKDB with REPAIR_FAST to see if it can succeed.
Please note that REPAIR_FAST actually did not perform any repair actions.
If you want to repair the reported errors from DBCC CHECKDB, please specify
REPAIR_ALLOW_DATA_LOSS or REPAIR_REBUILD (minor repair).
If the same error occurs, I recommend that you try running DBCC CHECKTABLE
on each of your table to see if which tables could not be repaired. You may
check if you can replace the table by creating a new one. Also as a more
convenient way, you may consider exporting all of your data tables to
another database via Import/Export Data wizard from SQL Server Management
Studio and then run DBCC CHECKDB to see if there are any errors.
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =========
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@.microsoft.com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======
Hi Luc,
Could you please post the DBCC CHECKDB error here for further research? Or
you may mail it to me (changliw_at_microsoft_dot_com).
Also I would like to know if you have tried my suggestion of
importing/exporting your data tables to another database? Does it help?
Note that regarding data corruption, it is always not an easy thing and
sometimes it is impossible to recover for some serious corruptions. We will
try our best to give you some suggestions, however if it does not help at
last, you may consider contacting third party data recovery company to
repair your data.
If you have any other questions or concerns, please feel free to let me
know. Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =======
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@.microsoft.com.
================================================== =======
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======
|||Hi Luc,
Thanks for your detailed response.
I notice that almost all the errors are regarding indexes on the two table
'EPISLog' and 'PhotoPresence'. Also the errors are for consistency not
allocation, so I recommend that you run "DBCC CHECKDB('GT',REPAIR_REBUILD)"
to repair indexes to see if it helps. Please set your database to single
user mode with the following statement before you run it:
alter database <database name>
set single_user
Go
Please feel free to let me know if you have any other questions or oncerns.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =======
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@.microsoft.com.
================================================== =======
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======
|||Hi Luc,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =======
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@.microsoft.com.
================================================== =======
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======
|||Hi Luc,
Appreciate your letting me know the situation of this issue.
If you have any other questions or concerns, please feel free to let me
know.
Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =======
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@.microsoft.com.
================================================== =======
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======
Could you please let me know what the newsgroup in which you put the same
post was? Is it a managed newsgroup? If your original post was not posted
at a Managed newsgroup, it could not be monitored by Microsoft Newsgroup
Support Team.
For your issue, I understand that you were trying to recover your database
from the latest database backup. When you ran DBCC CHECKDB with REPAIR_FAST
option, you encountered the error.
If I have misunderstood, please let me know.
After you restore the latest version of your database, I recommend that you
first trying detaching your database and then attaching the database with
single file, such as:
exec sp_detach_db 'database name'
Go
exec sp_attach_single_file_db @.dbname='database name',@.physname=N'your mdf
file path'
Go
After that, run DBCC CHECKDB with REPAIR_FAST to see if it can succeed.
Please note that REPAIR_FAST actually did not perform any repair actions.
If you want to repair the reported errors from DBCC CHECKDB, please specify
REPAIR_ALLOW_DATA_LOSS or REPAIR_REBUILD (minor repair).
If the same error occurs, I recommend that you try running DBCC CHECKTABLE
on each of your table to see if which tables could not be repaired. You may
check if you can replace the table by creating a new one. Also as a more
convenient way, you may consider exporting all of your data tables to
another database via Import/Export Data wizard from SQL Server Management
Studio and then run DBCC CHECKDB to see if there are any errors.
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =========
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@.microsoft.com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======
Hi Luc,
Could you please post the DBCC CHECKDB error here for further research? Or
you may mail it to me (changliw_at_microsoft_dot_com).
Also I would like to know if you have tried my suggestion of
importing/exporting your data tables to another database? Does it help?
Note that regarding data corruption, it is always not an easy thing and
sometimes it is impossible to recover for some serious corruptions. We will
try our best to give you some suggestions, however if it does not help at
last, you may consider contacting third party data recovery company to
repair your data.
If you have any other questions or concerns, please feel free to let me
know. Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =======
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@.microsoft.com.
================================================== =======
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======
|||Hi Luc,
Thanks for your detailed response.
I notice that almost all the errors are regarding indexes on the two table
'EPISLog' and 'PhotoPresence'. Also the errors are for consistency not
allocation, so I recommend that you run "DBCC CHECKDB('GT',REPAIR_REBUILD)"
to repair indexes to see if it helps. Please set your database to single
user mode with the following statement before you run it:
alter database <database name>
set single_user
Go
Please feel free to let me know if you have any other questions or oncerns.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =======
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@.microsoft.com.
================================================== =======
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======
|||Hi Luc,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =======
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@.microsoft.com.
================================================== =======
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======
|||Hi Luc,
Appreciate your letting me know the situation of this issue.
If you have any other questions or concerns, please feel free to let me
know.
Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =======
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@.microsoft.com.
================================================== =======
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======
Subscribe to:
Posts (Atom)