DBfile usage: repair

Showing posts with label repair_rebuild. Show all posts

Friday, February 24, 2012

DBCC CHECKTABLE with REPAIR_REBUILD

How can I use it on the dbo.syscomments table with only
REPAIR_REBUILD mode ?
DBCC CHECKTABLE
( 'table_name' | 'view_name'
[ , NOINDEX
| index_id
| { REPAIR_ALLOW_DATA_LOSS
| REPAIR_FAST
| REPAIR_REBUILD }
]
) [ WITH { [ ALL_ERRORMSGS | NO_INFOMSGS ]
[ , [ TABLOCK ] ]
[ , [ ESTIMATEONLY ] ]
[ , [ PHYSICAL_ONLY ] ]
}
]
What error are you trying to repair?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"MK" <anonymous@.discussions.microsoft.com> wrote in message
news:000001c4ac8f$6818c0d0$a501280a@.phx.gbl...
> How can I use it on the dbo.syscomments table with only
> REPAIR_REBUILD mode ?
>
> DBCC CHECKTABLE
> ( 'table_name' | 'view_name'
> [ , NOINDEX
> | index_id
> | { REPAIR_ALLOW_DATA_LOSS
> | REPAIR_FAST
> | REPAIR_REBUILD }
> ]
> ) [ WITH { [ ALL_ERRORMSGS | NO_INFOMSGS ]
> [ , [ TABLOCK ] ]
> [ , [ ESTIMATEONLY ] ]
> [ , [ PHYSICAL_ONLY ] ]
> }
> ]
>
>
|||Yuu should be able to run DBCC CHECKTABLE ('syscomments', REPAIR_REBUILD) without a problem. This will only allow REPAIR to repair errors that can be fixed by a rebuild the clustered index on syscomments. Have you found corruption in this table? I'm curious as to why you're looking for a way to do this.
Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine, DBCC
"MK" <anonymous@.discussions.microsoft.com> wrote in message news:000001c4ac8f$6818c0d0$a501280a@.phx.gbl...
How can I use it on the dbo.syscomments table with only
REPAIR_REBUILD mode ?
DBCC CHECKTABLE
( 'table_name' | 'view_name'
[ , NOINDEX
| index_id
| { REPAIR_ALLOW_DATA_LOSS
| REPAIR_FAST
| REPAIR_REBUILD }
]
) [ WITH { [ ALL_ERRORMSGS | NO_INFOMSGS ]
[ , [ TABLOCK ] ]
[ , [ ESTIMATEONLY ] ]
[ , [ PHYSICAL_ONLY ] ]
}
]
|||DBCC CHECKTABLE ('syscomments')
GO
Server: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 6, index ID 1. Page (1:94571) is
missing a reference from previous page (1:1031985).
Possible chain linkage problem.
Server: Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 6, index ID 1. Page (1:1031984) was
not seen in the scan although its parent (1:51) and
previous (4:4594265) refer to it. Check any previous
errors.
Server: Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 6, index ID 1. Page (1:1031985) was
not seen in the scan although its parent (1:51) and
previous (1:94572) refer to it. Check any previous errors.
Server: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 6, index ID 1. Page (4:2785610) is
missing a reference from previous page (1:1031984).
Possible chain linkage problem.
DBCC results for 'syscomments'.
There are 674 rows in 133 pages for object 'syscomments'.
CHECKTABLE found 0 allocation errors and 4 consistency
errors in table 'syscomments' (object ID 6).
repair_rebuild is the minimum repair level for the errors
found by DBCC CHECKTABLE (SAB.dbo.syscomments ).

>--Original Message--
>What error are you trying to repair?
>--
>Paul Randal
>Dev Lead, Microsoft SQL Server Storage Engine
>This posting is provided "AS IS" with no warranties, and
confers no rights.

DBCC CHECKTABLE on a View

