dbcc question

Is there a way to simulate maintenance plan option for
integrity checking called: attempt to repair minor
problems using some dbcc option preferably in mutli user
mode for MSSQL2000.Milan
The command you want is DBCC CHECKDB. It allows three
levels of repair. See Books on line for format and options.
Regards
John|||However, please be aware of two things:
1) you cannot run repair in multi-user mode - the database must be in
single-user mode
2) you should not automatically repair errors. You should always work out
why an error happened (so you can prevent it in future) and whether the
repair may lose some of your data. A preferable strategem is always to
restore from your most recent backups.
Regards,
Paul.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"John Bandettini" <johnbandettini@.yahoo.co.uk> wrote in message
news:079201c3502d$575fc370$a601280a@.phx.gbl...
> Milan
> The command you want is DBCC CHECKDB. It allows three
> levels of repair. See Books on line for format and options.
> Regards
> John

DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS

Hi
I ran a DBCC ceckdb with repair data loss
Is there anyway to know what data has been repaired ?
moreover if some data is lost how to find what is lost?
ThanksNot really. That is why it is so important to have proper backups that you
can restore from so as to minimize data loss.
Andrew J. Kelly
SQL Server MVP
"sa" <anonymous@.discussions.microsoft.com> wrote in message
news:DA2363E3-52A4-43BE-B0C4-9777F88D1F4D@.microsoft.com...
> Hi
> I ran a DBCC ceckdb with repair data loss
> Is there anyway to know what data has been repaired ?
> moreover if some data is lost how to find what is lost?
> Thanks
>|||Thanks Andrew|||Hi -
There is no easy way to find this out. If REPAIR_ALLOW_DATA_LOSS is
specified, many errors are "fixed" simply by de-allocating the 8k SQL page.
Thanks
- Surajit
"sa" <anonymous@.discussions.microsoft.com> wrote in message
news:DA2363E3-52A4-43BE-B0C4-9777F88D1F4D@.microsoft.com...
> Hi
> I ran a DBCC ceckdb with repair data loss
> Is there anyway to know what data has been repaired ?
> moreover if some data is lost how to find what is lost?
> Thanks
>

DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS

Hi
I ran a DBCC ceckdb with repair data loss
Is there anyway to know what data has been repaired ?
moreover if some data is lost how to find what is lost?
Thanks
Not really. That is why it is so important to have proper backups that you
can restore from so as to minimize data loss.
Andrew J. Kelly
SQL Server MVP
"sa" <anonymous@.discussions.microsoft.com> wrote in message
news:DA2363E3-52A4-43BE-B0C4-9777F88D1F4D@.microsoft.com...
> Hi
> I ran a DBCC ceckdb with repair data loss
> Is there anyway to know what data has been repaired ?
> moreover if some data is lost how to find what is lost?
> Thanks
>
|||Thanks Andrew
|||Hi -
There is no easy way to find this out. If REPAIR_ALLOW_DATA_LOSS is
specified, many errors are "fixed" simply by de-allocating the 8k SQL page.
Thanks
- Surajit
"sa" <anonymous@.discussions.microsoft.com> wrote in message
news:DA2363E3-52A4-43BE-B0C4-9777F88D1F4D@.microsoft.com...
> Hi
> I ran a DBCC ceckdb with repair data loss
> Is there anyway to know what data has been repaired ?
> moreover if some data is lost how to find what is lost?
> Thanks
>

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

DBCC CHECKDB REPAIR error 5244

Dumb: I posted this in another group a week ago, then wondered why I got no
reply. Turns out that group doesn't exist anymore--at least on msnews,
other newsservers stil carry it, but the activity level is near-zero :(
--
I'm trying to recover as much as possible from a database that has become
the victim of data corruption on disk.
The database uses simple recovery model. There are daily full backups,
archived to 7 days back, but the problem was detected too late, so the
errors also exist in all available backups.
I restored the latest backup on another machine, set it to single user, and
tried to run DBCC CHECKDB with the REPAIR_FAST option.
This results in an error message:
Msg 5244, Level 16, State 1, Server WS-LUC-2, Line 1
Repair statement not processed. One or more files in the database are
read-only and must be made writeable in order to run repair.
None of the database files is read-only though, there must be another
reason why writing fails.
Has anyone ever encountered this?
PS: the database was created on SQL Server 2005 Express Edition, I've tried
the repair on 2005 EE as well as on a full 2005 server. The result remains
the same.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.
=========================================================|||On Tue, 01 Apr 2008 03:56:37 GMT, changliw@.online.microsoft.com (Charles
Wang[MSFT]) wrote:
> 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.
My fault: it's not a managed newsgroup (microsoft.public.sqlserver.misc).
It must still have existed in the newsgroup list of my newsreader (Forté
Agent) from years ago.
Agent must have fallen back to another server when the group was deleted
from msnews.microsoft.com (I'm using two other servers in addition to
msnews to get access to non-MS newsgroups, but I also use them for
microsoft.public newsgroups because they keep old messages much longer,
which sometimes comes in handy if a question has been asked and answered
long ago).
> 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.
I get the same error.
To be sure I deleted the database, restored it again from the same backup,
detached it, and then ran these queries:
exec sp_attach_single_file_db @.dbname='PLRest',
physname=N'F:\Temp2\PLRest.mdf'
alter database PLRest set SINGLE_USER
dbcc checkdb('PLRest',REPAIR_FAST)
The result is a single message:
Msg 5244, Level 16, State 1, Line 3
Repair statement not processed. One or more files in the database are
read-only and must be made writeable in order to run repair.
The "set SINGLE_USER" line is there only because DBCC CHECKDB insists on
it.
Being only the developer who wrote the application, I haven't seen the
machine where the database comes from, but the engineer who was sent there
to repair it on site and who brought back the copy of the backup file,
reports the same error from dbcc checkdb there.
Changing REPAIR_FAST to REPAIR_REBUILD doesn't help either: same error.
Only without a REPAIR option, dbcc checkdb succeeds and reports about a
dozen errors.|||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.
=========================================================|||On Wed, 02 Apr 2008 14:52:52 GMT, changliw@.online.microsoft.com (Charles
Wang[MSFT]) wrote:
> 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).
See below.
> Also I would like to know if you have tried my suggestion of
> importing/exporting your data tables to another database? Does it help?
I had already had tried that, but exporting the full tables fails.
OTOH, I noticed that I can recover some (probably most) data from the
damaged tables by selecting part of the records through an index, trying to
guess what range is damaged and skipping over it.
Both damaged tables contain event logging records and are indexed on time,
so that isn't hard to do.
For the moment I'm waiting for someone to send me a copy of the actual .mdf
file itself instead of a backup (as I had originally asked for, but someone
decided it would be easier to send me a backup).
The application is still running, and as far as I can see from here, the
actual mdf file is in better condition than the backups (backup might have
stopped prematurely when it ran into the damaged section?)
Adding new records to the damaged tables still seems to work in the
original DB, but when I tried the same in the restored backup, it failed.
Selecting data fails in both cases (depending on the selection criteria).
> 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.
The data are not that critical (in fact some of them are, there's a legal
requirement to archive them for 10 years, but backups on paper exist).
> If you have any other questions or concerns, please feel free to let me
> know. Have a nice day!
And to you too.
Result of checkdb follows.
It says "repair_allow_data_loss is the minimum repair level", but I tried
that as well and it gives the same error.
DBCC results for 'PlayersRJMM'.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
Service Broker Msg 9667, State 1: Services analyzed: 3.
Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0
(type Unknown), page ID (1:13734) contains an incorrect page ID in its page
header. The PageId in the page header = (0:0).
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0
(type Unknown), page ID (1:13735) contains an incorrect page ID in its page
header. The PageId in the page header = (0:0).
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0
(type Unknown), page ID (1:13737) contains an incorrect page ID in its page
header. The PageId in the page header = (0:0).
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0
(type Unknown), page ID (1:13738) contains an incorrect page ID in its page
header. The PageId in the page header = (0:0).
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0
(type Unknown), page ID (1:13741) contains an incorrect page ID in its page
header. The PageId in the page header = (0:0).
Msg 8939, Level 16, State 5, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0
(type Unknown), page (1:13744). Test (m_headerVersion == HEADER_7_0)
failed. Values are 0 and 1.
Msg 8939, Level 16, State 6, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0
(type Unknown), page (1:13744). Test ((m_type >= DATA_PAGE && m_type <=UNDOFILE_HEADER_PAGE) || (m_type == UNKNOWN_PAGE && level == BASIC_HEADER))
failed. Values are 0 and 0.
Msg 8939, Level 16, State 7, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0
(type Unknown), page (1:13744). Test (m_freeData >= PAGEHEADSIZE &&
m_freeData <= (UINT)PAGESIZE - m_slotCnt * sizeof (Slot)) failed. Values
are 0 and 8192.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0
(type Unknown), page ID (1:13746) contains an incorrect page ID in its page
header. The PageId in the page header = (0:0).
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0
(type Unknown), page ID (1:13747) contains an incorrect page ID in its page
header. The PageId in the page header = (0:0).
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0
(type Unknown), page ID (1:13748) contains an incorrect page ID in its page
header. The PageId in the page header = (0:0).
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0
(type Unknown), page ID (1:13749) contains an incorrect page ID in its page
header. The PageId in the page header = (0:0).
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0
(type Unknown), page ID (1:13750) contains an incorrect page ID in its page
header. The PageId in the page header = (0:0).
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0
(type Unknown), page ID (1:13751) contains an incorrect page ID in its page
header. The PageId in the page header = (0:0).
Msg 8939, Level 16, State 5, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0
(type Unknown), page (1:13752). Test (m_headerVersion == HEADER_7_0)
failed. Values are 0 and 1.
Msg 8939, Level 16, State 6, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0
(type Unknown), page (1:13752). Test ((m_type >= DATA_PAGE && m_type <=UNDOFILE_HEADER_PAGE) || (m_type == UNKNOWN_PAGE && level == BASIC_HEADER))
failed. Values are 0 and 0.
Msg 8939, Level 16, State 7, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0
(type Unknown), page (1:13752). Test (m_freeData >= PAGEHEADSIZE &&
m_freeData <= (UINT)PAGESIZE - m_slotCnt * sizeof (Slot)) failed. Values
are 0 and 8192.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0
(type Unknown), page ID (1:13753) contains an incorrect page ID in its page
header. The PageId in the page header = (0:0).
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0
(type Unknown), page ID (1:13754) contains an incorrect page ID in its page
header. The PageId in the page header = (0:0).
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0
(type Unknown), page ID (1:13755) contains an incorrect page ID in its page
header. The PageId in the page header = (0:0).
CHECKDB found 0 allocation errors and 20 consistency errors not associated
with any single object.
DBCC results for 'sys.sysrowsetcolumns'.
There are 632 rows in 6 pages for object "sys.sysrowsetcolumns".
DBCC results for 'sys.sysrowsets'.
There are 104 rows in 1 pages for object "sys.sysrowsets".
DBCC results for 'sysallocunits'.
There are 120 rows in 2 pages for object "sysallocunits".
DBCC results for 'sys.sysfiles1'.
There are 2 rows in 1 pages for object "sys.sysfiles1".
DBCC results for 'sys.syshobtcolumns'.
There are 632 rows in 9 pages for object "sys.syshobtcolumns".
DBCC results for 'sys.syshobts'.
There are 104 rows in 1 pages for object "sys.syshobts".
DBCC results for 'sys.sysftinds'.
There are 0 rows in 0 pages for object "sys.sysftinds".
DBCC results for 'sys.sysserefs'.
There are 120 rows in 1 pages for object "sys.sysserefs".
DBCC results for 'sys.sysowners'.
There are 16 rows in 1 pages for object "sys.sysowners".
DBCC results for 'sys.sysprivs'.
There are 122 rows in 1 pages for object "sys.sysprivs".
DBCC results for 'sys.sysschobjs'.
There are 96 rows in 2 pages for object "sys.sysschobjs".
DBCC results for 'sys.syscolpars'.
There are 479 rows in 8 pages for object "sys.syscolpars".
DBCC results for 'sys.sysnsobjs'.
There are 1 rows in 1 pages for object "sys.sysnsobjs".
DBCC results for 'sys.syscerts'.
There are 0 rows in 0 pages for object "sys.syscerts".
DBCC results for 'sys.sysxprops'.
There are 0 rows in 0 pages for object "sys.sysxprops".
DBCC results for 'sys.sysscalartypes'.
There are 27 rows in 1 pages for object "sys.sysscalartypes".
DBCC results for 'sys.systypedsubobjs'.
There are 0 rows in 0 pages for object "sys.systypedsubobjs".
DBCC results for 'sys.sysidxstats'.
There are 179 rows in 4 pages for object "sys.sysidxstats".
DBCC results for 'sys.sysiscols'.
There are 308 rows in 3 pages for object "sys.sysiscols".
DBCC results for 'sys.sysbinobjs'.
There are 23 rows in 1 pages for object "sys.sysbinobjs".
DBCC results for 'sys.sysobjvalues'.
There are 207 rows in 47 pages for object "sys.sysobjvalues".
DBCC results for 'sys.sysclsobjs'.
There are 15 rows in 1 pages for object "sys.sysclsobjs".
DBCC results for 'sys.sysrowsetrefs'.
There are 0 rows in 0 pages for object "sys.sysrowsetrefs".
DBCC results for 'sys.sysremsvcbinds'.
There are 0 rows in 0 pages for object "sys.sysremsvcbinds".
DBCC results for 'sys.sysxmitqueue'.
There are 0 rows in 0 pages for object "sys.sysxmitqueue".
DBCC results for 'sys.sysrts'.
There are 1 rows in 1 pages for object "sys.sysrts".
DBCC results for 'sys.sysconvgroup'.
There are 0 rows in 0 pages for object "sys.sysconvgroup".
DBCC results for 'sys.sysdesend'.
There are 0 rows in 0 pages for object "sys.sysdesend".
DBCC results for 'sys.sysdercv'.
There are 0 rows in 0 pages for object "sys.sysdercv".
DBCC results for 'sys.syssingleobjrefs'.
There are 134 rows in 1 pages for object "sys.syssingleobjrefs".
DBCC results for 'sys.sysmultiobjrefs'.
There are 110 rows in 1 pages for object "sys.sysmultiobjrefs".
DBCC results for 'sys.sysdbfiles'.
There are 2 rows in 1 pages for object "sys.sysdbfiles".
DBCC results for 'sys.sysguidrefs'.
There are 0 rows in 0 pages for object "sys.sysguidrefs".
DBCC results for 'sys.sysqnames'.
There are 91 rows in 1 pages for object "sys.sysqnames".
DBCC results for 'sys.sysxmlcomponent'.
There are 93 rows in 1 pages for object "sys.sysxmlcomponent".
DBCC results for 'sys.sysxmlfacet'.
There are 97 rows in 1 pages for object "sys.sysxmlfacet".
DBCC results for 'sys.sysxmlplacement'.
There are 17 rows in 1 pages for object "sys.sysxmlplacement".
DBCC results for 'sys.sysobjkeycrypts'.
There are 0 rows in 0 pages for object "sys.sysobjkeycrypts".
DBCC results for 'sys.sysasymkeys'.
There are 0 rows in 0 pages for object "sys.sysasymkeys".
DBCC results for 'sys.syssqlguides'.
There are 0 rows in 0 pages for object "sys.syssqlguides".
DBCC results for 'sys.sysbinsubobjs'.
There are 0 rows in 0 pages for object "sys.sysbinsubobjs".
DBCC results for 'ZipCodes'.
There are 42134 rows in 208 pages for object "ZipCodes".
DBCC results for 'Photos'.
There are 860 rows in 5 pages for object "Photos".
DBCC results for 'IDCards'.
There are 817 rows in 7 pages for object "IDCards".
DBCC results for 'A2SCards'.
There are 501 rows in 4 pages for object "A2SCards".
DBCC results for 'A2SHistory'.
There are 18 rows in 1 pages for object "A2SHistory".
DBCC results for 'Presence'.
There are 14692 rows in 1260 pages for object "Presence".
DBCC results for 'PhotoPresence'.
Msg 8928, Level 16, State 1, Line 1
Object ID 277576027, index ID 1, partition ID 72057594039435264, alloc unit
ID 71794310283722752 (type LOB data): Page (1:13734) could not be
processed. See other errors for details.
Msg 8928, Level 16, State 1, Line 1
Object ID 277576027, index ID 1, partition ID 72057594039435264, alloc unit
ID 71794310283722752 (type LOB data): Page (1:13735) could not be
processed. See other errors for details.
Msg 8928, Level 16, State 1, Line 1
Object ID 277576027, index ID 1, partition ID 72057594039435264, alloc unit
ID 71794310283722752 (type LOB data): Page (1:13744) could not be
processed. See other errors for details.
Msg 8928, Level 16, State 1, Line 1
Object ID 277576027, index ID 1, partition ID 72057594039435264, alloc unit
ID 71794310283722752 (type LOB data): Page (1:13746) could not be
processed. See other errors for details.
Msg 8928, Level 16, State 1, Line 1
Object ID 277576027, index ID 1, partition ID 72057594039435264, alloc unit
ID 71794310283722752 (type LOB data): Page (1:13747) could not be
processed. See other errors for details.
Msg 8928, Level 16, State 1, Line 1
Object ID 277576027, index ID 1, partition ID 72057594039435264, alloc unit
ID 71794310283722752 (type LOB data): Page (1:13748) could not be
processed. See other errors for details.
Msg 8928, Level 16, State 1, Line 1
Object ID 277576027, index ID 1, partition ID 72057594039435264, alloc unit
ID 71794310283722752 (type LOB data): Page (1:13749) could not be
processed. See other errors for details.
Msg 8928, Level 16, State 1, Line 1
Object ID 277576027, index ID 1, partition ID 72057594039435264, alloc unit
ID 71794310283722752 (type LOB data): Page (1:13750) could not be
processed. See other errors for details.
Msg 8928, Level 16, State 1, Line 1
Object ID 277576027, index ID 1, partition ID 72057594039435264, alloc unit
ID 71794310283722752 (type LOB data): Page (1:13751) could not be
processed. See other errors for details.
Msg 8928, Level 16, State 1, Line 1
Object ID 277576027, index ID 1, partition ID 72057594039435264, alloc unit
ID 71794310283722752 (type LOB data): Page (1:13752) could not be
processed. See other errors for details.
Msg 8928, Level 16, State 1, Line 1
Object ID 277576027, index ID 1, partition ID 72057594039435264, alloc unit
ID 71794310283722752 (type LOB data): Page (1:13753) could not be
processed. See other errors for details.
Msg 8928, Level 16, State 1, Line 1
Object ID 277576027, index ID 1, partition ID 72057594039435264, alloc unit
ID 71794310283722752 (type LOB data): Page (1:13754) could not be
processed. See other errors for details.
Msg 8928, Level 16, State 1, Line 1
Object ID 277576027, index ID 1, partition ID 72057594039435264, alloc unit
ID 71794310283722752 (type LOB data): Page (1:13755) could not be
processed. See other errors for details.
There are 14685 rows in 105 pages for object "PhotoPresence".
CHECKDB found 0 allocation errors and 13 consistency errors in table
'PhotoPresence' (object ID 277576027).
DBCC results for 'EPISLog'.
Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 373576369, index ID 1, partition ID
72057594039238656, alloc unit ID 71800601762136064 (type LOB data). The
off-row data node at page (1:13577), slot 13, text ID 31002918912 does not
match its reference from page (1:56), slot 3.
Msg 8974, Level 16, State 1, Line 1
Table error: Object ID 373576369, index ID 1, partition ID
72057594039238656, alloc unit ID 71800601762136064 (type LOB data). The
off-row data node at page (1:13577), slot 13, text ID 31002918912 is
pointed to by page (1:56), slot 3 and by page (1:11416), slot 37.
Msg 8961, Level 16, State 3, Line 1
Table error: Object ID 373576369, index ID 1, partition ID
72057594039238656, alloc unit ID 71800601762136064 (type LOB data). The
off-row data node at page (1:13577), slot 14, text ID 31002984448 does not
match its reference from page (1:56), slot 3.
Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 373576369, index ID 1, partition ID
72057594039238656, alloc unit ID 71800601762136064 (type LOB data). The
off-row data node at page (1:13577), slot 14, text ID 31002984448 does not
match its reference from page (1:56), slot 3.
Msg 8974, Level 16, State 1, Line 1
Table error: Object ID 373576369, index ID 1, partition ID
72057594039238656, alloc unit ID 71800601762136064 (type LOB data). The
off-row data node at page (1:13577), slot 14, text ID 31002984448 is
pointed to by page (1:13577), slot 15 and by page (1:56), slot 3.
Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 373576369, index ID 1, partition ID
72057594039238656, alloc unit ID 71800601762136064 (type LOB data). The
off-row data node at page (1:13624), slot 10, text ID 26153123840 does not
match its reference from page (1:56), slot 1.
Msg 8974, Level 16, State 1, Line 1
Table error: Object ID 373576369, index ID 1, partition ID
72057594039238656, alloc unit ID 71800601762136064 (type LOB data). The
off-row data node at page (1:13624), slot 10, text ID 26153123840 is
pointed to by page (1:56), slot 1 and by page (1:56), slot 53.
Msg 8961, Level 16, State 3, Line 1
Table error: Object ID 373576369, index ID 1, partition ID
72057594039238656, alloc unit ID 71800601762136064 (type LOB data). The
off-row data node at page (1:13624), slot 11, text ID 26280329216 does not
match its reference from page (1:56), slot 4.
Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 373576369, index ID 1, partition ID
72057594039238656, alloc unit ID 71800601762136064 (type LOB data). The
off-row data node at page (1:13624), slot 11, text ID 26280329216 does not
match its reference from page (1:56), slot 4.
Msg 8974, Level 16, State 1, Line 1
Table error: Object ID 373576369, index ID 1, partition ID
72057594039238656, alloc unit ID 71800601762136064 (type LOB data). The
off-row data node at page (1:13624), slot 11, text ID 26280329216 is
pointed to by page (1:56), slot 4 and by page (1:13624), slot 12.
Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 373576369, index ID 1, partition ID
72057594039238656, alloc unit ID 71800601762136064 (type LOB data). The
off-row data node at page (1:13624), slot 12, text ID 26280329216 does not
match its reference from page (1:56), slot 4.
Msg 8974, Level 16, State 1, Line 1
Table error: Object ID 373576369, index ID 1, partition ID
72057594039238656, alloc unit ID 71800601762136064 (type LOB data). The
off-row data node at page (1:13624), slot 12, text ID 26280329216 is
pointed to by page (1:56), slot 61 and by page (1:56), slot 4.
Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 373576369, index ID 1, partition ID
72057594039238656, alloc unit ID 71800601762136064 (type LOB data). The
off-row data node at page (1:13624), slot 13, text ID 31065440256 does not
match its reference from page (1:56), slot 7.
Msg 8974, Level 16, State 1, Line 1
Table error: Object ID 373576369, index ID 1, partition ID
72057594039238656, alloc unit ID 71800601762136064 (type LOB data). The
off-row data node at page (1:13624), slot 13, text ID 31065440256 is
pointed to by page (1:56), slot 7 and by page (1:11416), slot 44.
Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 373576369, index ID 1, partition ID
72057594039238656, alloc unit ID 71800601762136064 (type LOB data). The
off-row data node at page (1:13624), slot 14, text ID 31262507008 does not
match its reference from page (1:56), slot 7.
Msg 8974, Level 16, State 1, Line 1
Table error: Object ID 373576369, index ID 1, partition ID
72057594039238656, alloc unit ID 71800601762136064 (type LOB data). The
off-row data node at page (1:13624), slot 14, text ID 31262507008 is
pointed to by page (1:11417), slot 15 and by page (1:56), slot 7.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 373576369, index ID 1, partition ID
72057594039238656, alloc unit ID 71800601762136064 (type LOB data). The
off-row data node at page (1:13659), slot 13, text ID 25953894400 is
referenced by page (1:56), slot 9, but was not seen in the scan.
Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 373576369, index ID 1, partition ID
72057594039238656, alloc unit ID 71800601762136064 (type LOB data). The
off-row data node at page (1:13660), slot 7, text ID 26151878656 does not
match its reference from page (1:56), slot 9.
Msg 8974, Level 16, State 1, Line 1
Table error: Object ID 373576369, index ID 1, partition ID
72057594039238656, alloc unit ID 71800601762136064 (type LOB data). The
off-row data node at page (1:13660), slot 7, text ID 26151878656 is pointed
to by page (1:56), slot 50 and by page (1:56), slot 9.
Msg 8928, Level 16, State 1, Line 1
Object ID 373576369, index ID 1, partition ID 72057594039238656, alloc unit
ID 71800601762136064 (type LOB data): Page (1:13737) could not be
processed. See other errors for details.
Msg 8928, Level 16, State 1, Line 1
Object ID 373576369, index ID 1, partition ID 72057594039238656, alloc unit
ID 71800601762136064 (type LOB data): Page (1:13738) could not be
processed. See other errors for details.
Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 373576369, index ID 1, partition ID
72057594039238656, alloc unit ID 71800601762136064 (type LOB data). The
off-row data node at page (1:13739), slot 0, text ID 25952976896 is not
referenced.
Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 373576369, index ID 1, partition ID
72057594039238656, alloc unit ID 71800601762136064 (type LOB data). The
off-row data node at page (1:13740), slot 0, text ID 25953042432 is not
referenced.
Msg 8928, Level 16, State 1, Line 1
Object ID 373576369, index ID 1, partition ID 72057594039238656, alloc unit
ID 71800601762136064 (type LOB data): Page (1:13741) could not be
processed. See other errors for details.
Msg 8929, Level 16, State 1, Line 1
Object ID 373576369, index ID 1, partition ID 72057594039238656, alloc unit
ID 72057594043236352 (type In-row data): Errors found in off-row data with
ID 25952518144 owned by data record identified by RID = (1:56:1)
Msg 8929, Level 16, State 1, Line 1
Object ID 373576369, index ID 1, partition ID 72057594039238656, alloc unit
ID 72057594043236352 (type In-row data): Errors found in off-row data with
ID 25952780288 owned by data record identified by RID = (1:56:3)
Msg 8929, Level 16, State 1, Line 1
Object ID 373576369, index ID 1, partition ID 72057594039238656, alloc unit
ID 72057594043236352 (type In-row data): Errors found in off-row data with
ID 25952976896 owned by data record identified by RID = (1:56:4)
Msg 8929, Level 16, State 1, Line 1
Object ID 373576369, index ID 1, partition ID 72057594039238656, alloc unit
ID 72057594043236352 (type In-row data): Errors found in off-row data with
ID 25953501184 owned by data record identified by RID = (1:56:7)
Msg 8929, Level 16, State 1, Line 1
Object ID 373576369, index ID 1, partition ID 72057594039238656, alloc unit
ID 72057594043236352 (type In-row data): Errors found in off-row data with
ID 25953894400 owned by data record identified by RID = (1:56:9)
Msg 8929, Level 16, State 1, Line 1
Object ID 373576369, index ID 1, partition ID 72057594039238656, alloc unit
ID 72057594043236352 (type In-row data): Errors found in off-row data with
ID 26151878656 owned by data record identified by RID = (1:56:50)
Msg 8929, Level 16, State 1, Line 1
Object ID 373576369, index ID 1, partition ID 72057594039238656, alloc unit
ID 72057594043236352 (type In-row data): Errors found in off-row data with
ID 26153123840 owned by data record identified by RID = (1:56:53)
Msg 8929, Level 16, State 1, Line 1
Object ID 373576369, index ID 1, partition ID 72057594039238656, alloc unit
ID 72057594043236352 (type In-row data): Errors found in off-row data with
ID 26280329216 owned by data record identified by RID = (1:56:61)
Msg 8929, Level 16, State 1, Line 1
Object ID 373576369, index ID 1, partition ID 72057594039238656, alloc unit
ID 72057594043236352 (type In-row data): Errors found in off-row data with
ID 31002918912 owned by data record identified by RID = (1:11416:37)
Msg 8929, Level 16, State 1, Line 1
Object ID 373576369, index ID 1, partition ID 72057594039238656, alloc unit
ID 72057594043236352 (type In-row data): Errors found in off-row data with
ID 31065440256 owned by data record identified by RID = (1:11416:44)
Msg 8929, Level 16, State 1, Line 1
Object ID 373576369, index ID 1, partition ID 72057594039238656, alloc unit
ID 72057594043236352 (type In-row data): Errors found in off-row data with
ID 31262507008 owned by data record identified by RID = (1:11417:15)
There are 8770 rows in 135 pages for object "EPISLog".
CHECKDB found 0 allocation errors and 35 consistency errors in table
'EPISLog' (object ID 373576369).
DBCC results for 'CountryCodes'.
There are 239 rows in 1 pages for object "CountryCodes".
DBCC results for 'sys.queue_messages_565577053'.
There are 0 rows in 0 pages for object "sys.queue_messages_565577053".
DBCC results for 'sys.queue_messages_597577167'.
There are 0 rows in 0 pages for object "sys.queue_messages_597577167".
DBCC results for 'sys.queue_messages_629577281'.
There are 0 rows in 0 pages for object "sys.queue_messages_629577281".
DBCC results for 'PlayerNameBackupUpdate11'.
There are 14554 rows in 112 pages for object "PlayerNameBackupUpdate11".
DBCC results for 'CountryCodesISO2'.
There are 239 rows in 1 pages for object "CountryCodesISO2".
DBCC results for 'Players'.
There are 24059 rows in 418 pages for object "Players".
CHECKDB found 0 allocation errors and 68 consistency errors in database
'PlayersRJMM'.
repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKDB (PlayersRJMM).
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.|||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.
=========================================================|||On Wed, 09 Apr 2008 06:13:09 GMT, changliw@.online.microsoft.com (Charles
Wang[MSFT]) wrote:
> 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.
Sorry for the delay.
REPAIR_REBUILD returns the same error as the other repair options, saying
one of the database files is read only.
Before that, I had also tried to rebuild the indexes on a freshly restored
copy of the database through SQL Server Management Studio, select each
index of the affected tables in turn in the object browser tree,
right-click and select Rebuild: that succeeded without any error message,
which I find even stranger.
But I had to give up on the issue, because the customer still hasn't
provided me with a copy of the original MDF after I asked them for the
second time (in fact, I haven't heard from them again).
If I've been informed correctly, the behavior of the original database is
not fully the same as that of the restored backup, so I see no point in
trying to fix such a restored backup.|||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.
=========================================================

DBCC CheckDB Repair Allow Data Loss

I have a farily large db (~450 gig) for a customer. After rebooting the
server last week, the DB came up suspect. Looking in the logs, there were
several 823 errors. Our 3rd party hardware hosting company says there are
no disk problems.
We have no back up of the DB (yea, keep your comments to this to yourself).
Running DBCC CheckDB, 2 tables were identied as having a problem. This took
58 hours to run.
Recomendation was to run dbcc checkDB repair_allow_data_loss. I'm ok with
doing this as the DB is a reporting DW database, and can be rebuilt with any
missing data. It's been running 48 hours now. Will it take the same
amount of time as the checkDB (58 hours)? Longer? Any chance after this
runs that it will not be able to repair and the DB will be not usable.
Anyone with experience with this please let me know.
Thanks.Lots of questions/comments to ask you here.
1) What is the output of the CHECKDB? Why not just rebuild the database as
you described below? Did you look through the errors do determine whether
data loss is to be expected?
2) how long does CHECKDB normally take to run on this database? 58 hours is
excessively long (unless certain errors have been found and have kicked off
other, deeper checks)
3) what's the h/w of the box (# of CPUs, IO bandwidth of IO subsystem)
4) how does the hosting company know there are no disk problems? 823s are
hard IO errors (i.e. the page on disk is bad in some way)
5) no way to tell whether any of the errors can't be repaired (there are
some documented unrepairable errors) without seeing the list...
6) repair may take longer - depends what it has to do. It usually takes the
same amount of time as the CHECKDB plus any time required to effect the
repairs.
Thanks
--
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Steve" <ss@.Mailinator.com> wrote in message
news:uZK3jAPpGHA.220@.TK2MSFTNGP05.phx.gbl...
>I have a farily large db (~450 gig) for a customer. After rebooting the
>server last week, the DB came up suspect. Looking in the logs, there were
>several 823 errors. Our 3rd party hardware hosting company says there are
>no disk problems.
> We have no back up of the DB (yea, keep your comments to this to
> yourself).
> Running DBCC CheckDB, 2 tables were identied as having a problem. This
> took 58 hours to run.
> Recomendation was to run dbcc checkDB repair_allow_data_loss. I'm ok with
> doing this as the DB is a reporting DW database, and can be rebuilt with
> any missing data. It's been running 48 hours now. Will it take the same
> amount of time as the checkDB (58 hours)? Longer? Any chance after this
> runs that it will not be able to repair and the DB will be not usable.
> Anyone with experience with this please let me know.
> Thanks.
>