I'm trying to run the following statement:
DBCC CHECKTABLE([vLostSaleCount], REPAIR_REBUILD) WITH NO_INFOMSGS
But I get this error message:
Server: Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'vLostSaleCount'. Check sysobjects.
So I run this statement:
SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'vLostSaleCount')
And get this result:
name id xtype uid info status base_schema_ver replinfo parent_obj crdate
ftcatid schema_ver stats_schema_ver type userstat sysstat indexdel refdate
version deltrig instrig updtrig seltrig category cache
vLostSaleCount 830626002 V 1 2 1610612736 16 0 0 2004-05-03 13:19:14.993 0
16 0 V 0 2 0 2004-05-03 13:19:14.993 0 0 0 0 0 0 0
(1 row(s) affected)
What is going on here? TIA!CHECKTABLE is checking physical consistency of the data. A view doesn't store any data so there is
nothing to check. Run CHECKTABLE on the tables that the view is querying instead.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in message
news:OK8fPsVPGHA.812@.TK2MSFTNGP10.phx.gbl...
> I'm trying to run the following statement:
> DBCC CHECKTABLE([vLostSaleCount], REPAIR_REBUILD) WITH NO_INFOMSGS
> But I get this error message:
> Server: Msg 2501, Level 16, State 1, Line 1
> Could not find a table or object named 'vLostSaleCount'. Check sysobjects.
> So I run this statement:
> SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'vLostSaleCount')
> And get this result:
> name id xtype uid info status base_schema_ver replinfo parent_obj crdate
> ftcatid schema_ver stats_schema_ver type userstat sysstat indexdel refdate
> version deltrig instrig updtrig seltrig category cache
> vLostSaleCount 830626002 V 1 2 1610612736 16 0 0 2004-05-03 13:19:14.993 0
> 16 0 V 0 2 0 2004-05-03 13:19:14.993 0 0 0 0 0 0 0
> (1 row(s) affected)
> What is going on here? TIA!
>|||Thanks! I'm taking your word for it, but that seems to be in conflict with
what BOL says. Is BOL in error? Here is DBCC CHECKTABLE from BOL:
DBCC CHECKTABLE
Checks the integrity of the data, index, text, ntext, and image pages for
the specified table or indexed view.
Syntax
DBCC CHECKTABLE
( 'table_name' | 'view_name'
[ , NOINDEX
| index_id
| { REPAIR_ALLOW_DATA_LOSS
| REPAIR_FAST
| REPAIR_REBUILD }
]
) [ WITH { [ ALL_ERRORMSGS | NO_INFOMSGS ]
[ , [ TABLOCK ] ]
[ , [ ESTIMATEONLY ] ]
[ , [ PHYSICAL_ONLY ] ]
}
]
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uMAHDuVPGHA.648@.TK2MSFTNGP14.phx.gbl...
> CHECKTABLE is checking physical consistency of the data. A view doesn't
store any data so there is
> nothing to check. Run CHECKTABLE on the tables that the view is querying
instead.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in
message
> news:OK8fPsVPGHA.812@.TK2MSFTNGP10.phx.gbl...
> > I'm trying to run the following statement:
> >
> > DBCC CHECKTABLE([vLostSaleCount], REPAIR_REBUILD) WITH NO_INFOMSGS
> >
> > But I get this error message:
> >
> > Server: Msg 2501, Level 16, State 1, Line 1
> > Could not find a table or object named 'vLostSaleCount'. Check
sysobjects.
> >
> > So I run this statement:
> >
> > SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'vLostSaleCount')
> >
> > And get this result:
> >
> > name id xtype uid info status base_schema_ver replinfo parent_obj crdate
> > ftcatid schema_ver stats_schema_ver type userstat sysstat indexdel
refdate
> > version deltrig instrig updtrig seltrig category cache
> > vLostSaleCount 830626002 V 1 2 1610612736 16 0 0 2004-05-03
13:19:14.993 0
> > 16 0 V 0 2 0 2004-05-03 13:19:14.993 0 0 0 0 0 0 0
> >
> > (1 row(s) affected)
> >
> > What is going on here? TIA!
> >
> >
>|||Is the view qualified with a name other than DBO?
"Ron Hinds" wrote:
> I'm trying to run the following statement:
> DBCC CHECKTABLE([vLostSaleCount], REPAIR_REBUILD) WITH NO_INFOMSGS
> But I get this error message:
> Server: Msg 2501, Level 16, State 1, Line 1
> Could not find a table or object named 'vLostSaleCount'. Check sysobjects.
> So I run this statement:
> SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'vLostSaleCount')
> And get this result:
> name id xtype uid info status base_schema_ver replinfo parent_obj crdate
> ftcatid schema_ver stats_schema_ver type userstat sysstat indexdel refdate
> version deltrig instrig updtrig seltrig category cache
> vLostSaleCount 830626002 V 1 2 1610612736 16 0 0 2004-05-03 13:19:14.993 0
> 16 0 V 0 2 0 2004-05-03 13:19:14.993 0 0 0 0 0 0 0
> (1 row(s) affected)
> What is going on here? TIA!
>
>|||No - I even tried qualifying it with dbo and still got the error.
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:0309BF46-E4A8-4F84-8B12-68B767AE40B6@.microsoft.com...
> Is the view qualified with a name other than DBO?
> "Ron Hinds" wrote:
> > I'm trying to run the following statement:
> >
> > DBCC CHECKTABLE([vLostSaleCount], REPAIR_REBUILD) WITH NO_INFOMSGS
> >
> > But I get this error message:
> >
> > Server: Msg 2501, Level 16, State 1, Line 1
> > Could not find a table or object named 'vLostSaleCount'. Check
sysobjects.
> >
> > So I run this statement:
> >
> > SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'vLostSaleCount')
> >
> > And get this result:
> >
> > name id xtype uid info status base_schema_ver replinfo parent_obj crdate
> > ftcatid schema_ver stats_schema_ver type userstat sysstat indexdel
refdate
> > version deltrig instrig updtrig seltrig category cache
> > vLostSaleCount 830626002 V 1 2 1610612736 16 0 0 2004-05-03
13:19:14.993 0
> > 16 0 V 0 2 0 2004-05-03 13:19:14.993 0 0 0 0 0 0 0
> >
> > (1 row(s) affected)
> >
> > What is going on here? TIA!
> >
> >
> >|||BOL is talking about indexed views.
An indexed view is not a 'normal' view and use database storage.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Ron Hinds" wrote:
> Thanks! I'm taking your word for it, but that seems to be in conflict with
> what BOL says. Is BOL in error? Here is DBCC CHECKTABLE from BOL:
> DBCC CHECKTABLE
> Checks the integrity of the data, index, text, ntext, and image pages for
> the specified table or indexed view.
> Syntax
> DBCC CHECKTABLE
> ( 'table_name' | 'view_name'
> [ , NOINDEX
> | index_id
> | { REPAIR_ALLOW_DATA_LOSS
> | REPAIR_FAST
> | REPAIR_REBUILD }
> ]
> ) [ WITH { [ ALL_ERRORMSGS | NO_INFOMSGS ]
> [ , [ TABLOCK ] ]
> [ , [ ESTIMATEONLY ] ]
> [ , [ PHYSICAL_ONLY ] ]
> }
> ]
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:uMAHDuVPGHA.648@.TK2MSFTNGP14.phx.gbl...
> > CHECKTABLE is checking physical consistency of the data. A view doesn't
> store any data so there is
> > nothing to check. Run CHECKTABLE on the tables that the view is querying
> instead.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> > Blog: http://solidqualitylearning.com/blogs/tibor/
> >
> >
> > "Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in
> message
> > news:OK8fPsVPGHA.812@.TK2MSFTNGP10.phx.gbl...
> > > I'm trying to run the following statement:
> > >
> > > DBCC CHECKTABLE([vLostSaleCount], REPAIR_REBUILD) WITH NO_INFOMSGS
> > >
> > > But I get this error message:
> > >
> > > Server: Msg 2501, Level 16, State 1, Line 1
> > > Could not find a table or object named 'vLostSaleCount'. Check
> sysobjects.
> > >
> > > So I run this statement:
> > >
> > > SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'vLostSaleCount')
> > >
> > > And get this result:
> > >
> > > name id xtype uid info status base_schema_ver replinfo parent_obj crdate
> > > ftcatid schema_ver stats_schema_ver type userstat sysstat indexdel
> refdate
> > > version deltrig instrig updtrig seltrig category cache
> > > vLostSaleCount 830626002 V 1 2 1610612736 16 0 0 2004-05-03
> 13:19:14.993 0
> > > 16 0 V 0 2 0 2004-05-03 13:19:14.993 0 0 0 0 0 0 0
> > >
> > > (1 row(s) affected)
> > >
> > > What is going on here? TIA!
> > >
> > >
> >
>
>|||Note "*indexed* view". Do you have an index on that view?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in message
news:%23I6bXxVPGHA.2236@.TK2MSFTNGP15.phx.gbl...
> Thanks! I'm taking your word for it, but that seems to be in conflict with
> what BOL says. Is BOL in error? Here is DBCC CHECKTABLE from BOL:
> DBCC CHECKTABLE
> Checks the integrity of the data, index, text, ntext, and image pages for
> the specified table or indexed view.
> Syntax
> DBCC CHECKTABLE
> ( 'table_name' | 'view_name'
> [ , NOINDEX
> | index_id
> | { REPAIR_ALLOW_DATA_LOSS
> | REPAIR_FAST
> | REPAIR_REBUILD }
> ]
> ) [ WITH { [ ALL_ERRORMSGS | NO_INFOMSGS ]
> [ , [ TABLOCK ] ]
> [ , [ ESTIMATEONLY ] ]
> [ , [ PHYSICAL_ONLY ] ]
> }
> ]
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:uMAHDuVPGHA.648@.TK2MSFTNGP14.phx.gbl...
>> CHECKTABLE is checking physical consistency of the data. A view doesn't
> store any data so there is
>> nothing to check. Run CHECKTABLE on the tables that the view is querying
> instead.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in
> message
>> news:OK8fPsVPGHA.812@.TK2MSFTNGP10.phx.gbl...
>> > I'm trying to run the following statement:
>> >
>> > DBCC CHECKTABLE([vLostSaleCount], REPAIR_REBUILD) WITH NO_INFOMSGS
>> >
>> > But I get this error message:
>> >
>> > Server: Msg 2501, Level 16, State 1, Line 1
>> > Could not find a table or object named 'vLostSaleCount'. Check
> sysobjects.
>> >
>> > So I run this statement:
>> >
>> > SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'vLostSaleCount')
>> >
>> > And get this result:
>> >
>> > name id xtype uid info status base_schema_ver replinfo parent_obj crdate
>> > ftcatid schema_ver stats_schema_ver type userstat sysstat indexdel
> refdate
>> > version deltrig instrig updtrig seltrig category cache
>> > vLostSaleCount 830626002 V 1 2 1610612736 16 0 0 2004-05-03
> 13:19:14.993 0
>> > 16 0 V 0 2 0 2004-05-03 13:19:14.993 0 0 0 0 0 0 0
>> >
>> > (1 row(s) affected)
>> >
>> > What is going on here? TIA!
>> >
>> >
>|||I see. Thanks guys!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uA5Qo$VPGHA.720@.TK2MSFTNGP14.phx.gbl...
> Note "*indexed* view". Do you have an index on that view?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in
message
> news:%23I6bXxVPGHA.2236@.TK2MSFTNGP15.phx.gbl...
> > Thanks! I'm taking your word for it, but that seems to be in conflict
with
> > what BOL says. Is BOL in error? Here is DBCC CHECKTABLE from BOL:
> >
> > DBCC CHECKTABLE
> > Checks the integrity of the data, index, text, ntext, and image pages
for
> > the specified table or indexed view.
> >
> > Syntax
> > DBCC CHECKTABLE
> > ( 'table_name' | 'view_name'
> > [ , NOINDEX
> > | index_id
> > | { REPAIR_ALLOW_DATA_LOSS
> > | REPAIR_FAST
> > | REPAIR_REBUILD }
> > ]
> > ) [ WITH { [ ALL_ERRORMSGS | NO_INFOMSGS ]
> > [ , [ TABLOCK ] ]
> > [ , [ ESTIMATEONLY ] ]
> > [ , [ PHYSICAL_ONLY ] ]
> > }
> > ]
> >
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:uMAHDuVPGHA.648@.TK2MSFTNGP14.phx.gbl...
> >> CHECKTABLE is checking physical consistency of the data. A view doesn't
> > store any data so there is
> >> nothing to check. Run CHECKTABLE on the tables that the view is
querying
> > instead.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in
> > message
> >> news:OK8fPsVPGHA.812@.TK2MSFTNGP10.phx.gbl...
> >> > I'm trying to run the following statement:
> >> >
> >> > DBCC CHECKTABLE([vLostSaleCount], REPAIR_REBUILD) WITH NO_INFOMSGS
> >> >
> >> > But I get this error message:
> >> >
> >> > Server: Msg 2501, Level 16, State 1, Line 1
> >> > Could not find a table or object named 'vLostSaleCount'. Check
> > sysobjects.
> >> >
> >> > So I run this statement:
> >> >
> >> > SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'vLostSaleCount')
> >> >
> >> > And get this result:
> >> >
> >> > name id xtype uid info status base_schema_ver replinfo parent_obj
crdate
> >> > ftcatid schema_ver stats_schema_ver type userstat sysstat indexdel
> > refdate
> >> > version deltrig instrig updtrig seltrig category cache
> >> > vLostSaleCount 830626002 V 1 2 1610612736 16 0 0 2004-05-03
> > 13:19:14.993 0
> >> > 16 0 V 0 2 0 2004-05-03 13:19:14.993 0 0 0 0 0 0 0
> >> >
> >> > (1 row(s) affected)
> >> >
> >> > What is going on here? TIA!
> >> >
> >> >
> >>
> >
> >
>