DBCC CheckDB Repair Allow Data Loss

I have a farily large db (~450 gig) for a customer. After rebooting the
server last week, the DB came up suspect. Looking in the logs, there were
several 823 errors. Our 3rd party hardware hosting company says there are
no disk problems.
We have no back up of the DB (yea, keep your comments to this to yourself).
Running DBCC CheckDB, 2 tables were identied as having a problem. This took
58 hours to run.
Recomendation was to run dbcc checkDB repair_allow_data_loss. I'm ok with
doing this as the DB is a reporting DW database, and can be rebuilt with any
missing data. It's been running 48 hours now. Will it take the same
amount of time as the checkDB (58 hours)? Longer? Any chance after this
runs that it will not be able to repair and the DB will be not usable.
Anyone with experience with this please let me know.
Thanks.Lots of questions/comments to ask you here.
1) What is the output of the CHECKDB? Why not just rebuild the database as
you described below? Did you look through the errors do determine whether
data loss is to be expected?
2) how long does CHECKDB normally take to run on this database? 58 hours is
excessively long (unless certain errors have been found and have kicked off
other, deeper checks)
3) what's the h/w of the box (# of CPUs, IO bandwidth of IO subsystem)
4) how does the hosting company know there are no disk problems? 823s are
hard IO errors (i.e. the page on disk is bad in some way)
5) no way to tell whether any of the errors can't be repaired (there are
some documented unrepairable errors) without seeing the list...
6) repair may take longer - depends what it has to do. It usually takes the
same amount of time as the CHECKDB plus any time required to effect the
repairs.
Thanks
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Steve" <ss@.Mailinator.com> wrote in message
news:uZK3jAPpGHA.220@.TK2MSFTNGP05.phx.gbl...
>I have a farily large db (~450 gig) for a customer. After rebooting the
>server last week, the DB came up suspect. Looking in the logs, there were
>several 823 errors. Our 3rd party hardware hosting company says there are
>no disk problems.
> We have no back up of the DB (yea, keep your comments to this to
> yourself).
> Running DBCC CheckDB, 2 tables were identied as having a problem. This
> took 58 hours to run.
> Recomendation was to run dbcc checkDB repair_allow_data_loss. I'm ok with
> doing this as the DB is a reporting DW database, and can be rebuilt with
> any missing data. It's been running 48 hours now. Will it take the same
> amount of time as the checkDB (58 hours)? Longer? Any chance after this
> runs that it will not be able to repair and the DB will be not usable.
> Anyone with experience with this please let me know.
> Thanks.
>

DBCC CHECKDB how to invoke to return any errors

Hello,
I would like to make intented error in the consistency of a database
and watch what will be the outcome from the DBCC CHECKDB.
Then train repair the database.
I would like to corrupt some data or index pages in mdf file.
How to do that ?
Tanks.
misoHmmmm...
well - you could try this, BUT BE CAREFUL!!! Do not run this on a live
database - it WILL break it!
It will definately cause DBCC CHECKDB to error.
Use this script at your own risk!!!
sp_configure allow, 1
go
reconfigure with override
go
update sysindexes set FirstIAM = 1234 where id = (select max(id) from
sysindexes)
go
sp_configure allow, 0
go
reconfigure with override
go
Immy
"Michal Valent" <valent@.spirit.sk> wrote in message
news:OeBJmQSRHHA.488@.TK2MSFTNGP06.phx.gbl...
> Hello,
> I would like to make intented error in the consistency of a database
> and watch what will be the outcome from the DBCC CHECKDB.
> Then train repair the database.
> I would like to corrupt some data or index pages in mdf file.
> How to do that ?
> Tanks.
> miso
>

dbcc checkdb found 2 errors

I'm in trouble...
dbcc checkdb found 2 errors on my database and this is the first time it was
checked. It says the lowest repair is the "allow data loss". If I run repair
and data is lost, how can I find out which data it is?
Select * from table
does not finish due to
error on page xxxxx.
oh i forgot, the trouble is I don't have a backup without that error, but I
have the way to recreate database but it's huge and I wonder if I could
identify the defect rows and insert only them?
tia
Zarko
Make sure you have an updated books online (Jan 2004) and search for the error numbers in there. Almost all
corruption errors has specific recommendations in the updated Books Online.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Zarko Jovanovic" <RmEaMkOnViEoTHvIoSmind_less@.bigfoot.com> wrote in message
news:1086238949.478311@.internet.fina.hr...
> I'm in trouble...
> dbcc checkdb found 2 errors on my database and this is the first time it was
> checked. It says the lowest repair is the "allow data loss". If I run repair
> and data is lost, how can I find out which data it is?
> Select * from table
> does not finish due to
> error on page xxxxx.
> oh i forgot, the trouble is I don't have a backup without that error, but I
> have the way to recreate database but it's huge and I wonder if I could
> identify the defect rows and insert only them?
> tia
> Zarko
>
|||Also the errors should give you the page numbers,
you might try to use dbcc page to see if you can look at the page and see
what data is on it..
Google dbcc page
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Zarko Jovanovic" <RmEaMkOnViEoTHvIoSmind_less@.bigfoot.com> wrote in message
news:1086238949.478311@.internet.fina.hr...
> I'm in trouble...
> dbcc checkdb found 2 errors on my database and this is the first time it
was
> checked. It says the lowest repair is the "allow data loss". If I run
repair
> and data is lost, how can I find out which data it is?
> Select * from table
> does not finish due to
> error on page xxxxx.
> oh i forgot, the trouble is I don't have a backup without that error, but
I
> have the way to recreate database but it's huge and I wonder if I could
> identify the defect rows and insert only them?
> tia
> Zarko
>
|||If you could post the errors, we might be able to give you a bit more insight.
Thanks,
Ryan Stonecipher
MS SQL Server Storage Engine
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message news:e4uZK0WSEHA.1544@.TK2MSFTNGP09.phx.gbl...
Also the errors should give you the page numbers,
you might try to use dbcc page to see if you can look at the page and see
what data is on it..
Google dbcc page
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Zarko Jovanovic" <RmEaMkOnViEoTHvIoSmind_less@.bigfoot.com> wrote in message
news:1086238949.478311@.internet.fina.hr...
> I'm in trouble...
> dbcc checkdb found 2 errors on my database and this is the first time it
was
> checked. It says the lowest repair is the "allow data loss". If I run
repair
> and data is lost, how can I find out which data it is?
> Select * from table
> does not finish due to
> error on page xxxxx.
> oh i forgot, the trouble is I don't have a backup without that error, but
I
> have the way to recreate database but it's huge and I wonder if I could
> identify the defect rows and insert only them?
> tia
> Zarko
>