DBCC CHECKTABLE on a View

Sunday, February 19, 2012

DBCC CHECKDB Single User Mode

Does the database have to be in Single User Mode to execute the
statement listed below?
DBCC CHECKDB ('Database',Repair_Rebuild) WITH ALL_ERRORMSGS
Thanks,> Does the database have to be in Single User Mode to execute the
> statement listed below?
Based on the error message, I'd say yes'?
Server: Msg 7919, Level 16, State 2, Line 1
Repair statement not processed. Database needs to be in single user mode.|||Yes it does. Before fixing any errors that checkdb has reported, you should
work out whay the errors occured (check the errorlog and event logs for
hardware messages for instance)
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"AB - MVP" <ten.xoc@.dnartreb.noraa> wrote in message
news:OhQXkCNUFHA.3436@.TK2MSFTNGP09.phx.gbl...
> Based on the error message, I'd say yes'?
> Server: Msg 7919, Level 16, State 2, Line 1
> Repair statement not processed. Database needs to be in single user mode.
>

DBCC CHECKDB Single User Mode

Does the database have to be in Single User Mode to execute the
statement listed below?
DBCC CHECKDB ('Database',Repair_Rebuild) WITH ALL_ERRORMSGS
Thanks,
> Does the database have to be in Single User Mode to execute the
> statement listed below?
Based on the error message, I'd say yes??
Server: Msg 7919, Level 16, State 2, Line 1
Repair statement not processed. Database needs to be in single user mode.
|||Yes it does. Before fixing any errors that checkdb has reported, you should
work out whay the errors occured (check the errorlog and event logs for
hardware messages for instance)
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"AB - MVP" <ten.xoc@.dnartreb.noraa> wrote in message
news:OhQXkCNUFHA.3436@.TK2MSFTNGP09.phx.gbl...
> Based on the error message, I'd say yes??
> Server: Msg 7919, Level 16, State 2, Line 1
> Repair statement not processed. Database needs to be in single user mode.
>