dbcc checkdb found 2 errors

I'm in trouble...
dbcc checkdb found 2 errors on my database and this is the first time it was
checked. It says the lowest repair is the "allow data loss". If I run repair
and data is lost, how can I find out which data it is?
Select * from table
does not finish due to
error on page xxxxx.
oh i forgot, the trouble is I don't have a backup without that error, but I
have the way to recreate database but it's huge and I wonder if I could
identify the defect rows and insert only them?
tia
ZarkoMake sure you have an updated books online (Jan 2004) and search for the err
or numbers in there. Almost all
corruption errors has specific recommendations in the updated Books Online.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Zarko Jovanovic" <RmEaMkOnViEoTHvIoSmind_less@.bigfoot.com> wrote in message
news:1086238949.478311@.internet.fina.hr...
> I'm in trouble...
> dbcc checkdb found 2 errors on my database and this is the first time it w
as
> checked. It says the lowest repair is the "allow data loss". If I run repa
ir
> and data is lost, how can I find out which data it is?
> Select * from table
> does not finish due to
> error on page xxxxx.
> oh i forgot, the trouble is I don't have a backup without that error, but
I
> have the way to recreate database but it's huge and I wonder if I could
> identify the defect rows and insert only them?
> tia
> Zarko
>|||Also the errors should give you the page numbers,
you might try to use dbcc page to see if you can look at the page and see
what data is on it..
Google dbcc page
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Zarko Jovanovic" <RmEaMkOnViEoTHvIoSmind_less@.bigfoot.com> wrote in message
news:1086238949.478311@.internet.fina.hr...
> I'm in trouble...
> dbcc checkdb found 2 errors on my database and this is the first time it
was
> checked. It says the lowest repair is the "allow data loss". If I run
repair
> and data is lost, how can I find out which data it is?
> Select * from table
> does not finish due to
> error on page xxxxx.
> oh i forgot, the trouble is I don't have a backup without that error, but
I
> have the way to recreate database but it's huge and I wonder if I could
> identify the defect rows and insert only them?
> tia
> Zarko
>|||If you could post the errors, we might be able to give you a bit more insigh
t.
Thanks,
Ryan Stonecipher
MS SQL Server Storage Engine
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message news:e
4uZK0WSEHA.1544@.TK2MSFTNGP09.phx.gbl...
Also the errors should give you the page numbers,
you might try to use dbcc page to see if you can look at the page and see
what data is on it..
Google dbcc page
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Zarko Jovanovic" <RmEaMkOnViEoTHvIoSmind_less@.bigfoot.com> wrote in message
news:1086238949.478311@.internet.fina.hr...
> I'm in trouble...
> dbcc checkdb found 2 errors on my database and this is the first time it
was
> checked. It says the lowest repair is the "allow data loss". If I run
repair
> and data is lost, how can I find out which data it is?
> Select * from table
> does not finish due to
> error on page xxxxx.
> oh i forgot, the trouble is I don't have a backup without that error, but
I
> have the way to recreate database but it's huge and I wonder if I could
> identify the defect rows and insert only them?
> tia
> Zarko
>

dbcc checkdb found 2 errors

I'm in trouble...
dbcc checkdb found 2 errors on my database and this is the first time it was
checked. It says the lowest repair is the "allow data loss". If I run repair
and data is lost, how can I find out which data it is?
Select * from table
does not finish due to
error on page xxxxx.
oh i forgot, the trouble is I don't have a backup without that error, but I
have the way to recreate database but it's huge and I wonder if I could
identify the defect rows and insert only them?
tia
ZarkoMake sure you have an updated books online (Jan 2004) and search for the error numbers in there. Almost all
corruption errors has specific recommendations in the updated Books Online.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Zarko Jovanovic" <RmEaMkOnViEoTHvIoSmind_less@.bigfoot.com> wrote in message
news:1086238949.478311@.internet.fina.hr...
> I'm in trouble...
> dbcc checkdb found 2 errors on my database and this is the first time it was
> checked. It says the lowest repair is the "allow data loss". If I run repair
> and data is lost, how can I find out which data it is?
> Select * from table
> does not finish due to
> error on page xxxxx.
> oh i forgot, the trouble is I don't have a backup without that error, but I
> have the way to recreate database but it's huge and I wonder if I could
> identify the defect rows and insert only them?
> tia
> Zarko
>|||Also the errors should give you the page numbers,
you might try to use dbcc page to see if you can look at the page and see
what data is on it..
Google dbcc page
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Zarko Jovanovic" <RmEaMkOnViEoTHvIoSmind_less@.bigfoot.com> wrote in message
news:1086238949.478311@.internet.fina.hr...
> I'm in trouble...
> dbcc checkdb found 2 errors on my database and this is the first time it
was
> checked. It says the lowest repair is the "allow data loss". If I run
repair
> and data is lost, how can I find out which data it is?
> Select * from table
> does not finish due to
> error on page xxxxx.
> oh i forgot, the trouble is I don't have a backup without that error, but
I
> have the way to recreate database but it's huge and I wonder if I could
> identify the defect rows and insert only them?
> tia
> Zarko
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0017_01C4495A.57644590
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
If you could post the errors, we might be able to give you a bit more =insight.
Thanks,
Ryan Stonecipher
MS SQL Server Storage Engine
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message =news:e4uZK0WSEHA.1544@.TK2MSFTNGP09.phx.gbl...
Also the errors should give you the page numbers,
you might try to use dbcc page to see if you can look at the page and =see
what data is on it..
Google dbcc page
-- Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Zarko Jovanovic" <RmEaMkOnViEoTHvIoSmind_less@.bigfoot.com> wrote in =message
news:1086238949.478311@.internet.fina.hr...
> I'm in trouble...
>
> dbcc checkdb found 2 errors on my database and this is the first =time it
was
> checked. It says the lowest repair is the "allow data loss". If I =run
repair
> and data is lost, how can I find out which data it is?
> Select * from table
> does not finish due to
> error on page xxxxx.
>
> oh i forgot, the trouble is I don't have a backup without that =error, but
I
> have the way to recreate database but it's huge and I wonder if I =could
> identify the defect rows and insert only them?
>
> tia
>
> Zarko
>
>
--=_NextPart_000_0017_01C4495A.57644590
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

If you could post the errors, we might be able to =give you a bit more insight.
Thanks,
Ryan Stonecipher
MS SQL Server Storage Engine
"Wayne Snyder" wrote in message news:e4uZK0WSEHA.1544=@.TK2MSFTNGP09.phx.gbl...
Also the errors should give you the page numbers,you might try to use =dbcc page to see if you can look at the page and seewhat data is on it..Google dbcc page-- Wayne Snyder, MCDBA, SQL =Server MVPMariner, Charlotte, NChttp://www.mariner-usa.com">www.mariner-usa.com(Please =respond only to the newsgroups.)I support the Professional Association =of SQL Server (PASS) and it'scommunity of SQL Server professionals.http://www.sqlpass.org">www.sqlpass.org"Zarko =Jovanovic" wrote in messagenews:1086238949.478311@.i=nternet.fina.hr...> I'm in trouble...>> dbcc checkdb found 2 errors on my =database and this is the first time itwas> checked. It says the =lowest repair is the "allow data loss". If I runrepair> and data =is lost, how can I find out which data it is?> Select * from =table> does not finish due to> error on page xxxxx.>> oh i =forgot, the trouble is I don't have a backup without that error, =butI> have the way to recreate database but it's huge and I wonder if I =could> identify the defect rows and insert only them?>> tia>> =Zarko>>

---

--=_NextPart_000_0017_01C4495A.57644590--

DBCC CHECKDB Error