DBCC CHECKDB Single User Mode

Does the database have to be in Single User Mode to execute the
statement listed below?
DBCC CHECKDB ('Database',Repair_Rebuild) WITH ALL_ERRORMSGS
Thanks,> Does the database have to be in Single User Mode to execute the
> statement listed below?
Based on the error message, I'd say yes'?
Server: Msg 7919, Level 16, State 2, Line 1
Repair statement not processed. Database needs to be in single user mode.|||Yes it does. Before fixing any errors that checkdb has reported, you should
work out whay the errors occured (check the errorlog and event logs for
hardware messages for instance)
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"AB - MVP" <ten.xoc@.dnartreb.noraa> wrote in message
news:OhQXkCNUFHA.3436@.TK2MSFTNGP09.phx.gbl...
> > Does the database have to be in Single User Mode to execute the
> > statement listed below?
> Based on the error message, I'd say yes'?
> Server: Msg 7919, Level 16, State 2, Line 1
> Repair statement not processed. Database needs to be in single user mode.
>

DBCC CheckDB Repair_Rebuild caused data loss.

Hi,
I tried Repair_Rebuild option in 7.0 . I was thinking that the following DBC
C command would not cause any data loss, which also noted in T-SQL Reference
s:
DBCC CHECKDB (dadabase name, REPAIR_FAST)
I lost 285 rows in a table that has total 103 thousand rows. (Thanks to back
ups, I was able to get those rows back.) Is data loss possible while using R
epair_rebuild option?
I got following messages:
...
Server: Msg 8993, Level 16, State 1, Line 0
Object ID 1656497080, forwarding row page (1:27122), slot 9 points to page (
1:242059), slot 14. Did not encounter forwarded row. Possible allocation err
or.
(I believe 285 times.)
...
The error has been repaired.
(I believe 285 times.)
...
Repair: Deleted record for object ID 1656497080, index ID 0, on page (1:2712
2), slot 9. Indexes will be rebuilt.
(I believe 285 times.)
...
CHECKDB fixed 0 allocation errors and 285 consistency errors in table 'table
name'. (object ID 1656497080).
...
CHECKDB fixed 0 allocation errors and 285 consistency errors in database 'da
tabasename'.
...
Thanks,
Akin HocaI tried following DBCC command: DBCC CHECKDB (dadabase name, Repair_Rebuild)
not DBCC CHECKDB (dadabase name, REPAIR_FAST)
Thanks,
Akin
-- Akin wrote: --
Hi,
I tried Repair_Rebuild option in 7.0 . I was thinking that the following DBC
C command would not cause any data loss, which also noted in T-SQL Reference
s:
DBCC CHECKDB (dadabase name, REPAIR_FAST)
I lost 285 rows in a table that has total 103 thousand rows. (Thanks to back
ups, I was able to get those rows back.) Is data loss possible while using R
epair_rebuild option?
I got following messages:
..
Server: Msg 8993, Level 16, State 1, Line 0
Object ID 1656497080, forwarding row page (1:27122), slot 9 points to page (
1:242059), slot 14. Did not encounter forwarded row. Possible allocation err
or.
(I believe 285 times.)
..
The error has been repaired.
(I believe 285 times.)
..
Repair: Deleted record for object ID 1656497080, index ID 0, on page (1:2712
2), slot 9. Indexes will be rebuilt.
(I believe 285 times.)
..
CHECKDB fixed 0 allocation errors and 285 consistency errors in table 'table
name'. (object ID 1656497080).
..
CHECKDB fixed 0 allocation errors and 285 consistency errors in database 'da
tabasename'.
..
Thanks,
Akin Hoca|||You didn't lose any data as part of the repair. What happened was that you
had 285 forwarding records point to non-existent records.
When a heap record is updated such that it grows and there is no space for
the record to expand, it is moved to a new location in the heap. This new
location is called a forwarded record. The old location then contains a
pointer to the new location. The pointer in the old location is called a
forwarding record. Non-clustered indexes on heaps refer back to the heap
records using a physical record identifier (i.e. the file/page/slot of the
record). Using the forwarded row mechanism means that updates to heap
records do not cause updates to non-clustered indexes on the heap. It's an
optimization mechanism.
So, your heap had 285 of the pointer records in that did not point to
updated copies of the original record. These were deleted by repair and this
is allowed under repair_rebuild, because no data is being lost. The
non-clustered indexes were rebuilt because they would have referenced the
285 pointer records, and so must be rebuilt to remove those references.
Please let me know if that did not make sense.
Now, the more interesting question is how did this situation arise in the
first place? That I do not know and you may want to call PSS for assistance.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Akin" <anonymous@.discussions.microsoft.com> wrote in message
news:7E85386A-0102-4B30-8BF1-BB123342C065@.microsoft.com...
> I tried following DBCC command: DBCC CHECKDB (dadabase name,
Repair_Rebuild)
> not DBCC CHECKDB (dadabase name, REPAIR_FAST)
> Thanks,
> Akin
>
> -- Akin wrote: --
> Hi,
> I tried Repair_Rebuild option in 7.0 . I was thinking that the
following DBCC command would not cause any data loss, which also noted in
T-SQL References:
> DBCC CHECKDB (dadabase name, REPAIR_FAST)
> I lost 285 rows in a table that has total 103 thousand rows. (Thanks
to backups, I was able to get those rows back.) Is data loss possible while
using Repair_rebuild option?
> I got following messages:
> ...
> Server: Msg 8993, Level 16, State 1, Line 0
> Object ID 1656497080, forwarding row page (1:27122), slot 9 points to
page (1:242059), slot 14. Did not encounter forwarded row. Possible
allocation error.
> (I believe 285 times.)
> ...
> The error has been repaired.
> (I believe 285 times.)
> ...
> Repair: Deleted record for object ID 1656497080, index ID 0, on page
(1:27122), slot 9. Indexes will be rebuilt.
> (I believe 285 times.)
> ...
> CHECKDB fixed 0 allocation errors and 285 consistency errors in table
'tablename'. (object ID 1656497080).
> ...
> CHECKDB fixed 0 allocation errors and 285 consistency errors in
database 'databasename'.
> ...
> Thanks,
> Akin Hoca