I am getting the following error after checking the database with DBCC
CHECKDB('DATABASE NAME'). We tried to repair with repair_allow_data_loos
option. But it is not repairing. I am not a professional DBA please advise
what to do.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:5604) in database ID 7 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:5604) with latch type SH. VerifyPageId
failed.
Hi,
Best solution will be to restore the database from a healthy backup and to
analyze further open a case with Microsoft product support.
These errors occurs when there is a Hardware issue, so contact your hardware
vendor and ask them to do a hardware diagonostic.
Replace the hardware if any fault is rectified
Thanks
Hari
SQL Server MVP
"Sarma" <Sarma@.discussions.microsoft.com> wrote in message
news:C9AD329A-1066-482E-AAC8-1248BD70FAE8@.microsoft.com...
>I am getting the following error after checking the database with DBCC
> CHECKDB('DATABASE NAME'). We tried to repair with repair_allow_data_loos
> option. But it is not repairing. I am not a professional DBA please advise
> what to do.
> Server: Msg 8906, Level 16, State 1, Line 1
> Page (1:5604) in database ID 7 is allocated in the SGAM (1:3) and PFS
> (1:1),
> but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED
> 0_PCT_FULL'.
> Server: Msg 8966, Level 16, State 1, Line 1
> Could not read and latch page (1:5604) with latch type SH. VerifyPageId
> failed.
|||Here are my general recommendations for these types of situations.
Also, below is from Books Online, specific for this error number:
Severity Level 16
Message Text
Page P_ID in database ID DB_ID is allocated in the SGAM SGAM_P_ID and PFS PFS_P_ID, but was not
allocated in any IAM. PFS flags 'PFS_FLAGS'.
Explanation
A PFS page has one byte per page for all pages in the file interval it maps (approximately 64 Mb).
Each byte contains, among other things, a bit that, when set, indicates that the page is from a
mixed extent (that is, it is a mixed page).
An IAM page has, among other things, an array of eight page IDs (called the mixed page array) to
hold the mixed pages allocated to the index. Note that IAM pages themselves are mixed pages, but IAM
pages are not self-referential (that is, the IAM page ID itself is not stored in its own mixed page
array).
This message means that a page has the 'mixed page' bit set in its PFS byte, but the page was not
seen as an IAM page and was not seen in an IAM page's mixed page array.
Action
HARDWARE FAILURE
Run hardware diagnostics and correct any problems. Also examine the Microsoft Windows NT system
and application logs and the SQL ServerT error log to see if the error occurred as the result of
hardware failure. Fix any hardware related problems.
If you have persistent data corruption problems, try to swap out different hardware components to
isolate the problem. Check to ensure that your system does not have write caching enabled on the
disk controller. If you suspect this to be the problem, contact your hardware vendor.
Finally, you might find it beneficial to switch to a completely new hardware system, including
reformatting the disk drives and reinstalling the operating system.
RESTORE FROM BACKUP
If the problem is not hardware related and a known clean backup is available, restore the database
from the backup.
DBCC CHECKDB
If no clean backup is available, execute DBCC CHECKDB without a repair clause to determine the
extent of the corruption. DBCC CHECKDB will recommend a repair clause to use. Then, execute DBCC
CHECKDB with the appropriate repair clause to repair the corruption.
Caution If you are unsure what effect DBCC CHECKDB with a repair clause has on your data, contact
your primary support provider before executing this statement.
If the page is an IAM page, performing a repair will de-allocate the page, and the IAM chain will be
patched by the repairs for accompanying errors.
Otherwise, a general purpose routine to repair doubly allocated pages is used. It takes two sets of
IDs, one for each of the two indexes (IAM chains) that have the page allocated. In this case, we use
the IDs derived from the page itself, and an invalid set of IDs.
The algorithm of the general purpose routine is as follows:
1.. Check page validity.
a.. If the page is not a valid page ID in the database, de-allocate it from both indexes and
return.
b.. If the page is corrupt, de-allocate it from both indexes, then return.
2.. Work out which index the page actually belongs to.
3.. Check for the allocation page.
a.. If the page is an IAM page or allocation page, de-allocate it from both indexes passed in.
b.. If the page is an IAM page and the index it thinks it belongs to cannot be found,
de-allocate the page.
c.. Return.
4.. If the page belongs to the first index passed in:
a.. De-allocate it from the second index passed in.
b.. Try to allocate it to the first index. If this fails, de-allocate the page.
c.. Return.
Note We should not get below here for this particular error.
5.. De-allocate the page from the first index.
6.. If the page does not belong to second index:
a.. If the second index is not invalid, de-allocate the page from the second index.
b.. Try to allocate the page to the real owning index. If it fails, de-allocate the page.
c.. Return.
7.. Page must belong to second index passed in.
a.. Try to allocate the page to the second index. If it fails, de-allocate the page.
b.. Return.
Caution This repair may cause data loss.
If running DBCC CHECKDB with one of the repair clauses does not correct the problem, contact your
primary support provider.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sarma" <Sarma@.discussions.microsoft.com> wrote in message
news:C9AD329A-1066-482E-AAC8-1248BD70FAE8@.microsoft.com...
>I am getting the following error after checking the database with DBCC
> CHECKDB('DATABASE NAME'). We tried to repair with repair_allow_data_loos
> option. But it is not repairing. I am not a professional DBA please advise
> what to do.
> Server: Msg 8906, Level 16, State 1, Line 1
> Page (1:5604) in database ID 7 is allocated in the SGAM (1:3) and PFS (1:1),
> but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED
> 0_PCT_FULL'.
> Server: Msg 8966, Level 16, State 1, Line 1
> Could not read and latch page (1:5604) with latch type SH. VerifyPageId
> failed.
begin 666 caution.gif
M1TE&.#EA# `+`/<``````#,``&8``)D``,P``/\````S`#,S`&8S`)DS`,PS
M`/\S``!F`#-F`&9F`)EF`,QF`/]F``"9`#.9`&:9`)F9`,R9`/^9``#,`#/,
M`&;,`)G,`,S,`/_,``#_`#/_`&;_`)G_`,S_`/__````,S,`,V8`,YD`,\P`
M,_\`,P`S,S,S,V8S,YDS,\PS,_\S,P!F,S-F,V9F,YEF,\QF,_]F,P"9,S.9
M,V:9,YF9,\R9,_^9,P#,,S/,,V;,,YG,,\S,,__,,P#_,S/_,V;_,YG_,\S_
M,___,P``9C,`9F8`9ID`9LP`9O\`9@.`S9C,S9F8S9IDS9LPS9 O\S9@.!F9C-F
M9F9F9IEF9LQF9O]F9@."99C.99F:99IF99LR99O^99@.#,9C/,9F;,9IG,9LS,
M9O_,9@.#_9C/_9F;_9IG_9LS_9O__9@.``F3,`F68`F9D`F<P`F?\`F0`SF3,S
MF68SF9DSF<PSF?\SF0!FF3-FF69FF9EFF<QFF?]FF0"9F3.9F6:9F9F9F<R9
MF?^9F0#,F3/,F6;,F9G,F<S,F?_,F0#_F3/_F6;_F9G_F<S_F?__F0``S#,`
MS&8`S)D`S,P`S/\`S `SS#,SS&8SS)DSS,PSS/\SS !FS#-FS&9FS)EFS,QF
MS/]FS "9S#.9S&:9S)F9S,R9S/^9S #,S#/,S&;,S)G,S,S,S/_,S #_S#/_
MS&;_S)G_S,S_S/__S ``_S,`_V8`_YD`_\P`__\`_P`S_S,S_V8S_YDS_\PS
M__\S_P!F_S-F_V9F_YEF_\QF__]F_P"9_S.9_V:9_YF9_\R9__^9_P#,_S/,
M_V;,_YG,_\S,___,_P#__S/__V;__YG__\S______P``````````````````
M````````````````````````````````````````````````` ```````````
M````````````````````````````````````````````````` ```````````
M`````````````````````"'Y! $``*P`+ `````,``L`0 @.B`%D)'$BPH,&#
;K HH1)AP(4*%!1A"9$A1X,2'#BMBC#@.P( `[
`
end

DBCC CHECKDB Error

I am getting the following error after checking the database with DBCC
CHECKDB('DATABASE NAME'). We tried to repair with repair_allow_data_loos
option. But it is not repairing. I am not a professional DBA please advise
what to do.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:5604) in database ID 7 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:5604) with latch type SH. VerifyPageId
failed.Hi,
Best solution will be to restore the database from a healthy backup and to
analyze further open a case with Microsoft product support.
These errors occurs when there is a Hardware issue, so contact your hardware
vendor and ask them to do a hardware diagonostic.
Replace the hardware if any fault is rectified
Thanks
Hari
SQL Server MVP
"Sarma" <Sarma@.discussions.microsoft.com> wrote in message
news:C9AD329A-1066-482E-AAC8-1248BD70FAE8@.microsoft.com...
>I am getting the following error after checking the database with DBCC
> CHECKDB('DATABASE NAME'). We tried to repair with repair_allow_data_loos
> option. But it is not repairing. I am not a professional DBA please advise
> what to do.
> Server: Msg 8906, Level 16, State 1, Line 1
> Page (1:5604) in database ID 7 is allocated in the SGAM (1:3) and PFS
> (1:1),
> but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED
> 0_PCT_FULL'.
> Server: Msg 8966, Level 16, State 1, Line 1
> Could not read and latch page (1:5604) with latch type SH. VerifyPageId
> failed.|||Here are my general recommendations for these types of situations.
Also, below is from Books Online, specific for this error number:
Severity Level 16
Message Text
Page P_ID in database ID DB_ID is allocated in the SGAM SGAM_P_ID and PFS PFS_P_ID, but was not
allocated in any IAM. PFS flags 'PFS_FLAGS'.
Explanation
A PFS page has one byte per page for all pages in the file interval it maps (approximately 64 Mb).
Each byte contains, among other things, a bit that, when set, indicates that the page is from a
mixed extent (that is, it is a mixed page).
An IAM page has, among other things, an array of eight page IDs (called the mixed page array) to
hold the mixed pages allocated to the index. Note that IAM pages themselves are mixed pages, but IAM
pages are not self-referential (that is, the IAM page ID itself is not stored in its own mixed page
array).
This message means that a page has the 'mixed page' bit set in its PFS byte, but the page was not
seen as an IAM page and was not seen in an IAM page's mixed page array.
Action
HARDWARE FAILURE
Run hardware diagnostics and correct any problems. Also examine the Microsoft® Windows NT® system
and application logs and the SQL ServerT error log to see if the error occurred as the result of
hardware failure. Fix any hardware related problems.
If you have persistent data corruption problems, try to swap out different hardware components to
isolate the problem. Check to ensure that your system does not have write caching enabled on the
disk controller. If you suspect this to be the problem, contact your hardware vendor.
Finally, you might find it beneficial to switch to a completely new hardware system, including
reformatting the disk drives and reinstalling the operating system.
RESTORE FROM BACKUP
If the problem is not hardware related and a known clean backup is available, restore the database
from the backup.
DBCC CHECKDB
If no clean backup is available, execute DBCC CHECKDB without a repair clause to determine the
extent of the corruption. DBCC CHECKDB will recommend a repair clause to use. Then, execute DBCC
CHECKDB with the appropriate repair clause to repair the corruption.
Caution If you are unsure what effect DBCC CHECKDB with a repair clause has on your data, contact
your primary support provider before executing this statement.
If the page is an IAM page, performing a repair will de-allocate the page, and the IAM chain will be
patched by the repairs for accompanying errors.
Otherwise, a general purpose routine to repair doubly allocated pages is used. It takes two sets of
IDs, one for each of the two indexes (IAM chains) that have the page allocated. In this case, we use
the IDs derived from the page itself, and an invalid set of IDs.
The algorithm of the general purpose routine is as follows:
1.. Check page validity.
a.. If the page is not a valid page ID in the database, de-allocate it from both indexes and
return.
b.. If the page is corrupt, de-allocate it from both indexes, then return.
2.. Work out which index the page actually belongs to.
3.. Check for the allocation page.
a.. If the page is an IAM page or allocation page, de-allocate it from both indexes passed in.
b.. If the page is an IAM page and the index it thinks it belongs to cannot be found,
de-allocate the page.
c.. Return.
4.. If the page belongs to the first index passed in:
a.. De-allocate it from the second index passed in.
b.. Try to allocate it to the first index. If this fails, de-allocate the page.
c.. Return.
Note We should not get below here for this particular error.
5.. De-allocate the page from the first index.
6.. If the page does not belong to second index:
a.. If the second index is not invalid, de-allocate the page from the second index.
b.. Try to allocate the page to the real owning index. If it fails, de-allocate the page.
c.. Return.
7.. Page must belong to second index passed in.
a.. Try to allocate the page to the second index. If it fails, de-allocate the page.
b.. Return.
Caution This repair may cause data loss.
If running DBCC CHECKDB with one of the repair clauses does not correct the problem, contact your
primary support provider.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sarma" <Sarma@.discussions.microsoft.com> wrote in message
news:C9AD329A-1066-482E-AAC8-1248BD70FAE8@.microsoft.com...
>I am getting the following error after checking the database with DBCC
> CHECKDB('DATABASE NAME'). We tried to repair with repair_allow_data_loos
> option. But it is not repairing. I am not a professional DBA please advise
> what to do.
> Server: Msg 8906, Level 16, State 1, Line 1
> Page (1:5604) in database ID 7 is allocated in the SGAM (1:3) and PFS (1:1),
> but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED
> 0_PCT_FULL'.
> Server: Msg 8966, Level 16, State 1, Line 1
> Could not read and latch page (1:5604) with latch type SH. VerifyPageId
> failed.
begin 666 caution.gif
M1TE&.#EA# `+`/<``````#,``&8``)D``,P``/\````S`#,S`&8S`)DS`,PS
M`/\S``!F`#-F`&9F`)EF`,QF`/]F``"9`#.9`&:9`)F9`,R9`/^9``#,`#/,
M`&;,`)G,`,S,`/_,``#_`#/_`&;_`)G_`,S_`/__````,S,`,V8`,YD`,\P`
M,_\`,P`S,S,S,V8S,YDS,\PS,_\S,P!F,S-F,V9F,YEF,\QF,_]F,P"9,S.9
M,V:9,YF9,\R9,_^9,P#,,S/,,V;,,YG,,\S,,__,,P#_,S/_,V;_,YG_,\S_
M,___,P``9C,`9F8`9ID`9LP`9O\`9@.`S9C,S9F8S9IDS9LPS9O\S9@.!F9C-F
M9F9F9IEF9LQF9O]F9@."99C.99F:99IF99LR99O^99@.#,9C/,9F;,9IG,9LS,
M9O_,9@.#_9C/_9F;_9IG_9LS_9O__9@.``F3,`F68`F9D`F<P`F?\`F0`SF3,S
MF68SF9DSF<PSF?\SF0!FF3-FF69FF9EFF<QFF?]FF0"9F3.9F6:9F9F9F<R9
MF?^9F0#,F3/,F6;,F9G,F<S,F?_,F0#_F3/_F6;_F9G_F<S_F?__F0``S#,`
MS&8`S)D`S,P`S/\`S `SS#,SS&8SS)DSS,PSS/\SS !FS#-FS&9FS)EFS,QF
MS/]FS "9S#.9S&:9S)F9S,R9S/^9S #,S#/,S&;,S)G,S,S,S/_,S #_S#/_
MS&;_S)G_S,S_S/__S ``_S,`_V8`_YD`_\P`__\`_P`S_S,S_V8S_YDS_\PS
M__\S_P!F_S-F_V9F_YEF_\QF__]F_P"9_S.9_V:9_YF9_\R9__^9_P#,_S/,
M_V;,_YG,_\S,___,_P#__S/__V;__YG__\S______P``````````````````
M````````````````````````````````````````````````````````````
M````````````````````````````````````````````````````````````
M`````````````````````"'Y! $``*P`+ `````,``L`0 @.B`%D)'$BPH,&#
;K HH1)AP(4*%!1A"9$A1X,2'#BMBC#@.P( `[
`
end

DBCC CHECKDB Error

I am getting the following error after checking the database with DBCC
CHECKDB('DATABASE NAME'). We tried to repair with repair_allow_data_loos
option. But it is not repairing. I am not a professional DBA please advise
what to do.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:5604) in database ID 7 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:5604) with latch type SH. VerifyPageId
failed.Hi,
Best solution will be to restore the database from a healthy backup and to
analyze further open a case with Microsoft product support.
These errors occurs when there is a hardware issue, so contact your hardware
vendor and ask them to do a hardware diagonostic.
Replace the hardware if any fault is rectified
Thanks
Hari
SQL Server MVP
"Sarma" <Sarma@.discussions.microsoft.com> wrote in message
news:C9AD329A-1066-482E-AAC8-1248BD70FAE8@.microsoft.com...
>I am getting the following error after checking the database with DBCC
> CHECKDB('DATABASE NAME'). We tried to repair with repair_allow_data_loos
> option. But it is not repairing. I am not a professional DBA please advise
> what to do.
> Server: Msg 8906, Level 16, State 1, Line 1
> Page (1:5604) in database ID 7 is allocated in the SGAM (1:3) and PFS
> (1:1),
> but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED
> 0_PCT_FULL'.
> Server: Msg 8966, Level 16, State 1, Line 1
> Could not read and latch page (1:5604) with latch type SH. VerifyPageId
> failed.