DBCC CheckDB Repair_Rebuild caused data loss.

Hi,
I tried Repair_Rebuild option in 7.0 . I was thinking that the following DBCC command would not cause any data loss, which also noted in T-SQL References:
DBCC CHECKDB (dadabase name, REPAIR_FAST)
I lost 285 rows in a table that has total 103 thousand rows. (Thanks to backups, I was able to get those rows back.) Is data loss possible while using Repair_rebuild option?
I got following messages:
...
Server: Msg 8993, Level 16, State 1, Line 0
Object ID 1656497080, forwarding row page (1:27122), slot 9 points to page (1:242059), slot 14. Did not encounter forwarded row. Possible allocation error.
(I believe 285 times.)
...
The error has been repaired.
(I believe 285 times.)
...
Repair: Deleted record for object ID 1656497080, index ID 0, on page (1:27122), slot 9. Indexes will be rebuilt.
(I believe 285 times.)
...
CHECKDB fixed 0 allocation errors and 285 consistency errors in table 'tablename'. (object ID 1656497080).
...
CHECKDB fixed 0 allocation errors and 285 consistency errors in database 'databasename'.
...
Thanks,
Akin Hoca
I tried following DBCC command: DBCC CHECKDB (dadabase name, Repair_Rebuild)
not DBCC CHECKDB (dadabase name, REPAIR_FAST)
Thanks,
Akin
-- Akin wrote: --
Hi,
I tried Repair_Rebuild option in 7.0 . I was thinking that the following DBCC command would not cause any data loss, which also noted in T-SQL References:
DBCC CHECKDB (dadabase name, REPAIR_FAST)
I lost 285 rows in a table that has total 103 thousand rows. (Thanks to backups, I was able to get those rows back.) Is data loss possible while using Repair_rebuild option?
I got following messages:
...
Server: Msg 8993, Level 16, State 1, Line 0
Object ID 1656497080, forwarding row page (1:27122), slot 9 points to page (1:242059), slot 14. Did not encounter forwarded row. Possible allocation error.
(I believe 285 times.)
...
The error has been repaired.
(I believe 285 times.)
...
Repair: Deleted record for object ID 1656497080, index ID 0, on page (1:27122), slot 9. Indexes will be rebuilt.
(I believe 285 times.)
...
CHECKDB fixed 0 allocation errors and 285 consistency errors in table 'tablename'. (object ID 1656497080).
...
CHECKDB fixed 0 allocation errors and 285 consistency errors in database 'databasename'.
...
Thanks,
Akin Hoca
|||You didn't lose any data as part of the repair. What happened was that you
had 285 forwarding records point to non-existent records.
When a heap record is updated such that it grows and there is no space for
the record to expand, it is moved to a new location in the heap. This new
location is called a forwarded record. The old location then contains a
pointer to the new location. The pointer in the old location is called a
forwarding record. Non-clustered indexes on heaps refer back to the heap
records using a physical record identifier (i.e. the file/page/slot of the
record). Using the forwarded row mechanism means that updates to heap
records do not cause updates to non-clustered indexes on the heap. It's an
optimization mechanism.
So, your heap had 285 of the pointer records in that did not point to
updated copies of the original record. These were deleted by repair and this
is allowed under repair_rebuild, because no data is being lost. The
non-clustered indexes were rebuilt because they would have referenced the
285 pointer records, and so must be rebuilt to remove those references.
Please let me know if that did not make sense.
Now, the more interesting question is how did this situation arise in the
first place? That I do not know and you may want to call PSS for assistance.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Akin" <anonymous@.discussions.microsoft.com> wrote in message
news:7E85386A-0102-4B30-8BF1-BB123342C065@.microsoft.com...
> I tried following DBCC command: DBCC CHECKDB (dadabase name,
Repair_Rebuild)
> not DBCC CHECKDB (dadabase name, REPAIR_FAST)
> Thanks,
> Akin
>
> -- Akin wrote: --
> Hi,
> I tried Repair_Rebuild option in 7.0 . I was thinking that the
following DBCC command would not cause any data loss, which also noted in
T-SQL References:
> DBCC CHECKDB (dadabase name, REPAIR_FAST)
> I lost 285 rows in a table that has total 103 thousand rows. (Thanks
to backups, I was able to get those rows back.) Is data loss possible while
using Repair_rebuild option?
> I got following messages:
> ...
> Server: Msg 8993, Level 16, State 1, Line 0
> Object ID 1656497080, forwarding row page (1:27122), slot 9 points to
page (1:242059), slot 14. Did not encounter forwarded row. Possible
allocation error.
> (I believe 285 times.)
> ...
> The error has been repaired.
> (I believe 285 times.)
> ...
> Repair: Deleted record for object ID 1656497080, index ID 0, on page
(1:27122), slot 9. Indexes will be rebuilt.
> (I believe 285 times.)
> ...
> CHECKDB fixed 0 allocation errors and 285 consistency errors in table
'tablename'. (object ID 1656497080).
> ...
> CHECKDB fixed 0 allocation errors and 285 consistency errors in
database 'databasename'.
> ...
> Thanks,
> Akin Hoca

Friday, February 24, 2012

DBCC CHECKTABLE with REPAIR_REBUILD

DBCC CHECKTABLE on a View

DBCC CHECKTABLE on a View

DBCC CHECKTABLE on a View

Sunday, February 19, 2012

DBCC CHECKDB Single User Mode

DBCC CHECKDB Single User Mode

DBCC CHECKDB Single User Mode

DBCC CheckDB Repair_Rebuild caused data loss.

DBCC CheckDB Repair_Rebuild caused data loss.

DBfile usage

Blog Archive

About Me