I want to condense several rows of a table using a select
statement, function, or stored procedure
For Example
mytable :
1 a
2 b
2 c
2 d
3 a
so that my select should result in
1 a
2 b,c,d
3 a
Any ideas or suggestions?
While this code references the sample database in my book (CHA2), the
pattern will work for your problem as well. In this example, the @.EventDates
variable is used to gather, or denomalize, the EventDate column:
USE CHA2
DECLARE
@.EventDates VARCHAR(1024)
SET @.EventDates = ''
SELECT @.EventDates = @.EventDates + CONVERT(VARCHAR(15), a.d,107 ) + '; '
FROM (Select DateBegin as [d] FROM Event
JOIN Tour
ON Event.TourID = Tour.TourID
WHERE Tour.[Name] = 'Outer Banks Lighthouses') as a
SELECT Left(@.EventDates, Len(@.EventDates)-1) AS 'Outer Banks Lighthouses
Events'
-Paul Nielsen, SQL Server MVP
SQL Server 2000 Bible, Wiley Press
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:392801c4aa4b$477a6810$a401280a@.phx.gbl...
>I want to condense several rows of a table using a select
> statement, function, or stored procedure
> For Example
> mytable :
> 1 a
> 2 b
> 2 c
> 2 d
> 3 a
> so that my select should result in
> 1 a
> 2 b,c,d
> 3 a
> Any ideas or suggestions?
Showing posts with label table. Show all posts
Showing posts with label table. Show all posts
Thursday, March 29, 2012
Consoldate columns in a SELECT
I have a vendor supplied orders table that tracks the fee types associated
with an order. The allowable fee types are A, B, C, D, and E. These fee
types can be in anyone of the six fee code fields. In the corresponding fee
amount field is the fee amount. The application allows NULL fee amounts even
when there is a corresponding fee code entered. See OrderID 7 where fee code
'E' in field FEE_6_CD has a NULL value in FEE_6_AMT.
For the purposes of my SELECT I want to consolidate and sum all fee types
into 2 master types:
AB_Consolidation-All A and B fee types
CDE_Consolidation--All C, D, and E fee types.
See CREATE TABLE and INSERT statements as well as expected results below.
Thanks to anyone who could help.
CREATE TABLE [dbo].[Orders] (
[OrderID] [smallint],
[OrderType] [char] (4),
[OrderDate] [Datetime],
[FEE_1_CD] [char] (1)NULL,
[FEE_2_CD] [char] (1)NULL,
[FEE_3_CD] [char] (1)NULL,
[FEE_4_CD] [char] (1)NULL,
[FEE_5_CD] [char] (1)NULL,
[FEE_6_CD] [char] (1)NULL,
[FEE_1_AMT] [decimal](18, 2) NULL,
[FEE_2_AMT] [decimal](18, 2) NULL,
[FEE_3_AMT] [decimal](18, 2) NULL,
[FEE_4_AMT] [decimal](18, 2) NULL,
[FEE_5_AMT] [decimal](18, 2) NULL,
[FEE_6_AMT] [decimal](18, 2) NULL
) ON [PRIMARY]
INSERT INTO ORDERS
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE
E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
VALUES
(1,'Buy','20060117','A','C',NULL,NULL,NU
LL,NULL,8,9,NULL,NULL,NULL,NULL)
INSERT INTO ORDERS
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE
E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
VALUES
(2,'Buy','20060117',NULL,NULL,'D',NULL,N
ULL,NULL,NULL,NULL,11,NULL,NULL,NULL
)
INSERT INTO ORDERS
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE
E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
VALUES
(3,'Sell','20060117',NULL,NULL,NULL,NULL
,NULL,'B',NULL,NULL,NULL,NULL,NULL,1
5)
INSERT INTO ORDERS
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE
E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
VALUES
(4,'Buy','20060116','A',NULL,NULL,'B',NU
LL,NULL,18,NUll,NULL,85,NULL,NULL)
INSERT INTO ORDERS
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE
E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
VALUES
(5,'Sell','20060117',NULL,NULL,NULL,NULL
,NULL,'D',NULL,NULL,NULL,NULL,NULL,5
4)
INSERT INTO ORDERS
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE
E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
VALUES (6,'Buy','20060116','A','C','D','B',NULL
,NULL,8,9,11,12,NULL,NULL)
INSERT INTO ORDERS
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE
E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
VALUES (7,'Buy','20060117','A','C',NULL,NULL,'D
','E',3,99,NULL,NULL,14,NULL)
Expect Results:
OrderID,OrderType,OrderDate,ABConsolidat
ion,DEConsolidation
1,Buy,20060117,8,9
2,Buy,20060117,0,11
3,Sell,20060117,15,0
4,Buy,20060116,103,0
5,Sell,20060117,0,54
6,Buy,20060116,20,20
7,Buy,20060117,3,113
Partial SELECT statement
SELECT
OrderID,
OrderType,
OrderDate,
Perform calcs here AS AB_Consolidation,
Perform calcs here AS CDE_Consolidation
FROM ORDERSAs you might have noticed, the vendor table schema is mediocre. In any case,
you can use a derived table to transpose the data & get the results you
need. If this table is being used in multiple areas, a view might be better.
You can create one as:
ALTER VIEW vw ( order_id, order_type, order_date, seq, code, amount )
AS SELECT OrderID, OrderType, OrderDate, seq,
CASE seq WHEN 1 THEN FEE_1_CD
WHEN 2 THEN FEE_2_CD
WHEN 3 THEN FEE_3_CD
WHEN 4 THEN FEE_4_CD
WHEN 5 THEN FEE_5_CD
WHEN 6 THEN FEE_6_CD
END,
CASE seq WHEN 1 THEN FEE_1_AMT
WHEN 2 THEN FEE_2_AMT
WHEN 3 THEN FEE_3_AMT
WHEN 4 THEN FEE_4_AMT
WHEN 5 THEN FEE_5_AMT
WHEN 6 THEN FEE_6_AMT
END
FROM Orders, ( SELECT 1 UNION
SELECT 2 UNION
SELECT 3 UNION
SELECT 4 UNION
SELECT 5 UNION
SELECT 6) S ( seq )
GO
Now you can do the query for consolidation like:
SELECT order_id, order_type, order_date,
SUM( CASE WHEN code IN ( 'A', 'B' )
THEN Amt ELSE 0 END ) AS "AB_",
SUM( CASE WHEN code IN ( 'C', 'D', 'E' )
THEN Amt ELSE 0 END ) AS "CDE_"
FROM vw
GROUP BY order_id, order_type, order_date,
ORDER BY order_id ;
Anith|||cant you just do this, or am I missing something?
SELECT
OrderID,
OrderType,
OrderDate,
coalesce(FEE_1_AMT,0)+coalesce(FEE_2_AMT
,0) AS AB_Consolidation,
coalesce(FEE_3_AMT,0) + coalesce(FEE_4_AMT,0) + coalesce(FEE_5_AMT,0) AS
CDE_Consolidation
FROM ORDERS
Basically, just add the collumns using coaless to handle the nulls.
"Terri" <terri@.cybernets.com> wrote in message
news:dqovlt$v0d$2@.reader2.nmix.net...
> I have a vendor supplied orders table that tracks the fee types associated
> with an order. The allowable fee types are A, B, C, D, and E. These fee
> types can be in anyone of the six fee code fields. In the corresponding
fee
> amount field is the fee amount. The application allows NULL fee amounts
even
> when there is a corresponding fee code entered. See OrderID 7 where fee
code
> 'E' in field FEE_6_CD has a NULL value in FEE_6_AMT.
> For the purposes of my SELECT I want to consolidate and sum all fee types
> into 2 master types:
> AB_Consolidation-All A and B fee types
> CDE_Consolidation--All C, D, and E fee types.
> See CREATE TABLE and INSERT statements as well as expected results below.
> Thanks to anyone who could help.
>
> CREATE TABLE [dbo].[Orders] (
> [OrderID] [smallint],
> [OrderType] [char] (4),
> [OrderDate] [Datetime],
> [FEE_1_CD] [char] (1)NULL,
> [FEE_2_CD] [char] (1)NULL,
> [FEE_3_CD] [char] (1)NULL,
> [FEE_4_CD] [char] (1)NULL,
> [FEE_5_CD] [char] (1)NULL,
> [FEE_6_CD] [char] (1)NULL,
> [FEE_1_AMT] [decimal](18, 2) NULL,
> [FEE_2_AMT] [decimal](18, 2) NULL,
> [FEE_3_AMT] [decimal](18, 2) NULL,
> [FEE_4_AMT] [decimal](18, 2) NULL,
> [FEE_5_AMT] [decimal](18, 2) NULL,
> [FEE_6_AMT] [decimal](18, 2) NULL
> ) ON [PRIMARY]
>
> INSERT INTO ORDERS
>
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE[col
or=darkred]
> E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
> VALUES
> (1,'Buy','20060117','A','C',NULL,NULL,NU
LL,NULL,8,9,NULL,NULL,NULL,NULL)
> INSERT INTO ORDERS
>[/color]
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE[col
or=darkred]
> E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
> VALUES
>[/color]
(2,'Buy','20060117',NULL,NULL,'D',NULL,N
ULL,NULL,NULL,NULL,11,NULL,NULL,NULL[col
or=darkred]
> )
> INSERT INTO ORDERS
>[/color]
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE[col
or=darkred]
> E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
> VALUES
>[/color]
(3,'Sell','20060117',NULL,NULL,NULL,NULL
,NULL,'B',NULL,NULL,NULL,NULL,NULL,1[col
or=darkred]
> 5)
> INSERT INTO ORDERS
>[/color]
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE[col
or=darkred]
> E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
> VALUES
> (4,'Buy','20060116','A',NULL,NULL,'B',NU
LL,NULL,18,NUll,NULL,85,NULL,NULL)
> INSERT INTO ORDERS
>[/color]
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE[col
or=darkred]
> E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
> VALUES
>[/color]
(5,'Sell','20060117',NULL,NULL,NULL,NULL
,NULL,'D',NULL,NULL,NULL,NULL,NULL,5[col
or=darkred]
> 4)
> INSERT INTO ORDERS
>[/color]
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE[col
or=darkred]
> E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
> VALUES (6,'Buy','20060116','A','C','D','B',NULL
,NULL,8,9,11,12,NULL,NULL)
> INSERT INTO ORDERS
>[/color]
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE[col
or=darkred]
> E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
> VALUES[/color]
(7,'Buy','20060117','A','C',NULL,NULL,'D
','E',3,99,NULL,NULL,14,NULL)red">
>
> Expect Results:
> OrderID,OrderType,OrderDate,ABConsolidat
ion,DEConsolidation
> 1,Buy,20060117,8,9
> 2,Buy,20060117,0,11
> 3,Sell,20060117,15,0
> 4,Buy,20060116,103,0
> 5,Sell,20060117,0,54
> 6,Buy,20060116,20,20
> 7,Buy,20060117,3,113
> Partial SELECT statement
> SELECT
> OrderID,
> OrderType,
> OrderDate,
> Perform calcs here AS AB_Consolidation,
> Perform calcs here AS CDE_Consolidation
> FROM ORDERS
>
>|||"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:OPVJGPUHGHA.2668@.tk2msftngp13.phx.gbl...
> cant you just do this, or am I missing something?
> SELECT
> OrderID,
> OrderType,
> OrderDate,
> coalesce(FEE_1_AMT,0)+coalesce(FEE_2_AMT
,0) AS AB_Consolidation,
> coalesce(FEE_3_AMT,0) + coalesce(FEE_4_AMT,0) + coalesce(FEE_5_AMT,0) AS
> CDE_Consolidation
> FROM ORDERS
> Basically, just add the collumns using coaless to handle the nulls.
Fee type A can be in any of the 6 fee fields so I don't think that will
work. I'm looking at Anith's solution to see if that will work.
thanks|||>
> Fee type A can be in any of the 6 fee fields so I don't think that will
> work. I'm looking at Anith's solution to see if that will work.
>
Ahhh... I misunderstood that in your first post. The problem is much more
interesting than I thought.
I like how Anith is getting 6 fields from one row to 6. I'll have to
remember this join technique in case I ever run into a similar situation.
If your final code differs from his, please let us know what you come up
with.
with an order. The allowable fee types are A, B, C, D, and E. These fee
types can be in anyone of the six fee code fields. In the corresponding fee
amount field is the fee amount. The application allows NULL fee amounts even
when there is a corresponding fee code entered. See OrderID 7 where fee code
'E' in field FEE_6_CD has a NULL value in FEE_6_AMT.
For the purposes of my SELECT I want to consolidate and sum all fee types
into 2 master types:
AB_Consolidation-All A and B fee types
CDE_Consolidation--All C, D, and E fee types.
See CREATE TABLE and INSERT statements as well as expected results below.
Thanks to anyone who could help.
CREATE TABLE [dbo].[Orders] (
[OrderID] [smallint],
[OrderType] [char] (4),
[OrderDate] [Datetime],
[FEE_1_CD] [char] (1)NULL,
[FEE_2_CD] [char] (1)NULL,
[FEE_3_CD] [char] (1)NULL,
[FEE_4_CD] [char] (1)NULL,
[FEE_5_CD] [char] (1)NULL,
[FEE_6_CD] [char] (1)NULL,
[FEE_1_AMT] [decimal](18, 2) NULL,
[FEE_2_AMT] [decimal](18, 2) NULL,
[FEE_3_AMT] [decimal](18, 2) NULL,
[FEE_4_AMT] [decimal](18, 2) NULL,
[FEE_5_AMT] [decimal](18, 2) NULL,
[FEE_6_AMT] [decimal](18, 2) NULL
) ON [PRIMARY]
INSERT INTO ORDERS
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE
E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
VALUES
(1,'Buy','20060117','A','C',NULL,NULL,NU
LL,NULL,8,9,NULL,NULL,NULL,NULL)
INSERT INTO ORDERS
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE
E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
VALUES
(2,'Buy','20060117',NULL,NULL,'D',NULL,N
ULL,NULL,NULL,NULL,11,NULL,NULL,NULL
)
INSERT INTO ORDERS
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE
E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
VALUES
(3,'Sell','20060117',NULL,NULL,NULL,NULL
,NULL,'B',NULL,NULL,NULL,NULL,NULL,1
5)
INSERT INTO ORDERS
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE
E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
VALUES
(4,'Buy','20060116','A',NULL,NULL,'B',NU
LL,NULL,18,NUll,NULL,85,NULL,NULL)
INSERT INTO ORDERS
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE
E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
VALUES
(5,'Sell','20060117',NULL,NULL,NULL,NULL
,NULL,'D',NULL,NULL,NULL,NULL,NULL,5
4)
INSERT INTO ORDERS
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE
E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
VALUES (6,'Buy','20060116','A','C','D','B',NULL
,NULL,8,9,11,12,NULL,NULL)
INSERT INTO ORDERS
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE
E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
VALUES (7,'Buy','20060117','A','C',NULL,NULL,'D
','E',3,99,NULL,NULL,14,NULL)
Expect Results:
OrderID,OrderType,OrderDate,ABConsolidat
ion,DEConsolidation
1,Buy,20060117,8,9
2,Buy,20060117,0,11
3,Sell,20060117,15,0
4,Buy,20060116,103,0
5,Sell,20060117,0,54
6,Buy,20060116,20,20
7,Buy,20060117,3,113
Partial SELECT statement
SELECT
OrderID,
OrderType,
OrderDate,
Perform calcs here AS AB_Consolidation,
Perform calcs here AS CDE_Consolidation
FROM ORDERSAs you might have noticed, the vendor table schema is mediocre. In any case,
you can use a derived table to transpose the data & get the results you
need. If this table is being used in multiple areas, a view might be better.
You can create one as:
ALTER VIEW vw ( order_id, order_type, order_date, seq, code, amount )
AS SELECT OrderID, OrderType, OrderDate, seq,
CASE seq WHEN 1 THEN FEE_1_CD
WHEN 2 THEN FEE_2_CD
WHEN 3 THEN FEE_3_CD
WHEN 4 THEN FEE_4_CD
WHEN 5 THEN FEE_5_CD
WHEN 6 THEN FEE_6_CD
END,
CASE seq WHEN 1 THEN FEE_1_AMT
WHEN 2 THEN FEE_2_AMT
WHEN 3 THEN FEE_3_AMT
WHEN 4 THEN FEE_4_AMT
WHEN 5 THEN FEE_5_AMT
WHEN 6 THEN FEE_6_AMT
END
FROM Orders, ( SELECT 1 UNION
SELECT 2 UNION
SELECT 3 UNION
SELECT 4 UNION
SELECT 5 UNION
SELECT 6) S ( seq )
GO
Now you can do the query for consolidation like:
SELECT order_id, order_type, order_date,
SUM( CASE WHEN code IN ( 'A', 'B' )
THEN Amt ELSE 0 END ) AS "AB_",
SUM( CASE WHEN code IN ( 'C', 'D', 'E' )
THEN Amt ELSE 0 END ) AS "CDE_"
FROM vw
GROUP BY order_id, order_type, order_date,
ORDER BY order_id ;
Anith|||cant you just do this, or am I missing something?
SELECT
OrderID,
OrderType,
OrderDate,
coalesce(FEE_1_AMT,0)+coalesce(FEE_2_AMT
,0) AS AB_Consolidation,
coalesce(FEE_3_AMT,0) + coalesce(FEE_4_AMT,0) + coalesce(FEE_5_AMT,0) AS
CDE_Consolidation
FROM ORDERS
Basically, just add the collumns using coaless to handle the nulls.
"Terri" <terri@.cybernets.com> wrote in message
news:dqovlt$v0d$2@.reader2.nmix.net...
> I have a vendor supplied orders table that tracks the fee types associated
> with an order. The allowable fee types are A, B, C, D, and E. These fee
> types can be in anyone of the six fee code fields. In the corresponding
fee
> amount field is the fee amount. The application allows NULL fee amounts
even
> when there is a corresponding fee code entered. See OrderID 7 where fee
code
> 'E' in field FEE_6_CD has a NULL value in FEE_6_AMT.
> For the purposes of my SELECT I want to consolidate and sum all fee types
> into 2 master types:
> AB_Consolidation-All A and B fee types
> CDE_Consolidation--All C, D, and E fee types.
> See CREATE TABLE and INSERT statements as well as expected results below.
> Thanks to anyone who could help.
>
> CREATE TABLE [dbo].[Orders] (
> [OrderID] [smallint],
> [OrderType] [char] (4),
> [OrderDate] [Datetime],
> [FEE_1_CD] [char] (1)NULL,
> [FEE_2_CD] [char] (1)NULL,
> [FEE_3_CD] [char] (1)NULL,
> [FEE_4_CD] [char] (1)NULL,
> [FEE_5_CD] [char] (1)NULL,
> [FEE_6_CD] [char] (1)NULL,
> [FEE_1_AMT] [decimal](18, 2) NULL,
> [FEE_2_AMT] [decimal](18, 2) NULL,
> [FEE_3_AMT] [decimal](18, 2) NULL,
> [FEE_4_AMT] [decimal](18, 2) NULL,
> [FEE_5_AMT] [decimal](18, 2) NULL,
> [FEE_6_AMT] [decimal](18, 2) NULL
> ) ON [PRIMARY]
>
> INSERT INTO ORDERS
>
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE[col
or=darkred]
> E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
> VALUES
> (1,'Buy','20060117','A','C',NULL,NULL,NU
LL,NULL,8,9,NULL,NULL,NULL,NULL)
> INSERT INTO ORDERS
>[/color]
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE[col
or=darkred]
> E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
> VALUES
>[/color]
(2,'Buy','20060117',NULL,NULL,'D',NULL,N
ULL,NULL,NULL,NULL,11,NULL,NULL,NULL[col
or=darkred]
> )
> INSERT INTO ORDERS
>[/color]
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE[col
or=darkred]
> E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
> VALUES
>[/color]
(3,'Sell','20060117',NULL,NULL,NULL,NULL
,NULL,'B',NULL,NULL,NULL,NULL,NULL,1[col
or=darkred]
> 5)
> INSERT INTO ORDERS
>[/color]
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE[col
or=darkred]
> E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
> VALUES
> (4,'Buy','20060116','A',NULL,NULL,'B',NU
LL,NULL,18,NUll,NULL,85,NULL,NULL)
> INSERT INTO ORDERS
>[/color]
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE[col
or=darkred]
> E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
> VALUES
>[/color]
(5,'Sell','20060117',NULL,NULL,NULL,NULL
,NULL,'D',NULL,NULL,NULL,NULL,NULL,5[col
or=darkred]
> 4)
> INSERT INTO ORDERS
>[/color]
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE[col
or=darkred]
> E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
> VALUES (6,'Buy','20060116','A','C','D','B',NULL
,NULL,8,9,11,12,NULL,NULL)
> INSERT INTO ORDERS
>[/color]
(OrderID,OrderType,OrderDate,FEE_1_CD,FE
E_2_CD,FEE_3_CD,FEE_4_CD,FEE_5_CD,FE[col
or=darkred]
> E_6_CD,FEE_1_AMT,FEE_2_AMT,FEE_3_AMT,FEE
_4_AMT,FEE_5_AMT,FEE_6_AMT)
> VALUES[/color]
(7,'Buy','20060117','A','C',NULL,NULL,'D
','E',3,99,NULL,NULL,14,NULL)red">
>
> Expect Results:
> OrderID,OrderType,OrderDate,ABConsolidat
ion,DEConsolidation
> 1,Buy,20060117,8,9
> 2,Buy,20060117,0,11
> 3,Sell,20060117,15,0
> 4,Buy,20060116,103,0
> 5,Sell,20060117,0,54
> 6,Buy,20060116,20,20
> 7,Buy,20060117,3,113
> Partial SELECT statement
> SELECT
> OrderID,
> OrderType,
> OrderDate,
> Perform calcs here AS AB_Consolidation,
> Perform calcs here AS CDE_Consolidation
> FROM ORDERS
>
>|||"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:OPVJGPUHGHA.2668@.tk2msftngp13.phx.gbl...
> cant you just do this, or am I missing something?
> SELECT
> OrderID,
> OrderType,
> OrderDate,
> coalesce(FEE_1_AMT,0)+coalesce(FEE_2_AMT
,0) AS AB_Consolidation,
> coalesce(FEE_3_AMT,0) + coalesce(FEE_4_AMT,0) + coalesce(FEE_5_AMT,0) AS
> CDE_Consolidation
> FROM ORDERS
> Basically, just add the collumns using coaless to handle the nulls.
Fee type A can be in any of the 6 fee fields so I don't think that will
work. I'm looking at Anith's solution to see if that will work.
thanks|||>
> Fee type A can be in any of the 6 fee fields so I don't think that will
> work. I'm looking at Anith's solution to see if that will work.
>
Ahhh... I misunderstood that in your first post. The problem is much more
interesting than I thought.
I like how Anith is getting 6 fields from one row to 6. I'll have to
remember this join technique in case I ever run into a similar situation.
If your final code differs from his, please let us know what you come up
with.
Tuesday, March 27, 2012
Consistency errors/repair_all_data_loss question
I've been receiving consistency errors for the past few weeks
whenever the indexes on 1 table are rebuilt (which they are each
evening). I restored the production database to our development
server and ran DBCC CHECKDB and DBREINDEX to confirm that the errors
were the same (which would rule out hardware errors, right?)
DBREINDEX results:
A possible database consistency problem has been detected on database
'database_name'. DBCC CHECKDB and DBCC CHECKCATALOG should be run on
database 'database_name'.
Connection Broken
Then I ran CHECKDB with repair_allow_data_loss (results below) and that
fixed the consistency errors (CHECKDB came back with no errors the
next time).
I read online (not BOL) that only index, header and consistency
information are removed with repair_all_data_loss, no actual data.
>From the info below, can you tell if the only thing that changed was
the clustered index? And if so, would it be safe (i.e., no data loss)
to run repair_allow_data_loss on the production database? I'd like
to clean this error up without resorting to a month old backup, which
would definitely mean data loss.
Thanks!!
CHECKDB with repair_allow_data_loss results:
CHECKDB found 0 allocation errors and 9 consistency errors in table
'dbowner.f8' (object ID 1648724926).
Ran DBCC CHECKDB ('database_name',repair_allow_data_loss) WITH
ALL_ERRORMSGS
DBCC results for 'dbowner.f8'.
Server: Msg 8944, Level 16, State 4, Line 1
Table error: Object ID 1648724926, index ID 0, page (1:155093), row 25.
Test (offsetNull >= BASEOFFSET) failed. Values are 0 and 4.
Server: Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 1648724926, index ID 1. Page (1:155093) was not
seen in the scan although its parent (1:155073) and previous (1:155092)
refer to it. Check any previous errors.
Server: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1648724926, index ID 1. Page (1:155094) is
missing a reference from previous page (1:155093). Possible chain
linkage problem.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1648724926. The text, ntext, or image node at
page (1:313603), slot 10, text ID 41680896 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1648724926. The text, ntext, or image node at
page (1:313603), slot 11, text ID 1340932096 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1648724926. The text, ntext, or image node at
page (1:313603), slot 12, text ID 23986176 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1648724926. The text, ntext, or image node at
page (1:313604), slot 0, text ID 96010240 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1648724926. The text, ntext, or image node at
page (1:313604), slot 1, text ID 817430528 is not referenced.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
Repair: Page (1:155093) has been deallocated from object ID 1648724926,
index ID 0.
Clustered index successfully restored for object 'dbowner.f8' in
database 'database_name'.
There are 1140058 rows in 33131 pages for object 'dbowner.f8'.
CHECKDB found 0 allocation errors and 9 consistency errors in table
'dbowner.f8' (object ID 1648724926).
CHECKDB fixed 0 allocation errors and 9 consistency errors in table
'dbowner.f8' (object ID 1648724926).
Select count (*) from dbowner.f8 on development server shows: 1140058
Select count (*) from dbowner.f8 on production server shows: 1145787
> I restored the production database to our development
> server and ran DBCC CHECKDB and DBREINDEX to confirm that the errors
> were the same (which would rule out hardware errors, right?)
I doubt it will rule out hw errors. I can imagine that hw errors were introduced on the prod
machine, corrupted the db and is carried over by your backup on the test machine.
> I read online (not BOL) that only index, header and consistency
> information are removed with repair_all_data_loss, no actual data.
Que! Can you post that link? AFAIK, The option does what it say. If will remove all corrupted pages,
resulting in possible data loss. In your case, you seem to have problems in index 0 (a data page)
for a user object (just reading the first error you posted). That page will be removed = data loss.
I strongly encourage you to open a case with MS Support, assuming that you value the database > the
few hundred bucks a support issue costs. They might not be able to help you further, but you have
tried. And make sure you do far more frequent CHECKDBs. Also, you might want to check
http://www.karaszi.com/SQLServer/inf...suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"nmsm" <naomimsm@.gmail.com> wrote in message
news:1127328673.857606.24480@.f14g2000cwb.googlegro ups.com...
> I've been receiving consistency errors for the past few weeks
> whenever the indexes on 1 table are rebuilt (which they are each
> evening). I restored the production database to our development
> server and ran DBCC CHECKDB and DBREINDEX to confirm that the errors
> were the same (which would rule out hardware errors, right?)
> DBREINDEX results:
> A possible database consistency problem has been detected on database
> 'database_name'. DBCC CHECKDB and DBCC CHECKCATALOG should be run on
> database 'database_name'.
> Connection Broken
>
> Then I ran CHECKDB with repair_allow_data_loss (results below) and that
> fixed the consistency errors (CHECKDB came back with no errors the
> next time).
> I read online (not BOL) that only index, header and consistency
> information are removed with repair_all_data_loss, no actual data.
> the clustered index? And if so, would it be safe (i.e., no data loss)
> to run repair_allow_data_loss on the production database? I'd like
> to clean this error up without resorting to a month old backup, which
> would definitely mean data loss.
> Thanks!!
> CHECKDB with repair_allow_data_loss results:
> CHECKDB found 0 allocation errors and 9 consistency errors in table
> 'dbowner.f8' (object ID 1648724926).
> Ran DBCC CHECKDB ('database_name',repair_allow_data_loss) WITH
> ALL_ERRORMSGS
> DBCC results for 'dbowner.f8'.
> Server: Msg 8944, Level 16, State 4, Line 1
> Table error: Object ID 1648724926, index ID 0, page (1:155093), row 25.
> Test (offsetNull >= BASEOFFSET) failed. Values are 0 and 4.
> Server: Msg 8976, Level 16, State 1, Line 1
> Table error: Object ID 1648724926, index ID 1. Page (1:155093) was not
> seen in the scan although its parent (1:155073) and previous (1:155092)
> refer to it. Check any previous errors.
> Server: Msg 8978, Level 16, State 1, Line 1
> Table error: Object ID 1648724926, index ID 1. Page (1:155094) is
> missing a reference from previous page (1:155093). Possible chain
> linkage problem.
> Server: Msg 8964, Level 16, State 1, Line 1
> Table error: Object ID 1648724926. The text, ntext, or image node at
> page (1:313603), slot 10, text ID 41680896 is not referenced.
> Server: Msg 8964, Level 16, State 1, Line 1
> Table error: Object ID 1648724926. The text, ntext, or image node at
> page (1:313603), slot 11, text ID 1340932096 is not referenced.
> Server: Msg 8964, Level 16, State 1, Line 1
> Table error: Object ID 1648724926. The text, ntext, or image node at
> page (1:313603), slot 12, text ID 23986176 is not referenced.
> Server: Msg 8964, Level 16, State 1, Line 1
> Table error: Object ID 1648724926. The text, ntext, or image node at
> page (1:313604), slot 0, text ID 96010240 is not referenced.
> Server: Msg 8964, Level 16, State 1, Line 1
> Table error: Object ID 1648724926. The text, ntext, or image node at
> page (1:313604), slot 1, text ID 817430528 is not referenced.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> Repair: Page (1:155093) has been deallocated from object ID 1648724926,
> index ID 0.
> Clustered index successfully restored for object 'dbowner.f8' in
> database 'database_name'.
> There are 1140058 rows in 33131 pages for object 'dbowner.f8'.
> CHECKDB found 0 allocation errors and 9 consistency errors in table
> 'dbowner.f8' (object ID 1648724926).
> CHECKDB fixed 0 allocation errors and 9 consistency errors in table
> 'dbowner.f8' (object ID 1648724926).
> Select count (*) from dbowner.f8 on development server shows: 1140058
> Select count (*) from dbowner.f8 on production server shows: 1145787
>
whenever the indexes on 1 table are rebuilt (which they are each
evening). I restored the production database to our development
server and ran DBCC CHECKDB and DBREINDEX to confirm that the errors
were the same (which would rule out hardware errors, right?)
DBREINDEX results:
A possible database consistency problem has been detected on database
'database_name'. DBCC CHECKDB and DBCC CHECKCATALOG should be run on
database 'database_name'.
Connection Broken
Then I ran CHECKDB with repair_allow_data_loss (results below) and that
fixed the consistency errors (CHECKDB came back with no errors the
next time).
I read online (not BOL) that only index, header and consistency
information are removed with repair_all_data_loss, no actual data.
>From the info below, can you tell if the only thing that changed was
the clustered index? And if so, would it be safe (i.e., no data loss)
to run repair_allow_data_loss on the production database? I'd like
to clean this error up without resorting to a month old backup, which
would definitely mean data loss.
Thanks!!
CHECKDB with repair_allow_data_loss results:
CHECKDB found 0 allocation errors and 9 consistency errors in table
'dbowner.f8' (object ID 1648724926).
Ran DBCC CHECKDB ('database_name',repair_allow_data_loss) WITH
ALL_ERRORMSGS
DBCC results for 'dbowner.f8'.
Server: Msg 8944, Level 16, State 4, Line 1
Table error: Object ID 1648724926, index ID 0, page (1:155093), row 25.
Test (offsetNull >= BASEOFFSET) failed. Values are 0 and 4.
Server: Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 1648724926, index ID 1. Page (1:155093) was not
seen in the scan although its parent (1:155073) and previous (1:155092)
refer to it. Check any previous errors.
Server: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1648724926, index ID 1. Page (1:155094) is
missing a reference from previous page (1:155093). Possible chain
linkage problem.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1648724926. The text, ntext, or image node at
page (1:313603), slot 10, text ID 41680896 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1648724926. The text, ntext, or image node at
page (1:313603), slot 11, text ID 1340932096 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1648724926. The text, ntext, or image node at
page (1:313603), slot 12, text ID 23986176 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1648724926. The text, ntext, or image node at
page (1:313604), slot 0, text ID 96010240 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1648724926. The text, ntext, or image node at
page (1:313604), slot 1, text ID 817430528 is not referenced.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
Repair: Page (1:155093) has been deallocated from object ID 1648724926,
index ID 0.
Clustered index successfully restored for object 'dbowner.f8' in
database 'database_name'.
There are 1140058 rows in 33131 pages for object 'dbowner.f8'.
CHECKDB found 0 allocation errors and 9 consistency errors in table
'dbowner.f8' (object ID 1648724926).
CHECKDB fixed 0 allocation errors and 9 consistency errors in table
'dbowner.f8' (object ID 1648724926).
Select count (*) from dbowner.f8 on development server shows: 1140058
Select count (*) from dbowner.f8 on production server shows: 1145787
> I restored the production database to our development
> server and ran DBCC CHECKDB and DBREINDEX to confirm that the errors
> were the same (which would rule out hardware errors, right?)
I doubt it will rule out hw errors. I can imagine that hw errors were introduced on the prod
machine, corrupted the db and is carried over by your backup on the test machine.
> I read online (not BOL) that only index, header and consistency
> information are removed with repair_all_data_loss, no actual data.
Que! Can you post that link? AFAIK, The option does what it say. If will remove all corrupted pages,
resulting in possible data loss. In your case, you seem to have problems in index 0 (a data page)
for a user object (just reading the first error you posted). That page will be removed = data loss.
I strongly encourage you to open a case with MS Support, assuming that you value the database > the
few hundred bucks a support issue costs. They might not be able to help you further, but you have
tried. And make sure you do far more frequent CHECKDBs. Also, you might want to check
http://www.karaszi.com/SQLServer/inf...suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"nmsm" <naomimsm@.gmail.com> wrote in message
news:1127328673.857606.24480@.f14g2000cwb.googlegro ups.com...
> I've been receiving consistency errors for the past few weeks
> whenever the indexes on 1 table are rebuilt (which they are each
> evening). I restored the production database to our development
> server and ran DBCC CHECKDB and DBREINDEX to confirm that the errors
> were the same (which would rule out hardware errors, right?)
> DBREINDEX results:
> A possible database consistency problem has been detected on database
> 'database_name'. DBCC CHECKDB and DBCC CHECKCATALOG should be run on
> database 'database_name'.
> Connection Broken
>
> Then I ran CHECKDB with repair_allow_data_loss (results below) and that
> fixed the consistency errors (CHECKDB came back with no errors the
> next time).
> I read online (not BOL) that only index, header and consistency
> information are removed with repair_all_data_loss, no actual data.
> the clustered index? And if so, would it be safe (i.e., no data loss)
> to run repair_allow_data_loss on the production database? I'd like
> to clean this error up without resorting to a month old backup, which
> would definitely mean data loss.
> Thanks!!
> CHECKDB with repair_allow_data_loss results:
> CHECKDB found 0 allocation errors and 9 consistency errors in table
> 'dbowner.f8' (object ID 1648724926).
> Ran DBCC CHECKDB ('database_name',repair_allow_data_loss) WITH
> ALL_ERRORMSGS
> DBCC results for 'dbowner.f8'.
> Server: Msg 8944, Level 16, State 4, Line 1
> Table error: Object ID 1648724926, index ID 0, page (1:155093), row 25.
> Test (offsetNull >= BASEOFFSET) failed. Values are 0 and 4.
> Server: Msg 8976, Level 16, State 1, Line 1
> Table error: Object ID 1648724926, index ID 1. Page (1:155093) was not
> seen in the scan although its parent (1:155073) and previous (1:155092)
> refer to it. Check any previous errors.
> Server: Msg 8978, Level 16, State 1, Line 1
> Table error: Object ID 1648724926, index ID 1. Page (1:155094) is
> missing a reference from previous page (1:155093). Possible chain
> linkage problem.
> Server: Msg 8964, Level 16, State 1, Line 1
> Table error: Object ID 1648724926. The text, ntext, or image node at
> page (1:313603), slot 10, text ID 41680896 is not referenced.
> Server: Msg 8964, Level 16, State 1, Line 1
> Table error: Object ID 1648724926. The text, ntext, or image node at
> page (1:313603), slot 11, text ID 1340932096 is not referenced.
> Server: Msg 8964, Level 16, State 1, Line 1
> Table error: Object ID 1648724926. The text, ntext, or image node at
> page (1:313603), slot 12, text ID 23986176 is not referenced.
> Server: Msg 8964, Level 16, State 1, Line 1
> Table error: Object ID 1648724926. The text, ntext, or image node at
> page (1:313604), slot 0, text ID 96010240 is not referenced.
> Server: Msg 8964, Level 16, State 1, Line 1
> Table error: Object ID 1648724926. The text, ntext, or image node at
> page (1:313604), slot 1, text ID 817430528 is not referenced.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> Repair: Page (1:155093) has been deallocated from object ID 1648724926,
> index ID 0.
> Clustered index successfully restored for object 'dbowner.f8' in
> database 'database_name'.
> There are 1140058 rows in 33131 pages for object 'dbowner.f8'.
> CHECKDB found 0 allocation errors and 9 consistency errors in table
> 'dbowner.f8' (object ID 1648724926).
> CHECKDB fixed 0 allocation errors and 9 consistency errors in table
> 'dbowner.f8' (object ID 1648724926).
> Select count (*) from dbowner.f8 on development server shows: 1140058
> Select count (*) from dbowner.f8 on production server shows: 1145787
>
Consistency errors/repair_all_data_loss question
I've been receiving consistency errors for the past few weeks
whenever the indexes on 1 table are rebuilt (which they are each
evening). I restored the production database to our development
server and ran DBCC CHECKDB and DBREINDEX to confirm that the errors
were the same (which would rule out hardware errors, right?)
DBREINDEX results:
A possible database consistency problem has been detected on database
'database_name'. DBCC CHECKDB and DBCC CHECKCATALOG should be run on
database 'database_name'.
Connection Broken
Then I ran CHECKDB with repair_allow_data_loss (results below) and that
fixed the consistency errors (CHECKDB came back with no errors the
next time).
I read online (not BOL) that only index, header and consistency
information are removed with repair_all_data_loss, no actual data.
>From the info below, can you tell if the only thing that changed was
the clustered index? And if so, would it be safe (i.e., no data loss)
to run repair_allow_data_loss on the production database? I'd like
to clean this error up without resorting to a month old backup, which
would definitely mean data loss.
Thanks!!
CHECKDB with repair_allow_data_loss results:
CHECKDB found 0 allocation errors and 9 consistency errors in table
'dbowner.f8' (object ID 1648724926).
Ran DBCC CHECKDB ('database_name',repair_allow_data_loss) WITH
ALL_ERRORMSGS
DBCC results for 'dbowner.f8'.
Server: Msg 8944, Level 16, State 4, Line 1
Table error: Object ID 1648724926, index ID 0, page (1:155093), row 25.
Test (offsetNull >= BASEOFFSET) failed. Values are 0 and 4.
Server: Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 1648724926, index ID 1. Page (1:155093) was not
seen in the scan although its parent (1:155073) and previous (1:155092)
refer to it. Check any previous errors.
Server: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1648724926, index ID 1. Page (1:155094) is
missing a reference from previous page (1:155093). Possible chain
linkage problem.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1648724926. The text, ntext, or image node at
page (1:313603), slot 10, text ID 41680896 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1648724926. The text, ntext, or image node at
page (1:313603), slot 11, text ID 1340932096 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1648724926. The text, ntext, or image node at
page (1:313603), slot 12, text ID 23986176 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1648724926. The text, ntext, or image node at
page (1:313604), slot 0, text ID 96010240 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1648724926. The text, ntext, or image node at
page (1:313604), slot 1, text ID 817430528 is not referenced.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
Repair: Page (1:155093) has been deallocated from object ID 1648724926,
index ID 0.
Clustered index successfully restored for object 'dbowner.f8' in
database 'database_name'.
There are 1140058 rows in 33131 pages for object 'dbowner.f8'.
CHECKDB found 0 allocation errors and 9 consistency errors in table
'dbowner.f8' (object ID 1648724926).
CHECKDB fixed 0 allocation errors and 9 consistency errors in table
'dbowner.f8' (object ID 1648724926).
Select count (*) from dbowner.f8 on development server shows: 1140058
Select count (*) from dbowner.f8 on production server shows: 1145787> I restored the production database to our development
> server and ran DBCC CHECKDB and DBREINDEX to confirm that the errors
> were the same (which would rule out hardware errors, right?)
I doubt it will rule out hw errors. I can imagine that hw errors were introduced on the prod
machine, corrupted the db and is carried over by your backup on the test machine.
> I read online (not BOL) that only index, header and consistency
> information are removed with repair_all_data_loss, no actual data.
Que! Can you post that link? AFAIK, The option does what it say. If will remove all corrupted pages,
resulting in possible data loss. In your case, you seem to have problems in index 0 (a data page)
for a user object (just reading the first error you posted). That page will be removed = data loss.
I strongly encourage you to open a case with MS Support, assuming that you value the database > the
few hundred bucks a support issue costs. They might not be able to help you further, but you have
tried. And make sure you do far more frequent CHECKDBs. Also, you might want to check
http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"nmsm" <naomimsm@.gmail.com> wrote in message
news:1127328673.857606.24480@.f14g2000cwb.googlegroups.com...
> I've been receiving consistency errors for the past few weeks
> whenever the indexes on 1 table are rebuilt (which they are each
> evening). I restored the production database to our development
> server and ran DBCC CHECKDB and DBREINDEX to confirm that the errors
> were the same (which would rule out hardware errors, right?)
> DBREINDEX results:
> A possible database consistency problem has been detected on database
> 'database_name'. DBCC CHECKDB and DBCC CHECKCATALOG should be run on
> database 'database_name'.
> Connection Broken
>
> Then I ran CHECKDB with repair_allow_data_loss (results below) and that
> fixed the consistency errors (CHECKDB came back with no errors the
> next time).
> I read online (not BOL) that only index, header and consistency
> information are removed with repair_all_data_loss, no actual data.
>>From the info below, can you tell if the only thing that changed was
> the clustered index? And if so, would it be safe (i.e., no data loss)
> to run repair_allow_data_loss on the production database? I'd like
> to clean this error up without resorting to a month old backup, which
> would definitely mean data loss.
> Thanks!!
> CHECKDB with repair_allow_data_loss results:
> CHECKDB found 0 allocation errors and 9 consistency errors in table
> 'dbowner.f8' (object ID 1648724926).
> Ran DBCC CHECKDB ('database_name',repair_allow_data_loss) WITH
> ALL_ERRORMSGS
> DBCC results for 'dbowner.f8'.
> Server: Msg 8944, Level 16, State 4, Line 1
> Table error: Object ID 1648724926, index ID 0, page (1:155093), row 25.
> Test (offsetNull >= BASEOFFSET) failed. Values are 0 and 4.
> Server: Msg 8976, Level 16, State 1, Line 1
> Table error: Object ID 1648724926, index ID 1. Page (1:155093) was not
> seen in the scan although its parent (1:155073) and previous (1:155092)
> refer to it. Check any previous errors.
> Server: Msg 8978, Level 16, State 1, Line 1
> Table error: Object ID 1648724926, index ID 1. Page (1:155094) is
> missing a reference from previous page (1:155093). Possible chain
> linkage problem.
> Server: Msg 8964, Level 16, State 1, Line 1
> Table error: Object ID 1648724926. The text, ntext, or image node at
> page (1:313603), slot 10, text ID 41680896 is not referenced.
> Server: Msg 8964, Level 16, State 1, Line 1
> Table error: Object ID 1648724926. The text, ntext, or image node at
> page (1:313603), slot 11, text ID 1340932096 is not referenced.
> Server: Msg 8964, Level 16, State 1, Line 1
> Table error: Object ID 1648724926. The text, ntext, or image node at
> page (1:313603), slot 12, text ID 23986176 is not referenced.
> Server: Msg 8964, Level 16, State 1, Line 1
> Table error: Object ID 1648724926. The text, ntext, or image node at
> page (1:313604), slot 0, text ID 96010240 is not referenced.
> Server: Msg 8964, Level 16, State 1, Line 1
> Table error: Object ID 1648724926. The text, ntext, or image node at
> page (1:313604), slot 1, text ID 817430528 is not referenced.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> Repair: Page (1:155093) has been deallocated from object ID 1648724926,
> index ID 0.
> Clustered index successfully restored for object 'dbowner.f8' in
> database 'database_name'.
> There are 1140058 rows in 33131 pages for object 'dbowner.f8'.
> CHECKDB found 0 allocation errors and 9 consistency errors in table
> 'dbowner.f8' (object ID 1648724926).
> CHECKDB fixed 0 allocation errors and 9 consistency errors in table
> 'dbowner.f8' (object ID 1648724926).
> Select count (*) from dbowner.f8 on development server shows: 1140058
> Select count (*) from dbowner.f8 on production server shows: 1145787
>
whenever the indexes on 1 table are rebuilt (which they are each
evening). I restored the production database to our development
server and ran DBCC CHECKDB and DBREINDEX to confirm that the errors
were the same (which would rule out hardware errors, right?)
DBREINDEX results:
A possible database consistency problem has been detected on database
'database_name'. DBCC CHECKDB and DBCC CHECKCATALOG should be run on
database 'database_name'.
Connection Broken
Then I ran CHECKDB with repair_allow_data_loss (results below) and that
fixed the consistency errors (CHECKDB came back with no errors the
next time).
I read online (not BOL) that only index, header and consistency
information are removed with repair_all_data_loss, no actual data.
>From the info below, can you tell if the only thing that changed was
the clustered index? And if so, would it be safe (i.e., no data loss)
to run repair_allow_data_loss on the production database? I'd like
to clean this error up without resorting to a month old backup, which
would definitely mean data loss.
Thanks!!
CHECKDB with repair_allow_data_loss results:
CHECKDB found 0 allocation errors and 9 consistency errors in table
'dbowner.f8' (object ID 1648724926).
Ran DBCC CHECKDB ('database_name',repair_allow_data_loss) WITH
ALL_ERRORMSGS
DBCC results for 'dbowner.f8'.
Server: Msg 8944, Level 16, State 4, Line 1
Table error: Object ID 1648724926, index ID 0, page (1:155093), row 25.
Test (offsetNull >= BASEOFFSET) failed. Values are 0 and 4.
Server: Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 1648724926, index ID 1. Page (1:155093) was not
seen in the scan although its parent (1:155073) and previous (1:155092)
refer to it. Check any previous errors.
Server: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1648724926, index ID 1. Page (1:155094) is
missing a reference from previous page (1:155093). Possible chain
linkage problem.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1648724926. The text, ntext, or image node at
page (1:313603), slot 10, text ID 41680896 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1648724926. The text, ntext, or image node at
page (1:313603), slot 11, text ID 1340932096 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1648724926. The text, ntext, or image node at
page (1:313603), slot 12, text ID 23986176 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1648724926. The text, ntext, or image node at
page (1:313604), slot 0, text ID 96010240 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1648724926. The text, ntext, or image node at
page (1:313604), slot 1, text ID 817430528 is not referenced.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
Repair: Page (1:155093) has been deallocated from object ID 1648724926,
index ID 0.
Clustered index successfully restored for object 'dbowner.f8' in
database 'database_name'.
There are 1140058 rows in 33131 pages for object 'dbowner.f8'.
CHECKDB found 0 allocation errors and 9 consistency errors in table
'dbowner.f8' (object ID 1648724926).
CHECKDB fixed 0 allocation errors and 9 consistency errors in table
'dbowner.f8' (object ID 1648724926).
Select count (*) from dbowner.f8 on development server shows: 1140058
Select count (*) from dbowner.f8 on production server shows: 1145787> I restored the production database to our development
> server and ran DBCC CHECKDB and DBREINDEX to confirm that the errors
> were the same (which would rule out hardware errors, right?)
I doubt it will rule out hw errors. I can imagine that hw errors were introduced on the prod
machine, corrupted the db and is carried over by your backup on the test machine.
> I read online (not BOL) that only index, header and consistency
> information are removed with repair_all_data_loss, no actual data.
Que! Can you post that link? AFAIK, The option does what it say. If will remove all corrupted pages,
resulting in possible data loss. In your case, you seem to have problems in index 0 (a data page)
for a user object (just reading the first error you posted). That page will be removed = data loss.
I strongly encourage you to open a case with MS Support, assuming that you value the database > the
few hundred bucks a support issue costs. They might not be able to help you further, but you have
tried. And make sure you do far more frequent CHECKDBs. Also, you might want to check
http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"nmsm" <naomimsm@.gmail.com> wrote in message
news:1127328673.857606.24480@.f14g2000cwb.googlegroups.com...
> I've been receiving consistency errors for the past few weeks
> whenever the indexes on 1 table are rebuilt (which they are each
> evening). I restored the production database to our development
> server and ran DBCC CHECKDB and DBREINDEX to confirm that the errors
> were the same (which would rule out hardware errors, right?)
> DBREINDEX results:
> A possible database consistency problem has been detected on database
> 'database_name'. DBCC CHECKDB and DBCC CHECKCATALOG should be run on
> database 'database_name'.
> Connection Broken
>
> Then I ran CHECKDB with repair_allow_data_loss (results below) and that
> fixed the consistency errors (CHECKDB came back with no errors the
> next time).
> I read online (not BOL) that only index, header and consistency
> information are removed with repair_all_data_loss, no actual data.
>>From the info below, can you tell if the only thing that changed was
> the clustered index? And if so, would it be safe (i.e., no data loss)
> to run repair_allow_data_loss on the production database? I'd like
> to clean this error up without resorting to a month old backup, which
> would definitely mean data loss.
> Thanks!!
> CHECKDB with repair_allow_data_loss results:
> CHECKDB found 0 allocation errors and 9 consistency errors in table
> 'dbowner.f8' (object ID 1648724926).
> Ran DBCC CHECKDB ('database_name',repair_allow_data_loss) WITH
> ALL_ERRORMSGS
> DBCC results for 'dbowner.f8'.
> Server: Msg 8944, Level 16, State 4, Line 1
> Table error: Object ID 1648724926, index ID 0, page (1:155093), row 25.
> Test (offsetNull >= BASEOFFSET) failed. Values are 0 and 4.
> Server: Msg 8976, Level 16, State 1, Line 1
> Table error: Object ID 1648724926, index ID 1. Page (1:155093) was not
> seen in the scan although its parent (1:155073) and previous (1:155092)
> refer to it. Check any previous errors.
> Server: Msg 8978, Level 16, State 1, Line 1
> Table error: Object ID 1648724926, index ID 1. Page (1:155094) is
> missing a reference from previous page (1:155093). Possible chain
> linkage problem.
> Server: Msg 8964, Level 16, State 1, Line 1
> Table error: Object ID 1648724926. The text, ntext, or image node at
> page (1:313603), slot 10, text ID 41680896 is not referenced.
> Server: Msg 8964, Level 16, State 1, Line 1
> Table error: Object ID 1648724926. The text, ntext, or image node at
> page (1:313603), slot 11, text ID 1340932096 is not referenced.
> Server: Msg 8964, Level 16, State 1, Line 1
> Table error: Object ID 1648724926. The text, ntext, or image node at
> page (1:313603), slot 12, text ID 23986176 is not referenced.
> Server: Msg 8964, Level 16, State 1, Line 1
> Table error: Object ID 1648724926. The text, ntext, or image node at
> page (1:313604), slot 0, text ID 96010240 is not referenced.
> Server: Msg 8964, Level 16, State 1, Line 1
> Table error: Object ID 1648724926. The text, ntext, or image node at
> page (1:313604), slot 1, text ID 817430528 is not referenced.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> Repair: Page (1:155093) has been deallocated from object ID 1648724926,
> index ID 0.
> Clustered index successfully restored for object 'dbowner.f8' in
> database 'database_name'.
> There are 1140058 rows in 33131 pages for object 'dbowner.f8'.
> CHECKDB found 0 allocation errors and 9 consistency errors in table
> 'dbowner.f8' (object ID 1648724926).
> CHECKDB fixed 0 allocation errors and 9 consistency errors in table
> 'dbowner.f8' (object ID 1648724926).
> Select count (*) from dbowner.f8 on development server shows: 1140058
> Select count (*) from dbowner.f8 on production server shows: 1145787
>
Consistency errors/repair_all_data_loss question
I've been receiving consistency errors for the past few weeks
whenever the indexes on 1 table are rebuilt (which they are each
evening). I restored the production database to our development
server and ran DBCC CHECKDB and DBREINDEX to confirm that the errors
were the same (which would rule out hardware errors, right?)
DBREINDEX results:
A possible database consistency problem has been detected on database
'database_name'. DBCC CHECKDB and DBCC CHECKCATALOG should be run on
database 'database_name'.
Connection Broken
Then I ran CHECKDB with repair_allow_data_loss (results below) and that
fixed the consistency errors (CHECKDB came back with no errors the
next time).
I read online (not BOL) that only index, header and consistency
information are removed with repair_all_data_loss, no actual data.
>From the info below, can you tell if the only thing that changed was
the clustered index? And if so, would it be safe (i.e., no data loss)
to run repair_allow_data_loss on the production database? I'd like
to clean this error up without resorting to a month old backup, which
would definitely mean data loss.
Thanks!!
CHECKDB with repair_allow_data_loss results:
CHECKDB found 0 allocation errors and 9 consistency errors in table
'dbowner.f8' (object ID 1648724926).
Ran DBCC CHECKDB ('database_name',repair_allow_data_loss)
WITH
ALL_ERRORMSGS
DBCC results for 'dbowner.f8'.
Server: Msg 8944, Level 16, State 4, Line 1
Table error: Object ID 1648724926, index ID 0, page (1:155093), row 25.
Test (offsetNull >= BASEOFFSET) failed. Values are 0 and 4.
Server: Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 1648724926, index ID 1. Page (1:155093) was not
seen in the scan although its parent (1:155073) and previous (1:155092)
refer to it. Check any previous errors.
Server: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1648724926, index ID 1. Page (1:155094) is
missing a reference from previous page (1:155093). Possible chain
linkage problem.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1648724926. The text, ntext, or image node at
page (1:313603), slot 10, text ID 41680896 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1648724926. The text, ntext, or image node at
page (1:313603), slot 11, text ID 1340932096 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1648724926. The text, ntext, or image node at
page (1:313603), slot 12, text ID 23986176 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1648724926. The text, ntext, or image node at
page (1:313604), slot 0, text ID 96010240 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1648724926. The text, ntext, or image node at
page (1:313604), slot 1, text ID 817430528 is not referenced.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
Repair: Page (1:155093) has been deallocated from object ID 1648724926,
index ID 0.
Clustered index successfully restored for object 'dbowner.f8' in
database 'database_name'.
There are 1140058 rows in 33131 pages for object 'dbowner.f8'.
CHECKDB found 0 allocation errors and 9 consistency errors in table
'dbowner.f8' (object ID 1648724926).
CHECKDB fixed 0 allocation errors and 9 consistency errors in table
'dbowner.f8' (object ID 1648724926).
Select count (*) from dbowner.f8 on development server shows: 1140058
Select count (*) from dbowner.f8 on production server shows: 1145787> I restored the production database to our development
> server and ran DBCC CHECKDB and DBREINDEX to confirm that the errors
> were the same (which would rule out hardware errors, right?)
I doubt it will rule out hw errors. I can imagine that hw errors were introd
uced on the prod
machine, corrupted the db and is carried over by your backup on the test mac
hine.
> I read online (not BOL) that only index, header and consistency
> information are removed with repair_all_data_loss, no actual data.
Que! Can you post that link? AFAIK, The option does what it say. If will rem
ove all corrupted pages,
resulting in possible data loss. In your case, you seem to have problems in
index 0 (a data page)
for a user object (just reading the first error you posted). That page will
be removed = data loss.
I strongly encourage you to open a case with MS Support, assuming that you v
alue the database > the
few hundred bucks a support issue costs. They might not be able to help you
further, but you have
tried. And make sure you do far more frequent CHECKDBs. Also, you might want
to check
http://www.karaszi.com/SQLServer/in..._suspect_db.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"nmsm" <naomimsm@.gmail.com> wrote in message
news:1127328673.857606.24480@.f14g2000cwb.googlegroups.com...
> I've been receiving consistency errors for the past few weeks
> whenever the indexes on 1 table are rebuilt (which they are each
> evening). I restored the production database to our development
> server and ran DBCC CHECKDB and DBREINDEX to confirm that the errors
> were the same (which would rule out hardware errors, right?)
> DBREINDEX results:
> A possible database consistency problem has been detected on database
> 'database_name'. DBCC CHECKDB and DBCC CHECKCATALOG should be run on
> database 'database_name'.
> Connection Broken
>
> Then I ran CHECKDB with repair_allow_data_loss (results below) and that
> fixed the consistency errors (CHECKDB came back with no errors the
> next time).
> I read online (not BOL) that only index, header and consistency
> information are removed with repair_all_data_loss, no actual data.
> the clustered index? And if so, would it be safe (i.e., no data loss)
> to run repair_allow_data_loss on the production database? I'd like
> to clean this error up without resorting to a month old backup, which
> would definitely mean data loss.
> Thanks!!
> CHECKDB with repair_allow_data_loss results:
> CHECKDB found 0 allocation errors and 9 consistency errors in table
> 'dbowner.f8' (object ID 1648724926).
> Ran DBCC CHECKDB ('database_name',repair_allow_data_loss)
WITH
> ALL_ERRORMSGS
> DBCC results for 'dbowner.f8'.
> Server: Msg 8944, Level 16, State 4, Line 1
> Table error: Object ID 1648724926, index ID 0, page (1:155093), row 25.
> Test (offsetNull >= BASEOFFSET) failed. Values are 0 and 4.
> Server: Msg 8976, Level 16, State 1, Line 1
> Table error: Object ID 1648724926, index ID 1. Page (1:155093) was not
> seen in the scan although its parent (1:155073) and previous (1:155092)
> refer to it. Check any previous errors.
> Server: Msg 8978, Level 16, State 1, Line 1
> Table error: Object ID 1648724926, index ID 1. Page (1:155094) is
> missing a reference from previous page (1:155093). Possible chain
> linkage problem.
> Server: Msg 8964, Level 16, State 1, Line 1
> Table error: Object ID 1648724926. The text, ntext, or image node at
> page (1:313603), slot 10, text ID 41680896 is not referenced.
> Server: Msg 8964, Level 16, State 1, Line 1
> Table error: Object ID 1648724926. The text, ntext, or image node at
> page (1:313603), slot 11, text ID 1340932096 is not referenced.
> Server: Msg 8964, Level 16, State 1, Line 1
> Table error: Object ID 1648724926. The text, ntext, or image node at
> page (1:313603), slot 12, text ID 23986176 is not referenced.
> Server: Msg 8964, Level 16, State 1, Line 1
> Table error: Object ID 1648724926. The text, ntext, or image node at
> page (1:313604), slot 0, text ID 96010240 is not referenced.
> Server: Msg 8964, Level 16, State 1, Line 1
> Table error: Object ID 1648724926. The text, ntext, or image node at
> page (1:313604), slot 1, text ID 817430528 is not referenced.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> Repair: Page (1:155093) has been deallocated from object ID 1648724926,
> index ID 0.
> Clustered index successfully restored for object 'dbowner.f8' in
> database 'database_name'.
> There are 1140058 rows in 33131 pages for object 'dbowner.f8'.
> CHECKDB found 0 allocation errors and 9 consistency errors in table
> 'dbowner.f8' (object ID 1648724926).
> CHECKDB fixed 0 allocation errors and 9 consistency errors in table
> 'dbowner.f8' (object ID 1648724926).
> Select count (*) from dbowner.f8 on development server shows: 1140058
> Select count (*) from dbowner.f8 on production server shows: 1145787
>sqlsql
whenever the indexes on 1 table are rebuilt (which they are each
evening). I restored the production database to our development
server and ran DBCC CHECKDB and DBREINDEX to confirm that the errors
were the same (which would rule out hardware errors, right?)
DBREINDEX results:
A possible database consistency problem has been detected on database
'database_name'. DBCC CHECKDB and DBCC CHECKCATALOG should be run on
database 'database_name'.
Connection Broken
Then I ran CHECKDB with repair_allow_data_loss (results below) and that
fixed the consistency errors (CHECKDB came back with no errors the
next time).
I read online (not BOL) that only index, header and consistency
information are removed with repair_all_data_loss, no actual data.
>From the info below, can you tell if the only thing that changed was
the clustered index? And if so, would it be safe (i.e., no data loss)
to run repair_allow_data_loss on the production database? I'd like
to clean this error up without resorting to a month old backup, which
would definitely mean data loss.
Thanks!!
CHECKDB with repair_allow_data_loss results:
CHECKDB found 0 allocation errors and 9 consistency errors in table
'dbowner.f8' (object ID 1648724926).
Ran DBCC CHECKDB ('database_name',repair_allow_data_loss)
WITH
ALL_ERRORMSGS
DBCC results for 'dbowner.f8'.
Server: Msg 8944, Level 16, State 4, Line 1
Table error: Object ID 1648724926, index ID 0, page (1:155093), row 25.
Test (offsetNull >= BASEOFFSET) failed. Values are 0 and 4.
Server: Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 1648724926, index ID 1. Page (1:155093) was not
seen in the scan although its parent (1:155073) and previous (1:155092)
refer to it. Check any previous errors.
Server: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1648724926, index ID 1. Page (1:155094) is
missing a reference from previous page (1:155093). Possible chain
linkage problem.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1648724926. The text, ntext, or image node at
page (1:313603), slot 10, text ID 41680896 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1648724926. The text, ntext, or image node at
page (1:313603), slot 11, text ID 1340932096 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1648724926. The text, ntext, or image node at
page (1:313603), slot 12, text ID 23986176 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1648724926. The text, ntext, or image node at
page (1:313604), slot 0, text ID 96010240 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1648724926. The text, ntext, or image node at
page (1:313604), slot 1, text ID 817430528 is not referenced.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
Repair: Page (1:155093) has been deallocated from object ID 1648724926,
index ID 0.
Clustered index successfully restored for object 'dbowner.f8' in
database 'database_name'.
There are 1140058 rows in 33131 pages for object 'dbowner.f8'.
CHECKDB found 0 allocation errors and 9 consistency errors in table
'dbowner.f8' (object ID 1648724926).
CHECKDB fixed 0 allocation errors and 9 consistency errors in table
'dbowner.f8' (object ID 1648724926).
Select count (*) from dbowner.f8 on development server shows: 1140058
Select count (*) from dbowner.f8 on production server shows: 1145787> I restored the production database to our development
> server and ran DBCC CHECKDB and DBREINDEX to confirm that the errors
> were the same (which would rule out hardware errors, right?)
I doubt it will rule out hw errors. I can imagine that hw errors were introd
uced on the prod
machine, corrupted the db and is carried over by your backup on the test mac
hine.
> I read online (not BOL) that only index, header and consistency
> information are removed with repair_all_data_loss, no actual data.
Que! Can you post that link? AFAIK, The option does what it say. If will rem
ove all corrupted pages,
resulting in possible data loss. In your case, you seem to have problems in
index 0 (a data page)
for a user object (just reading the first error you posted). That page will
be removed = data loss.
I strongly encourage you to open a case with MS Support, assuming that you v
alue the database > the
few hundred bucks a support issue costs. They might not be able to help you
further, but you have
tried. And make sure you do far more frequent CHECKDBs. Also, you might want
to check
http://www.karaszi.com/SQLServer/in..._suspect_db.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"nmsm" <naomimsm@.gmail.com> wrote in message
news:1127328673.857606.24480@.f14g2000cwb.googlegroups.com...
> I've been receiving consistency errors for the past few weeks
> whenever the indexes on 1 table are rebuilt (which they are each
> evening). I restored the production database to our development
> server and ran DBCC CHECKDB and DBREINDEX to confirm that the errors
> were the same (which would rule out hardware errors, right?)
> DBREINDEX results:
> A possible database consistency problem has been detected on database
> 'database_name'. DBCC CHECKDB and DBCC CHECKCATALOG should be run on
> database 'database_name'.
> Connection Broken
>
> Then I ran CHECKDB with repair_allow_data_loss (results below) and that
> fixed the consistency errors (CHECKDB came back with no errors the
> next time).
> I read online (not BOL) that only index, header and consistency
> information are removed with repair_all_data_loss, no actual data.
> the clustered index? And if so, would it be safe (i.e., no data loss)
> to run repair_allow_data_loss on the production database? I'd like
> to clean this error up without resorting to a month old backup, which
> would definitely mean data loss.
> Thanks!!
> CHECKDB with repair_allow_data_loss results:
> CHECKDB found 0 allocation errors and 9 consistency errors in table
> 'dbowner.f8' (object ID 1648724926).
> Ran DBCC CHECKDB ('database_name',repair_allow_data_loss)
WITH
> ALL_ERRORMSGS
> DBCC results for 'dbowner.f8'.
> Server: Msg 8944, Level 16, State 4, Line 1
> Table error: Object ID 1648724926, index ID 0, page (1:155093), row 25.
> Test (offsetNull >= BASEOFFSET) failed. Values are 0 and 4.
> Server: Msg 8976, Level 16, State 1, Line 1
> Table error: Object ID 1648724926, index ID 1. Page (1:155093) was not
> seen in the scan although its parent (1:155073) and previous (1:155092)
> refer to it. Check any previous errors.
> Server: Msg 8978, Level 16, State 1, Line 1
> Table error: Object ID 1648724926, index ID 1. Page (1:155094) is
> missing a reference from previous page (1:155093). Possible chain
> linkage problem.
> Server: Msg 8964, Level 16, State 1, Line 1
> Table error: Object ID 1648724926. The text, ntext, or image node at
> page (1:313603), slot 10, text ID 41680896 is not referenced.
> Server: Msg 8964, Level 16, State 1, Line 1
> Table error: Object ID 1648724926. The text, ntext, or image node at
> page (1:313603), slot 11, text ID 1340932096 is not referenced.
> Server: Msg 8964, Level 16, State 1, Line 1
> Table error: Object ID 1648724926. The text, ntext, or image node at
> page (1:313603), slot 12, text ID 23986176 is not referenced.
> Server: Msg 8964, Level 16, State 1, Line 1
> Table error: Object ID 1648724926. The text, ntext, or image node at
> page (1:313604), slot 0, text ID 96010240 is not referenced.
> Server: Msg 8964, Level 16, State 1, Line 1
> Table error: Object ID 1648724926. The text, ntext, or image node at
> page (1:313604), slot 1, text ID 817430528 is not referenced.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> Repair: Page (1:155093) has been deallocated from object ID 1648724926,
> index ID 0.
> Clustered index successfully restored for object 'dbowner.f8' in
> database 'database_name'.
> There are 1140058 rows in 33131 pages for object 'dbowner.f8'.
> CHECKDB found 0 allocation errors and 9 consistency errors in table
> 'dbowner.f8' (object ID 1648724926).
> CHECKDB fixed 0 allocation errors and 9 consistency errors in table
> 'dbowner.f8' (object ID 1648724926).
> Select count (*) from dbowner.f8 on development server shows: 1140058
> Select count (*) from dbowner.f8 on production server shows: 1145787
>sqlsql
consistency errors not associated with any single object
I have a dbcc checkdb returning that I have two consistency errors. One
is directly attributal to a table. The second is not attributable to
any single objects. It is my understanding that this second consistency
error is related to an extent that is shared. Is there anyway to see
which objects share this unknown extnet? If I issue a dbcc checkdb
along with a repair allow data loss is there anyway to see what data it
will remove before it does so?
Thanks ahead of time.Please post the exact output from CHECKDB.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"sfibich" <sfibich@.pfgc.com> wrote in message
news:uZDCRS3YEHA.644@.tk2msftngp13.phx.gbl...
> I have a dbcc checkdb returning that I have two consistency errors. One
> is directly attributal to a table. The second is not attributable to
> any single objects. It is my understanding that this second consistency
> error is related to an extent that is shared. Is there anyway to see
> which objects share this unknown extnet? If I issue a dbcc checkdb
> along with a repair allow data loss is there anyway to see what data it
> will remove before it does so?
> Thanks ahead of time.|||Paul S Randal [MS] wrote:
> Please post the exact output from CHECKDB.
>
There is an error at the very top, this is the one I am refereing to.
The error in the middle relates to a particular table that I can deal with.
Job 'DBCC_checkdb' : Step 7, 'dbcc checkdb stage' : Began Executing
2004-07-06 09:12:59
Msg 8909, Sev 16: Table error: Object ID 0, index ID 0, page ID
(3:391967). The PageId in the page header = (0:24). [SQLSTATE 42000]
Msg 2536, Sev 16: DBCC results for 'stage'. [SQLSTATE 01000]
Msg 8954, Sev 16: CHECKDB found 0 allocation errors and 1 consistency
errors not associated with any single object. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'sysobjects'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 329 rows in 7 pages for object 'sysobjects'.
[SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'sysindexes'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 666 rows in 37 pages for object
'sysindexes'. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'syscolumns'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 3400 rows in 78 pages for object
'syscolumns'. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'systypes'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 26 rows in 1 pages for object 'systypes'.
[SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'syscomments'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 296 rows in 81 pages for object
'syscomments'. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'sysfiles1'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 7 rows in 1 pages for object 'sysfiles1'.
[SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'syspermissions'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 52 rows in 1 pages for object
'syspermissions'. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'sysusers'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 18 rows in 1 pages for object 'sysusers'.
[SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'sysproperties'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 0 rows in 0 pages for object
'sysproperties'. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'sysdepends'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 2423 rows in 15 pages for object
'sysdepends'. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'sysreferences'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 3 rows in 1 pages for object
'sysreferences'. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'sysfulltextcatalogs'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 0 rows in 0 pages for object
'sysfulltextcatalogs'. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'sysfulltextnotify'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 0 rows in 0 pages for object
'sysfulltextnotify'. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'sysfilegroups'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 6 rows in 1 pages for object
'sysfilegroups'. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'meta_control_missing_day_ends_tbl'.
[SQLSTATE 01000]
There are 0 rows in 0 pages for object
'meta_control_missing_day_ends_tbl'. [SQLSTATE 01000]
DBCC results for 'import_opco_AFSLHF'. [SQLSTATE 01000]
There are 6442240 rows in 715814 pages for object 'import_opco_AFSLHF'.
[SQLSTATE 01000]
DBCC results for 'import_opco_AGPODF'. [SQLSTATE 01000]
There are 4601233 rows in 678964 pages for object 'import_opco_AGPODF'.
[SQLSTATE 01000]
DBCC results for 'BRBRDF'. [SQLSTATE 01000]
There are 0 rows in 0 pages for object 'BRBRDF'. [SQLSTATE 01000]
DBCC results for 'import_opco_AHPOHF'. [SQLSTATE 01000]
There are 592812 rows in 98813 pages for object 'import_opco_AHPOHF'.
[SQLSTATE 01000]
DBCC results for 'CR01F'. [SQLSTATE 01000]
There are 1570 rows in 11 pages for object 'CR01F'. [SQLSTATE 01000]
DBCC results for 'XXX_import_pfg_CMOTYPF'. [SQLSTATE 01000]
There are 65576 rows in 227 pages for object 'XXX_import_pfg_CMOTYPF'.
[SQLSTATE 01000]
DBCC results for 'XXX_import_pfg_CMPTYPF'. [SQLSTATE 01000]
There are 5415 rows in 33 pages for object 'XXX_import_pfg_CMPTYPF'.
[SQLSTATE 01000]
DBCC results for 'TYTYPF'. [SQLSTATE 01000]
There are 20800 rows in 138 pages for object 'TYTYPF'. [SQLSTATE 01000]
DBCC results for 'XXX_import_pfg_CTOTYPF'. [SQLSTATE 01000]
There are 340193 rows in 1516 pages for object 'XXX_import_pfg_CTOTYPF'.
[SQLSTATE 01000]
DBCC results for 'meta_AJFFCTLF_Status_tbl'. [SQLSTATE 01000]
There are 5 rows in 1 pages for object 'meta_AJFFCTLF_Status_tbl'.
[SQLSTATE 01000]
DBCC results for 'AESLDF_tbl'. [SQLSTATE 01000]
There are 14807362 rows in 227806 pages for object 'AESLDF_tbl'.
[SQLSTATE 01000]
DBCC results for 'XXX_import_pfg_CTPTYPF'. [SQLSTATE 01000]
There are 35448 rows in 229 pages for object 'XXX_import_pfg_CTPTYPF'.
[SQLSTATE 01000]
DBCC results for 'AFSLHF_tbl'. [SQLSTATE 01000]
There are 751636 rows in 11219 pages for object 'AFSLHF_tbl'. [SQLSTATE
01000]
DBCC results for 'meta_MetaControlAs400Trans_Status_tbl'. [SQLSTATE 01000]
There are 7 rows in 1 pages for object
'meta_MetaControlAs400Trans_Status_tbl'. [SQLSTATE 01000]
DBCC results for 'AGPODF_tbl'. [SQLSTATE 01000]
There are 531337 rows in 6561 pages for object 'AGPODF_tbl'. [SQLSTATE
01000]
DBCC results for 'import_pfg_LOCDMAST'. [SQLSTATE 01000]
There are 3300 rows in 27 pages for object 'import_pfg_LOCDMAST'.
[SQLSTATE 01000]
DBCC results for 'AHPOHF_tbl'. [SQLSTATE 01000]
There are 68367 rows in 732 pages for object 'AHPOHF_tbl'. [SQLSTATE 01000]
DBCC results for 'import_pfg_LOCMAST'. [SQLSTATE 01000]
There are 42331 rows in 321 pages for object 'import_pfg_LOCMAST'.
[SQLSTATE 01000]
DBCC results for 'ajffctlf_tbl'. [SQLSTATE 01000]
There are 16194 rows in 443 pages for object 'ajffctlf_tbl'. [SQLSTATE
01000]
DBCC results for 'import_pfg_LOCRMAST'. [SQLSTATE 01000]
There are 3210 rows in 26 pages for object 'import_pfg_LOCRMAST'.
[SQLSTATE 01000]
DBCC results for 'FM03MJF'. [SQLSTATE 01000]
There are 3505 rows in 23 pages for object 'FM03MJF'. [SQLSTATE 01000]
DBCC results for 'meta_control_AS400_trans'. [SQLSTATE 01000]
There are 112799 rows in 1849 pages for object
'meta_control_AS400_trans'. [SQLSTATE 01000]
DBCC results for 'meta_logging_AS400_trans'. [SQLSTATE 01000]
There are 18121 rows in 154 pages for object 'meta_logging_AS400_trans'.
[SQLSTATE 01000]
DBCC results for 'sys_time'. [SQLSTATE 01000]
There are 0 rows in 0 pages for object 'sys_time'. [SQLSTATE 01000]
DBCC results for 'sys_time_fiscal'. [SQLSTATE 01000]
There are 0 rows in 0 pages for object 'sys_time_fiscal'. [SQLSTATE 01000]
DBCC results for 'meta_certification_history'. [SQLSTATE 01000]
There are 49337 rows in 537 pages for object
'meta_certification_history'. [SQLSTATE 01000]
DBCC results for 'meta_certification_current'. [SQLSTATE 01000]
There are 400 rows in 17 pages for object 'meta_certification_current'.
[SQLSTATE 01000]
DBCC results for 'meta_table_types_tbl'. [SQLSTATE 01000]
There are 4 rows in 1 pages for object 'meta_table_types_tbl'. [SQLSTATE
01000]
DBCC results for 'dtproperties'. [SQLSTATE 01000]
There are 28 rows in 1 pages for object 'dtproperties'. [SQLSTATE 01000]
DBCC results for 'meta_control_AJ_import_tbl'. [SQLSTATE 01000]
There are 8 rows in 4 pages for object 'meta_control_AJ_import_tbl'.
[SQLSTATE 01000]
Msg 8928, Sev 16: Object ID 880722190, index ID 0: Page (3:391967) could
not be processed. See other errors for details. [SQLSTATE 42000]
Msg 2536, Sev 16: DBCC results for 'import_opco_AESLDF_2001'. [SQLSTATE
01000]
Msg 2593, Sev 16: There are 18047893 rows in 2255990 pages for object
'import_opco_AESLDF_2001'. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'maxdayend'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 1739 rows in 6 pages for object 'maxdayend'.
[SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'import_opco_AESLDF_2002'. [SQLSTATE
01000]
Msg 2593, Sev 16: There are 39724816 rows in 5062006 pages for object
'import_opco_AESLDF_2002'. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'import_opco_AESLDF_2003'. [SQLSTATE
01000]
Msg 2593, Sev 16: There are 51367587 rows in 7716283 pages for object
'import_opco_AESLDF_2003'. [SQLSTATE 01000]
Msg 8990, Sev 16: CHECKDB found 0 allocation errors and 1 consistency
errors in table 'import_opco_AESLDF_2003' (object ID 880722190).
[SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'import_opco_AESLDF_default'.
[SQLSTATE 01000]
Msg 2593, Sev 16: There are 178 rows in 23 pages for object
'import_opco_AESLDF_default'. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'sf_opco_item_temp'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 18025657 rows in 559636 pages for object
'sf_opco_item_temp'. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'meta_operation'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 7 rows in 1 pages for object
'meta_operation'. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'meta_control_as400_trans_02'.
[SQLSTATE 01000]
Msg 2593, Sev 16: There are 404306 rows in 2230 pages for object
'meta_control_as400_trans_02'. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'CLCLSF'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 18710 rows in 135 pages for object 'CLCLSF'.
[SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'CSCHNF'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 67202 rows in 505 pages for object 'CSCHNF'.
[SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'CSFRCF'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 619 rows in 9 pages for object 'CSFRCF'.
[SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'CSGROUPD'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 357624 rows in 2430 pages for object
'CSGROUPD'. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'CSGROUPH'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 167472 rows in 1173 pages for object
'CSGROUPH'. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'ITITMF'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 29342780 rows in 846483 pages for object
'ITITMF'. [SQLSTATE 01000]
DBCC results for 'MIT01F'. [SQLSTATE 01000]
There are 2158317 rows in 8969 pages for object 'MIT01F'. [SQLSTATE 01000]
DBCC results for 'PBPBHF'. [SQLSTATE 01000]
There are 207741 rows in 1472 pages for object 'PBPBHF'. [SQLSTATE 01000]
DBCC results for 'SLSLSF'. [SQLSTATE 01000]
There are 299042 rows in 2253 pages for object 'SLSLSF'. [SQLSTATE 01000]
DBCC results for 'VNVNDF'. [SQLSTATE 01000]
There are 2085401 rows in 15823 pages for object 'VNVNDF'. [SQLSTATE 01000]
DBCC results for 'CSCSTF2'. [SQLSTATE 01000]
There are 1731565 rows in 6578 pages for object 'CSCSTF2'. [SQLSTATE 01000]
DBCC results for 'INVREGF'. [SQLSTATE 01000]
There are 386374 rows in 3488 pages for object 'INVREGF'. [SQLSTATE 01000]
DBCC results for 'OCOSCF'. [SQLSTATE 01000]
There are 0 rows in 0 pages for object 'OCOSCF'. [SQLSTATE 01000]
DBCC results for 'RMRTMF'. [SQLSTATE 01000]
There are 0 rows in 0 pages for object 'RMRTMF'. [SQLSTATE 01000]
DBCC results for 'ITBAAF'. [SQLSTATE 01000]
There are 70680533 rows in 627912 pages for object 'ITBAAF'. [SQLSTATE
01000]
DBCC results for 'STPCNTF'. [SQLSTATE 01000]
There are 7217728 rows in 44833 pages for object 'STPCNTF'. [SQLSTATE 01000]
DBCC results for 'meta_control_opco_info'. [SQLSTATE 01000]
There are 18 rows in 1 pages for object 'meta_control_opco_info'.
[SQLSTATE 01000]
DBCC results for 'AIDEHF_backup'. [SQLSTATE 01000]
There are 5553 rows in 206 pages for object 'AIDEHF_backup'. [SQLSTATE
01000]
DBCC results for 'MostCurrentVersionDayEnd'. [SQLSTATE 01000]
There are 1 rows in 1 pages for object 'MostCurrentVersionDayEnd'.
[SQLSTATE 01000]
DBCC results for 'RecordsMostCurrentLastSoldDate'. [SQLSTATE 01000]
There are 18863 rows in 177 pages for object
'RecordsMostCurrentLastSoldDate'. [SQLSTATE 01000]
DBCC results for 'meta_control_table_info'. [SQLSTATE 01000]
There are 18 rows in 1 pages for object 'meta_control_table_info'.
[SQLSTATE 01000]
DBCC results for 'cd_attribute'. [SQLSTATE 01000]
There are 0 rows in 0 pages for object 'cd_attribute'. [SQLSTATE 01000]
DBCC results for 'cd_attribute_item_xref'. [SQLSTATE 01000]
There are 0 rows in 0 pages for object 'cd_attribute_item_xref'.
[SQLSTATE 01000]
DBCC results for 'RCTYPF'. [SQLSTATE 01000]
There are 4 rows in 1 pages for object 'RCTYPF'. [SQLSTATE 01000]
DBCC results for 'cd_attribute_type'. [SQLSTATE 01000]
There are 51 rows in 1 pages for object 'cd_attribute_type'. [SQLSTATE
01000]
DBCC results for 'cd_attribute_type_xref'. [SQLSTATE 01000]
There are 2 rows in 1 pages for object 'cd_attribute_type_xref'.
[SQLSTATE 01000]
DBCC results for 'CSRECAP'. [SQLSTATE 01000]
There are 6500447 rows in 73039 pages for object 'CSRECAP'. [SQLSTATE 01000]
DBCC results for 'cd_base_item'. [SQLSTATE 01000]
There are 5492062 rows in 458052 pages for object 'cd_base_item'.
[SQLSTATE 01000]
DBCC results for 'MasterDeliveryInfo'. [SQLSTATE 01000]
There are 275 rows in 2 pages for object 'MasterDeliveryInfo'. [SQLSTATE
01000]
DBCC results for 'CSCSTF'. [SQLSTATE 01000]
There are 15585869 rows in 833613 pages for object 'CSCSTF'. [SQLSTATE
01000]
DBCC results for 'cd_brand'. [SQLSTATE 01000]
There are 220167 rows in 2489 pages for object 'cd_brand'. [SQLSTATE 01000]
DBCC results for 'FinalDeliveryInfo'. [SQLSTATE 01000]
There are 275 rows in 2 pages for object 'FinalDeliveryInfo'. [SQLSTATE
01000]
DBCC results for 'RCRECF'. [SQLSTATE 01000]
There are 19468099 rows in 150916 pages for object 'RCRECF'. [SQLSTATE
01000]
DBCC results for 'cd_brand_type'. [SQLSTATE 01000]
There are 328 rows in 5 pages for object 'cd_brand_type'. [SQLSTATE 01000]
DBCC results for 'DECTL02F'. [SQLSTATE 01000]
There are 1252068 rows in 24082 pages for object 'DECTL02F'. [SQLSTATE
01000]
DBCC results for 'cd_category'. [SQLSTATE 01000]
There are 1961 rows in 23 pages for object 'cd_category'. [SQLSTATE 01000]
DBCC results for 'cd_cop'. [SQLSTATE 01000]
There are 347 rows in 6 pages for object 'cd_cop'. [SQLSTATE 01000]
DBCC results for 'cd_item'. [SQLSTATE 01000]
There are 2878151 rows in 33269 pages for object 'cd_item'. [SQLSTATE 01000]
DBCC results for 'cd_item_xref'. [SQLSTATE 01000]
There are 32862699 rows in 119501 pages for object 'cd_item_xref'.
[SQLSTATE 01000]
DBCC results for 'cd_packer_item'. [SQLSTATE 01000]
There are 2875655 rows in 45046 pages for object 'cd_packer_item'.
[SQLSTATE 01000]
DBCC results for 'import_opco_AESLDF_2004'. [SQLSTATE 01000]
There are 12815617 rows in 2110584 pages for object
'import_opco_AESLDF_2004'. [SQLSTATE 01000]
DBCC results for 'cd_size'. [SQLSTATE 01000]
There are 1678 rows in 10 pages for object 'cd_size'. [SQLSTATE 01000]
DBCC results for 'cd_subcategory'. [SQLSTATE 01000]
There are 5449 rows in 63 pages for object 'cd_subcategory'. [SQLSTATE
01000]
DBCC results for 'cd_supplier'. [SQLSTATE 01000]
There are 274329 rows in 2633 pages for object 'cd_supplier'. [SQLSTATE
01000]
DBCC results for 'TETRTF'. [SQLSTATE 01000]
There are 4302 rows in 29 pages for object 'TETRTF'. [SQLSTATE 01000]
DBCC results for 'cd_synonym'. [SQLSTATE 01000]
There are 0 rows in 0 pages for object 'cd_synonym'. [SQLSTATE 01000]
DBCC results for 'SGSMGF'. [SQLSTATE 01000]
There are 5021 rows in 35 pages for object 'SGSMGF'. [SQLSTATE 01000]
DBCC results for 'meta_control_AI_import_tbl'. [SQLSTATE 01000]
There are 0 rows in 4 pages for object 'meta_control_AI_import_tbl'.
[SQLSTATE 01000]
DBCC results for 'cd_temp_zone'. [SQLSTATE 01000]
There are 204 rows in 2 pages for object 'cd_temp_zone'. [SQLSTATE 01000]
DBCC results for 'ajffctlf_holding_tbl'. [SQLSTATE 01000]
There are 0 rows in 1 pages for object 'ajffctlf_holding_tbl'. [SQLSTATE
01000]
DBCC results for 'cd_vendor_xref'. [SQLSTATE 01000]
There are 739871 rows in 3866 pages for object 'cd_vendor_xref'.
[SQLSTATE 01000]
DBCC results for 'cd_word'. [SQLSTATE 01000]
There are 0 rows in 0 pages for object 'cd_word'. [SQLSTATE 01000]
DBCC results for 'meta_logging_stage_errors'. [SQLSTATE 01000]
There are 0 rows in 0 pages for object 'meta_logging_stage_errors'.
[SQLSTATE 01000]
DBCC results for 'AIDEHF_tbl'. [SQLSTATE 01000]
There are 1060 rows in 70 pages for object 'AIDEHF_tbl'. [SQLSTATE 01000]
DBCC results for 'AKDFCTLF'. [SQLSTATE 01000]
There are 61 rows in 1 pages for object 'AKDFCTLF'. [SQLSTATE 01000]
DBCC results for 'AIDEHF_holding_tbl'. [SQLSTATE 01000]
There are 0 rows in 1 pages for object 'AIDEHF_holding_tbl'. [SQLSTATE
01000]
DBCC results for 'meta_control_AIDEHF_errors_tbl'. [SQLSTATE 01000]
There are 0 rows in 0 pages for object 'meta_control_AIDEHF_errors_tbl'.
[SQLSTATE 01000]
CHECKDB found 0 allocation errors and 2 consistency errors in database
'stage'. [SQLSTATE 01000]
repair_allow_data_loss is the minimum repair level for the errors found
by DBCC CHECKDB (stage ). [SQLSTATE 01000]
DBCC execution completed. If DBCC printed error messages, contact your
system administrator. [SQLSTATE 01000]|||The page is corrupt (bad page id) and won't be processed. Depending on how
corrupt the page header is you may or may not be able to work out which
object/index it's from, but that's beyond the scope of this forum. I suspect
its a data page in a heap as there are no b-tree linkage errors and no
errors that would suggest its an IAM page. That being the case, the repair
is to delete the page (and hence all the data on the page) - that's why the
repair option is called REPAIR_ALLOW_DATA_LOSS. Given that it's probably a
heap data page, there's no easy way to work out what data will be deleted.
Your best course of action is to restore from your most recent backup and
then restore your log backups to the point of failure. Failing that, you'll
have to run repair. I suggest you do root-cause analysis to find out how
this happened - look in your NT event logs and the SQL errorlog for IO
subsystem errors.
I'd further suggest that you call Product Support to help you with this if
you're uncomfortable with any of the above.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"sfibich" <sfibich@.pfgc.com> wrote in message
news:eQT7#l5YEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Paul S Randal [MS] wrote:
> > Please post the exact output from CHECKDB.
> >
> There is an error at the very top, this is the one I am refereing to.
> The error in the middle relates to a particular table that I can deal
with.
> Job 'DBCC_checkdb' : Step 7, 'dbcc checkdb stage' : Began Executing
> 2004-07-06 09:12:59
> Msg 8909, Sev 16: Table error: Object ID 0, index ID 0, page ID
> (3:391967). The PageId in the page header = (0:24). [SQLSTATE 42000]
> Msg 2536, Sev 16: DBCC results for 'stage'. [SQLSTATE 01000]
> Msg 8954, Sev 16: CHECKDB found 0 allocation errors and 1 consistency
> errors not associated with any single object. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'sysobjects'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 329 rows in 7 pages for object 'sysobjects'.
> [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'sysindexes'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 666 rows in 37 pages for object
> 'sysindexes'. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'syscolumns'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 3400 rows in 78 pages for object
> 'syscolumns'. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'systypes'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 26 rows in 1 pages for object 'systypes'.
> [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'syscomments'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 296 rows in 81 pages for object
> 'syscomments'. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'sysfiles1'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 7 rows in 1 pages for object 'sysfiles1'.
> [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'syspermissions'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 52 rows in 1 pages for object
> 'syspermissions'. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'sysusers'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 18 rows in 1 pages for object 'sysusers'.
> [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'sysproperties'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 0 rows in 0 pages for object
> 'sysproperties'. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'sysdepends'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 2423 rows in 15 pages for object
> 'sysdepends'. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'sysreferences'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 3 rows in 1 pages for object
> 'sysreferences'. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'sysfulltextcatalogs'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 0 rows in 0 pages for object
> 'sysfulltextcatalogs'. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'sysfulltextnotify'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 0 rows in 0 pages for object
> 'sysfulltextnotify'. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'sysfilegroups'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 6 rows in 1 pages for object
> 'sysfilegroups'. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'meta_control_missing_day_ends_tbl'.
> [SQLSTATE 01000]
> There are 0 rows in 0 pages for object
> 'meta_control_missing_day_ends_tbl'. [SQLSTATE 01000]
> DBCC results for 'import_opco_AFSLHF'. [SQLSTATE 01000]
> There are 6442240 rows in 715814 pages for object 'import_opco_AFSLHF'.
> [SQLSTATE 01000]
> DBCC results for 'import_opco_AGPODF'. [SQLSTATE 01000]
> There are 4601233 rows in 678964 pages for object 'import_opco_AGPODF'.
> [SQLSTATE 01000]
> DBCC results for 'BRBRDF'. [SQLSTATE 01000]
> There are 0 rows in 0 pages for object 'BRBRDF'. [SQLSTATE 01000]
> DBCC results for 'import_opco_AHPOHF'. [SQLSTATE 01000]
> There are 592812 rows in 98813 pages for object 'import_opco_AHPOHF'.
> [SQLSTATE 01000]
> DBCC results for 'CR01F'. [SQLSTATE 01000]
> There are 1570 rows in 11 pages for object 'CR01F'. [SQLSTATE 01000]
> DBCC results for 'XXX_import_pfg_CMOTYPF'. [SQLSTATE 01000]
> There are 65576 rows in 227 pages for object 'XXX_import_pfg_CMOTYPF'.
> [SQLSTATE 01000]
> DBCC results for 'XXX_import_pfg_CMPTYPF'. [SQLSTATE 01000]
> There are 5415 rows in 33 pages for object 'XXX_import_pfg_CMPTYPF'.
> [SQLSTATE 01000]
> DBCC results for 'TYTYPF'. [SQLSTATE 01000]
> There are 20800 rows in 138 pages for object 'TYTYPF'. [SQLSTATE 01000]
> DBCC results for 'XXX_import_pfg_CTOTYPF'. [SQLSTATE 01000]
> There are 340193 rows in 1516 pages for object 'XXX_import_pfg_CTOTYPF'.
> [SQLSTATE 01000]
> DBCC results for 'meta_AJFFCTLF_Status_tbl'. [SQLSTATE 01000]
> There are 5 rows in 1 pages for object 'meta_AJFFCTLF_Status_tbl'.
> [SQLSTATE 01000]
> DBCC results for 'AESLDF_tbl'. [SQLSTATE 01000]
> There are 14807362 rows in 227806 pages for object 'AESLDF_tbl'.
> [SQLSTATE 01000]
> DBCC results for 'XXX_import_pfg_CTPTYPF'. [SQLSTATE 01000]
> There are 35448 rows in 229 pages for object 'XXX_import_pfg_CTPTYPF'.
> [SQLSTATE 01000]
> DBCC results for 'AFSLHF_tbl'. [SQLSTATE 01000]
> There are 751636 rows in 11219 pages for object 'AFSLHF_tbl'. [SQLSTATE
> 01000]
> DBCC results for 'meta_MetaControlAs400Trans_Status_tbl'. [SQLSTATE 01000]
> There are 7 rows in 1 pages for object
> 'meta_MetaControlAs400Trans_Status_tbl'. [SQLSTATE 01000]
> DBCC results for 'AGPODF_tbl'. [SQLSTATE 01000]
> There are 531337 rows in 6561 pages for object 'AGPODF_tbl'. [SQLSTATE
> 01000]
> DBCC results for 'import_pfg_LOCDMAST'. [SQLSTATE 01000]
> There are 3300 rows in 27 pages for object 'import_pfg_LOCDMAST'.
> [SQLSTATE 01000]
> DBCC results for 'AHPOHF_tbl'. [SQLSTATE 01000]
> There are 68367 rows in 732 pages for object 'AHPOHF_tbl'. [SQLSTATE
01000]
> DBCC results for 'import_pfg_LOCMAST'. [SQLSTATE 01000]
> There are 42331 rows in 321 pages for object 'import_pfg_LOCMAST'.
> [SQLSTATE 01000]
> DBCC results for 'ajffctlf_tbl'. [SQLSTATE 01000]
> There are 16194 rows in 443 pages for object 'ajffctlf_tbl'. [SQLSTATE
> 01000]
> DBCC results for 'import_pfg_LOCRMAST'. [SQLSTATE 01000]
> There are 3210 rows in 26 pages for object 'import_pfg_LOCRMAST'.
> [SQLSTATE 01000]
> DBCC results for 'FM03MJF'. [SQLSTATE 01000]
> There are 3505 rows in 23 pages for object 'FM03MJF'. [SQLSTATE 01000]
> DBCC results for 'meta_control_AS400_trans'. [SQLSTATE 01000]
> There are 112799 rows in 1849 pages for object
> 'meta_control_AS400_trans'. [SQLSTATE 01000]
> DBCC results for 'meta_logging_AS400_trans'. [SQLSTATE 01000]
> There are 18121 rows in 154 pages for object 'meta_logging_AS400_trans'.
> [SQLSTATE 01000]
> DBCC results for 'sys_time'. [SQLSTATE 01000]
> There are 0 rows in 0 pages for object 'sys_time'. [SQLSTATE 01000]
> DBCC results for 'sys_time_fiscal'. [SQLSTATE 01000]
> There are 0 rows in 0 pages for object 'sys_time_fiscal'. [SQLSTATE 01000]
> DBCC results for 'meta_certification_history'. [SQLSTATE 01000]
> There are 49337 rows in 537 pages for object
> 'meta_certification_history'. [SQLSTATE 01000]
> DBCC results for 'meta_certification_current'. [SQLSTATE 01000]
> There are 400 rows in 17 pages for object 'meta_certification_current'.
> [SQLSTATE 01000]
> DBCC results for 'meta_table_types_tbl'. [SQLSTATE 01000]
> There are 4 rows in 1 pages for object 'meta_table_types_tbl'. [SQLSTATE
> 01000]
> DBCC results for 'dtproperties'. [SQLSTATE 01000]
> There are 28 rows in 1 pages for object 'dtproperties'. [SQLSTATE 01000]
> DBCC results for 'meta_control_AJ_import_tbl'. [SQLSTATE 01000]
> There are 8 rows in 4 pages for object 'meta_control_AJ_import_tbl'.
> [SQLSTATE 01000]
> Msg 8928, Sev 16: Object ID 880722190, index ID 0: Page (3:391967) could
> not be processed. See other errors for details. [SQLSTATE 42000]
> Msg 2536, Sev 16: DBCC results for 'import_opco_AESLDF_2001'. [SQLSTATE
> 01000]
> Msg 2593, Sev 16: There are 18047893 rows in 2255990 pages for object
> 'import_opco_AESLDF_2001'. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'maxdayend'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 1739 rows in 6 pages for object 'maxdayend'.
> [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'import_opco_AESLDF_2002'. [SQLSTATE
> 01000]
> Msg 2593, Sev 16: There are 39724816 rows in 5062006 pages for object
> 'import_opco_AESLDF_2002'. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'import_opco_AESLDF_2003'. [SQLSTATE
> 01000]
> Msg 2593, Sev 16: There are 51367587 rows in 7716283 pages for object
> 'import_opco_AESLDF_2003'. [SQLSTATE 01000]
> Msg 8990, Sev 16: CHECKDB found 0 allocation errors and 1 consistency
> errors in table 'import_opco_AESLDF_2003' (object ID 880722190).
> [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'import_opco_AESLDF_default'.
> [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 178 rows in 23 pages for object
> 'import_opco_AESLDF_default'. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'sf_opco_item_temp'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 18025657 rows in 559636 pages for object
> 'sf_opco_item_temp'. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'meta_operation'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 7 rows in 1 pages for object
> 'meta_operation'. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'meta_control_as400_trans_02'.
> [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 404306 rows in 2230 pages for object
> 'meta_control_as400_trans_02'. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'CLCLSF'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 18710 rows in 135 pages for object 'CLCLSF'.
> [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'CSCHNF'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 67202 rows in 505 pages for object 'CSCHNF'.
> [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'CSFRCF'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 619 rows in 9 pages for object 'CSFRCF'.
> [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'CSGROUPD'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 357624 rows in 2430 pages for object
> 'CSGROUPD'. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'CSGROUPH'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 167472 rows in 1173 pages for object
> 'CSGROUPH'. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'ITITMF'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 29342780 rows in 846483 pages for object
> 'ITITMF'. [SQLSTATE 01000]
> DBCC results for 'MIT01F'. [SQLSTATE 01000]
> There are 2158317 rows in 8969 pages for object 'MIT01F'. [SQLSTATE 01000]
> DBCC results for 'PBPBHF'. [SQLSTATE 01000]
> There are 207741 rows in 1472 pages for object 'PBPBHF'. [SQLSTATE 01000]
> DBCC results for 'SLSLSF'. [SQLSTATE 01000]
> There are 299042 rows in 2253 pages for object 'SLSLSF'. [SQLSTATE 01000]
> DBCC results for 'VNVNDF'. [SQLSTATE 01000]
> There are 2085401 rows in 15823 pages for object 'VNVNDF'. [SQLSTATE
01000]
> DBCC results for 'CSCSTF2'. [SQLSTATE 01000]
> There are 1731565 rows in 6578 pages for object 'CSCSTF2'. [SQLSTATE
01000]
> DBCC results for 'INVREGF'. [SQLSTATE 01000]
> There are 386374 rows in 3488 pages for object 'INVREGF'. [SQLSTATE 01000]
> DBCC results for 'OCOSCF'. [SQLSTATE 01000]
> There are 0 rows in 0 pages for object 'OCOSCF'. [SQLSTATE 01000]
> DBCC results for 'RMRTMF'. [SQLSTATE 01000]
> There are 0 rows in 0 pages for object 'RMRTMF'. [SQLSTATE 01000]
> DBCC results for 'ITBAAF'. [SQLSTATE 01000]
> There are 70680533 rows in 627912 pages for object 'ITBAAF'. [SQLSTATE
> 01000]
> DBCC results for 'STPCNTF'. [SQLSTATE 01000]
> There are 7217728 rows in 44833 pages for object 'STPCNTF'. [SQLSTATE
01000]
> DBCC results for 'meta_control_opco_info'. [SQLSTATE 01000]
> There are 18 rows in 1 pages for object 'meta_control_opco_info'.
> [SQLSTATE 01000]
> DBCC results for 'AIDEHF_backup'. [SQLSTATE 01000]
> There are 5553 rows in 206 pages for object 'AIDEHF_backup'. [SQLSTATE
> 01000]
> DBCC results for 'MostCurrentVersionDayEnd'. [SQLSTATE 01000]
> There are 1 rows in 1 pages for object 'MostCurrentVersionDayEnd'.
> [SQLSTATE 01000]
> DBCC results for 'RecordsMostCurrentLastSoldDate'. [SQLSTATE 01000]
> There are 18863 rows in 177 pages for object
> 'RecordsMostCurrentLastSoldDate'. [SQLSTATE 01000]
> DBCC results for 'meta_control_table_info'. [SQLSTATE 01000]
> There are 18 rows in 1 pages for object 'meta_control_table_info'.
> [SQLSTATE 01000]
> DBCC results for 'cd_attribute'. [SQLSTATE 01000]
> There are 0 rows in 0 pages for object 'cd_attribute'. [SQLSTATE 01000]
> DBCC results for 'cd_attribute_item_xref'. [SQLSTATE 01000]
> There are 0 rows in 0 pages for object 'cd_attribute_item_xref'.
> [SQLSTATE 01000]
> DBCC results for 'RCTYPF'. [SQLSTATE 01000]
> There are 4 rows in 1 pages for object 'RCTYPF'. [SQLSTATE 01000]
> DBCC results for 'cd_attribute_type'. [SQLSTATE 01000]
> There are 51 rows in 1 pages for object 'cd_attribute_type'. [SQLSTATE
> 01000]
> DBCC results for 'cd_attribute_type_xref'. [SQLSTATE 01000]
> There are 2 rows in 1 pages for object 'cd_attribute_type_xref'.
> [SQLSTATE 01000]
> DBCC results for 'CSRECAP'. [SQLSTATE 01000]
> There are 6500447 rows in 73039 pages for object 'CSRECAP'. [SQLSTATE
01000]
> DBCC results for 'cd_base_item'. [SQLSTATE 01000]
> There are 5492062 rows in 458052 pages for object 'cd_base_item'.
> [SQLSTATE 01000]
> DBCC results for 'MasterDeliveryInfo'. [SQLSTATE 01000]
> There are 275 rows in 2 pages for object 'MasterDeliveryInfo'. [SQLSTATE
> 01000]
> DBCC results for 'CSCSTF'. [SQLSTATE 01000]
> There are 15585869 rows in 833613 pages for object 'CSCSTF'. [SQLSTATE
> 01000]
> DBCC results for 'cd_brand'. [SQLSTATE 01000]
> There are 220167 rows in 2489 pages for object 'cd_brand'. [SQLSTATE
01000]
> DBCC results for 'FinalDeliveryInfo'. [SQLSTATE 01000]
> There are 275 rows in 2 pages for object 'FinalDeliveryInfo'. [SQLSTATE
> 01000]
> DBCC results for 'RCRECF'. [SQLSTATE 01000]
> There are 19468099 rows in 150916 pages for object 'RCRECF'. [SQLSTATE
> 01000]
> DBCC results for 'cd_brand_type'. [SQLSTATE 01000]
> There are 328 rows in 5 pages for object 'cd_brand_type'. [SQLSTATE 01000]
> DBCC results for 'DECTL02F'. [SQLSTATE 01000]
> There are 1252068 rows in 24082 pages for object 'DECTL02F'. [SQLSTATE
> 01000]
> DBCC results for 'cd_category'. [SQLSTATE 01000]
> There are 1961 rows in 23 pages for object 'cd_category'. [SQLSTATE 01000]
> DBCC results for 'cd_cop'. [SQLSTATE 01000]
> There are 347 rows in 6 pages for object 'cd_cop'. [SQLSTATE 01000]
> DBCC results for 'cd_item'. [SQLSTATE 01000]
> There are 2878151 rows in 33269 pages for object 'cd_item'. [SQLSTATE
01000]
> DBCC results for 'cd_item_xref'. [SQLSTATE 01000]
> There are 32862699 rows in 119501 pages for object 'cd_item_xref'.
> [SQLSTATE 01000]
> DBCC results for 'cd_packer_item'. [SQLSTATE 01000]
> There are 2875655 rows in 45046 pages for object 'cd_packer_item'.
> [SQLSTATE 01000]
> DBCC results for 'import_opco_AESLDF_2004'. [SQLSTATE 01000]
> There are 12815617 rows in 2110584 pages for object
> 'import_opco_AESLDF_2004'. [SQLSTATE 01000]
> DBCC results for 'cd_size'. [SQLSTATE 01000]
> There are 1678 rows in 10 pages for object 'cd_size'. [SQLSTATE 01000]
> DBCC results for 'cd_subcategory'. [SQLSTATE 01000]
> There are 5449 rows in 63 pages for object 'cd_subcategory'. [SQLSTATE
> 01000]
> DBCC results for 'cd_supplier'. [SQLSTATE 01000]
> There are 274329 rows in 2633 pages for object 'cd_supplier'. [SQLSTATE
> 01000]
> DBCC results for 'TETRTF'. [SQLSTATE 01000]
> There are 4302 rows in 29 pages for object 'TETRTF'. [SQLSTATE 01000]
> DBCC results for 'cd_synonym'. [SQLSTATE 01000]
> There are 0 rows in 0 pages for object 'cd_synonym'. [SQLSTATE 01000]
> DBCC results for 'SGSMGF'. [SQLSTATE 01000]
> There are 5021 rows in 35 pages for object 'SGSMGF'. [SQLSTATE 01000]
> DBCC results for 'meta_control_AI_import_tbl'. [SQLSTATE 01000]
> There are 0 rows in 4 pages for object 'meta_control_AI_import_tbl'.
> [SQLSTATE 01000]
> DBCC results for 'cd_temp_zone'. [SQLSTATE 01000]
> There are 204 rows in 2 pages for object 'cd_temp_zone'. [SQLSTATE 01000]
> DBCC results for 'ajffctlf_holding_tbl'. [SQLSTATE 01000]
> There are 0 rows in 1 pages for object 'ajffctlf_holding_tbl'. [SQLSTATE
> 01000]
> DBCC results for 'cd_vendor_xref'. [SQLSTATE 01000]
> There are 739871 rows in 3866 pages for object 'cd_vendor_xref'.
> [SQLSTATE 01000]
> DBCC results for 'cd_word'. [SQLSTATE 01000]
> There are 0 rows in 0 pages for object 'cd_word'. [SQLSTATE 01000]
> DBCC results for 'meta_logging_stage_errors'. [SQLSTATE 01000]
> There are 0 rows in 0 pages for object 'meta_logging_stage_errors'.
> [SQLSTATE 01000]
> DBCC results for 'AIDEHF_tbl'. [SQLSTATE 01000]
> There are 1060 rows in 70 pages for object 'AIDEHF_tbl'. [SQLSTATE 01000]
> DBCC results for 'AKDFCTLF'. [SQLSTATE 01000]
> There are 61 rows in 1 pages for object 'AKDFCTLF'. [SQLSTATE 01000]
> DBCC results for 'AIDEHF_holding_tbl'. [SQLSTATE 01000]
> There are 0 rows in 1 pages for object 'AIDEHF_holding_tbl'. [SQLSTATE
> 01000]
> DBCC results for 'meta_control_AIDEHF_errors_tbl'. [SQLSTATE 01000]
> There are 0 rows in 0 pages for object 'meta_control_AIDEHF_errors_tbl'.
> [SQLSTATE 01000]
> CHECKDB found 0 allocation errors and 2 consistency errors in database
> 'stage'. [SQLSTATE 01000]
> repair_allow_data_loss is the minimum repair level for the errors found
> by DBCC CHECKDB (stage ). [SQLSTATE 01000]
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator. [SQLSTATE 01000]
is directly attributal to a table. The second is not attributable to
any single objects. It is my understanding that this second consistency
error is related to an extent that is shared. Is there anyway to see
which objects share this unknown extnet? If I issue a dbcc checkdb
along with a repair allow data loss is there anyway to see what data it
will remove before it does so?
Thanks ahead of time.Please post the exact output from CHECKDB.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"sfibich" <sfibich@.pfgc.com> wrote in message
news:uZDCRS3YEHA.644@.tk2msftngp13.phx.gbl...
> I have a dbcc checkdb returning that I have two consistency errors. One
> is directly attributal to a table. The second is not attributable to
> any single objects. It is my understanding that this second consistency
> error is related to an extent that is shared. Is there anyway to see
> which objects share this unknown extnet? If I issue a dbcc checkdb
> along with a repair allow data loss is there anyway to see what data it
> will remove before it does so?
> Thanks ahead of time.|||Paul S Randal [MS] wrote:
> Please post the exact output from CHECKDB.
>
There is an error at the very top, this is the one I am refereing to.
The error in the middle relates to a particular table that I can deal with.
Job 'DBCC_checkdb' : Step 7, 'dbcc checkdb stage' : Began Executing
2004-07-06 09:12:59
Msg 8909, Sev 16: Table error: Object ID 0, index ID 0, page ID
(3:391967). The PageId in the page header = (0:24). [SQLSTATE 42000]
Msg 2536, Sev 16: DBCC results for 'stage'. [SQLSTATE 01000]
Msg 8954, Sev 16: CHECKDB found 0 allocation errors and 1 consistency
errors not associated with any single object. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'sysobjects'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 329 rows in 7 pages for object 'sysobjects'.
[SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'sysindexes'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 666 rows in 37 pages for object
'sysindexes'. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'syscolumns'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 3400 rows in 78 pages for object
'syscolumns'. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'systypes'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 26 rows in 1 pages for object 'systypes'.
[SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'syscomments'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 296 rows in 81 pages for object
'syscomments'. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'sysfiles1'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 7 rows in 1 pages for object 'sysfiles1'.
[SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'syspermissions'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 52 rows in 1 pages for object
'syspermissions'. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'sysusers'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 18 rows in 1 pages for object 'sysusers'.
[SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'sysproperties'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 0 rows in 0 pages for object
'sysproperties'. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'sysdepends'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 2423 rows in 15 pages for object
'sysdepends'. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'sysreferences'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 3 rows in 1 pages for object
'sysreferences'. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'sysfulltextcatalogs'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 0 rows in 0 pages for object
'sysfulltextcatalogs'. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'sysfulltextnotify'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 0 rows in 0 pages for object
'sysfulltextnotify'. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'sysfilegroups'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 6 rows in 1 pages for object
'sysfilegroups'. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'meta_control_missing_day_ends_tbl'.
[SQLSTATE 01000]
There are 0 rows in 0 pages for object
'meta_control_missing_day_ends_tbl'. [SQLSTATE 01000]
DBCC results for 'import_opco_AFSLHF'. [SQLSTATE 01000]
There are 6442240 rows in 715814 pages for object 'import_opco_AFSLHF'.
[SQLSTATE 01000]
DBCC results for 'import_opco_AGPODF'. [SQLSTATE 01000]
There are 4601233 rows in 678964 pages for object 'import_opco_AGPODF'.
[SQLSTATE 01000]
DBCC results for 'BRBRDF'. [SQLSTATE 01000]
There are 0 rows in 0 pages for object 'BRBRDF'. [SQLSTATE 01000]
DBCC results for 'import_opco_AHPOHF'. [SQLSTATE 01000]
There are 592812 rows in 98813 pages for object 'import_opco_AHPOHF'.
[SQLSTATE 01000]
DBCC results for 'CR01F'. [SQLSTATE 01000]
There are 1570 rows in 11 pages for object 'CR01F'. [SQLSTATE 01000]
DBCC results for 'XXX_import_pfg_CMOTYPF'. [SQLSTATE 01000]
There are 65576 rows in 227 pages for object 'XXX_import_pfg_CMOTYPF'.
[SQLSTATE 01000]
DBCC results for 'XXX_import_pfg_CMPTYPF'. [SQLSTATE 01000]
There are 5415 rows in 33 pages for object 'XXX_import_pfg_CMPTYPF'.
[SQLSTATE 01000]
DBCC results for 'TYTYPF'. [SQLSTATE 01000]
There are 20800 rows in 138 pages for object 'TYTYPF'. [SQLSTATE 01000]
DBCC results for 'XXX_import_pfg_CTOTYPF'. [SQLSTATE 01000]
There are 340193 rows in 1516 pages for object 'XXX_import_pfg_CTOTYPF'.
[SQLSTATE 01000]
DBCC results for 'meta_AJFFCTLF_Status_tbl'. [SQLSTATE 01000]
There are 5 rows in 1 pages for object 'meta_AJFFCTLF_Status_tbl'.
[SQLSTATE 01000]
DBCC results for 'AESLDF_tbl'. [SQLSTATE 01000]
There are 14807362 rows in 227806 pages for object 'AESLDF_tbl'.
[SQLSTATE 01000]
DBCC results for 'XXX_import_pfg_CTPTYPF'. [SQLSTATE 01000]
There are 35448 rows in 229 pages for object 'XXX_import_pfg_CTPTYPF'.
[SQLSTATE 01000]
DBCC results for 'AFSLHF_tbl'. [SQLSTATE 01000]
There are 751636 rows in 11219 pages for object 'AFSLHF_tbl'. [SQLSTATE
01000]
DBCC results for 'meta_MetaControlAs400Trans_Status_tbl'. [SQLSTATE 01000]
There are 7 rows in 1 pages for object
'meta_MetaControlAs400Trans_Status_tbl'. [SQLSTATE 01000]
DBCC results for 'AGPODF_tbl'. [SQLSTATE 01000]
There are 531337 rows in 6561 pages for object 'AGPODF_tbl'. [SQLSTATE
01000]
DBCC results for 'import_pfg_LOCDMAST'. [SQLSTATE 01000]
There are 3300 rows in 27 pages for object 'import_pfg_LOCDMAST'.
[SQLSTATE 01000]
DBCC results for 'AHPOHF_tbl'. [SQLSTATE 01000]
There are 68367 rows in 732 pages for object 'AHPOHF_tbl'. [SQLSTATE 01000]
DBCC results for 'import_pfg_LOCMAST'. [SQLSTATE 01000]
There are 42331 rows in 321 pages for object 'import_pfg_LOCMAST'.
[SQLSTATE 01000]
DBCC results for 'ajffctlf_tbl'. [SQLSTATE 01000]
There are 16194 rows in 443 pages for object 'ajffctlf_tbl'. [SQLSTATE
01000]
DBCC results for 'import_pfg_LOCRMAST'. [SQLSTATE 01000]
There are 3210 rows in 26 pages for object 'import_pfg_LOCRMAST'.
[SQLSTATE 01000]
DBCC results for 'FM03MJF'. [SQLSTATE 01000]
There are 3505 rows in 23 pages for object 'FM03MJF'. [SQLSTATE 01000]
DBCC results for 'meta_control_AS400_trans'. [SQLSTATE 01000]
There are 112799 rows in 1849 pages for object
'meta_control_AS400_trans'. [SQLSTATE 01000]
DBCC results for 'meta_logging_AS400_trans'. [SQLSTATE 01000]
There are 18121 rows in 154 pages for object 'meta_logging_AS400_trans'.
[SQLSTATE 01000]
DBCC results for 'sys_time'. [SQLSTATE 01000]
There are 0 rows in 0 pages for object 'sys_time'. [SQLSTATE 01000]
DBCC results for 'sys_time_fiscal'. [SQLSTATE 01000]
There are 0 rows in 0 pages for object 'sys_time_fiscal'. [SQLSTATE 01000]
DBCC results for 'meta_certification_history'. [SQLSTATE 01000]
There are 49337 rows in 537 pages for object
'meta_certification_history'. [SQLSTATE 01000]
DBCC results for 'meta_certification_current'. [SQLSTATE 01000]
There are 400 rows in 17 pages for object 'meta_certification_current'.
[SQLSTATE 01000]
DBCC results for 'meta_table_types_tbl'. [SQLSTATE 01000]
There are 4 rows in 1 pages for object 'meta_table_types_tbl'. [SQLSTATE
01000]
DBCC results for 'dtproperties'. [SQLSTATE 01000]
There are 28 rows in 1 pages for object 'dtproperties'. [SQLSTATE 01000]
DBCC results for 'meta_control_AJ_import_tbl'. [SQLSTATE 01000]
There are 8 rows in 4 pages for object 'meta_control_AJ_import_tbl'.
[SQLSTATE 01000]
Msg 8928, Sev 16: Object ID 880722190, index ID 0: Page (3:391967) could
not be processed. See other errors for details. [SQLSTATE 42000]
Msg 2536, Sev 16: DBCC results for 'import_opco_AESLDF_2001'. [SQLSTATE
01000]
Msg 2593, Sev 16: There are 18047893 rows in 2255990 pages for object
'import_opco_AESLDF_2001'. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'maxdayend'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 1739 rows in 6 pages for object 'maxdayend'.
[SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'import_opco_AESLDF_2002'. [SQLSTATE
01000]
Msg 2593, Sev 16: There are 39724816 rows in 5062006 pages for object
'import_opco_AESLDF_2002'. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'import_opco_AESLDF_2003'. [SQLSTATE
01000]
Msg 2593, Sev 16: There are 51367587 rows in 7716283 pages for object
'import_opco_AESLDF_2003'. [SQLSTATE 01000]
Msg 8990, Sev 16: CHECKDB found 0 allocation errors and 1 consistency
errors in table 'import_opco_AESLDF_2003' (object ID 880722190).
[SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'import_opco_AESLDF_default'.
[SQLSTATE 01000]
Msg 2593, Sev 16: There are 178 rows in 23 pages for object
'import_opco_AESLDF_default'. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'sf_opco_item_temp'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 18025657 rows in 559636 pages for object
'sf_opco_item_temp'. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'meta_operation'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 7 rows in 1 pages for object
'meta_operation'. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'meta_control_as400_trans_02'.
[SQLSTATE 01000]
Msg 2593, Sev 16: There are 404306 rows in 2230 pages for object
'meta_control_as400_trans_02'. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'CLCLSF'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 18710 rows in 135 pages for object 'CLCLSF'.
[SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'CSCHNF'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 67202 rows in 505 pages for object 'CSCHNF'.
[SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'CSFRCF'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 619 rows in 9 pages for object 'CSFRCF'.
[SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'CSGROUPD'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 357624 rows in 2430 pages for object
'CSGROUPD'. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'CSGROUPH'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 167472 rows in 1173 pages for object
'CSGROUPH'. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'ITITMF'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 29342780 rows in 846483 pages for object
'ITITMF'. [SQLSTATE 01000]
DBCC results for 'MIT01F'. [SQLSTATE 01000]
There are 2158317 rows in 8969 pages for object 'MIT01F'. [SQLSTATE 01000]
DBCC results for 'PBPBHF'. [SQLSTATE 01000]
There are 207741 rows in 1472 pages for object 'PBPBHF'. [SQLSTATE 01000]
DBCC results for 'SLSLSF'. [SQLSTATE 01000]
There are 299042 rows in 2253 pages for object 'SLSLSF'. [SQLSTATE 01000]
DBCC results for 'VNVNDF'. [SQLSTATE 01000]
There are 2085401 rows in 15823 pages for object 'VNVNDF'. [SQLSTATE 01000]
DBCC results for 'CSCSTF2'. [SQLSTATE 01000]
There are 1731565 rows in 6578 pages for object 'CSCSTF2'. [SQLSTATE 01000]
DBCC results for 'INVREGF'. [SQLSTATE 01000]
There are 386374 rows in 3488 pages for object 'INVREGF'. [SQLSTATE 01000]
DBCC results for 'OCOSCF'. [SQLSTATE 01000]
There are 0 rows in 0 pages for object 'OCOSCF'. [SQLSTATE 01000]
DBCC results for 'RMRTMF'. [SQLSTATE 01000]
There are 0 rows in 0 pages for object 'RMRTMF'. [SQLSTATE 01000]
DBCC results for 'ITBAAF'. [SQLSTATE 01000]
There are 70680533 rows in 627912 pages for object 'ITBAAF'. [SQLSTATE
01000]
DBCC results for 'STPCNTF'. [SQLSTATE 01000]
There are 7217728 rows in 44833 pages for object 'STPCNTF'. [SQLSTATE 01000]
DBCC results for 'meta_control_opco_info'. [SQLSTATE 01000]
There are 18 rows in 1 pages for object 'meta_control_opco_info'.
[SQLSTATE 01000]
DBCC results for 'AIDEHF_backup'. [SQLSTATE 01000]
There are 5553 rows in 206 pages for object 'AIDEHF_backup'. [SQLSTATE
01000]
DBCC results for 'MostCurrentVersionDayEnd'. [SQLSTATE 01000]
There are 1 rows in 1 pages for object 'MostCurrentVersionDayEnd'.
[SQLSTATE 01000]
DBCC results for 'RecordsMostCurrentLastSoldDate'. [SQLSTATE 01000]
There are 18863 rows in 177 pages for object
'RecordsMostCurrentLastSoldDate'. [SQLSTATE 01000]
DBCC results for 'meta_control_table_info'. [SQLSTATE 01000]
There are 18 rows in 1 pages for object 'meta_control_table_info'.
[SQLSTATE 01000]
DBCC results for 'cd_attribute'. [SQLSTATE 01000]
There are 0 rows in 0 pages for object 'cd_attribute'. [SQLSTATE 01000]
DBCC results for 'cd_attribute_item_xref'. [SQLSTATE 01000]
There are 0 rows in 0 pages for object 'cd_attribute_item_xref'.
[SQLSTATE 01000]
DBCC results for 'RCTYPF'. [SQLSTATE 01000]
There are 4 rows in 1 pages for object 'RCTYPF'. [SQLSTATE 01000]
DBCC results for 'cd_attribute_type'. [SQLSTATE 01000]
There are 51 rows in 1 pages for object 'cd_attribute_type'. [SQLSTATE
01000]
DBCC results for 'cd_attribute_type_xref'. [SQLSTATE 01000]
There are 2 rows in 1 pages for object 'cd_attribute_type_xref'.
[SQLSTATE 01000]
DBCC results for 'CSRECAP'. [SQLSTATE 01000]
There are 6500447 rows in 73039 pages for object 'CSRECAP'. [SQLSTATE 01000]
DBCC results for 'cd_base_item'. [SQLSTATE 01000]
There are 5492062 rows in 458052 pages for object 'cd_base_item'.
[SQLSTATE 01000]
DBCC results for 'MasterDeliveryInfo'. [SQLSTATE 01000]
There are 275 rows in 2 pages for object 'MasterDeliveryInfo'. [SQLSTATE
01000]
DBCC results for 'CSCSTF'. [SQLSTATE 01000]
There are 15585869 rows in 833613 pages for object 'CSCSTF'. [SQLSTATE
01000]
DBCC results for 'cd_brand'. [SQLSTATE 01000]
There are 220167 rows in 2489 pages for object 'cd_brand'. [SQLSTATE 01000]
DBCC results for 'FinalDeliveryInfo'. [SQLSTATE 01000]
There are 275 rows in 2 pages for object 'FinalDeliveryInfo'. [SQLSTATE
01000]
DBCC results for 'RCRECF'. [SQLSTATE 01000]
There are 19468099 rows in 150916 pages for object 'RCRECF'. [SQLSTATE
01000]
DBCC results for 'cd_brand_type'. [SQLSTATE 01000]
There are 328 rows in 5 pages for object 'cd_brand_type'. [SQLSTATE 01000]
DBCC results for 'DECTL02F'. [SQLSTATE 01000]
There are 1252068 rows in 24082 pages for object 'DECTL02F'. [SQLSTATE
01000]
DBCC results for 'cd_category'. [SQLSTATE 01000]
There are 1961 rows in 23 pages for object 'cd_category'. [SQLSTATE 01000]
DBCC results for 'cd_cop'. [SQLSTATE 01000]
There are 347 rows in 6 pages for object 'cd_cop'. [SQLSTATE 01000]
DBCC results for 'cd_item'. [SQLSTATE 01000]
There are 2878151 rows in 33269 pages for object 'cd_item'. [SQLSTATE 01000]
DBCC results for 'cd_item_xref'. [SQLSTATE 01000]
There are 32862699 rows in 119501 pages for object 'cd_item_xref'.
[SQLSTATE 01000]
DBCC results for 'cd_packer_item'. [SQLSTATE 01000]
There are 2875655 rows in 45046 pages for object 'cd_packer_item'.
[SQLSTATE 01000]
DBCC results for 'import_opco_AESLDF_2004'. [SQLSTATE 01000]
There are 12815617 rows in 2110584 pages for object
'import_opco_AESLDF_2004'. [SQLSTATE 01000]
DBCC results for 'cd_size'. [SQLSTATE 01000]
There are 1678 rows in 10 pages for object 'cd_size'. [SQLSTATE 01000]
DBCC results for 'cd_subcategory'. [SQLSTATE 01000]
There are 5449 rows in 63 pages for object 'cd_subcategory'. [SQLSTATE
01000]
DBCC results for 'cd_supplier'. [SQLSTATE 01000]
There are 274329 rows in 2633 pages for object 'cd_supplier'. [SQLSTATE
01000]
DBCC results for 'TETRTF'. [SQLSTATE 01000]
There are 4302 rows in 29 pages for object 'TETRTF'. [SQLSTATE 01000]
DBCC results for 'cd_synonym'. [SQLSTATE 01000]
There are 0 rows in 0 pages for object 'cd_synonym'. [SQLSTATE 01000]
DBCC results for 'SGSMGF'. [SQLSTATE 01000]
There are 5021 rows in 35 pages for object 'SGSMGF'. [SQLSTATE 01000]
DBCC results for 'meta_control_AI_import_tbl'. [SQLSTATE 01000]
There are 0 rows in 4 pages for object 'meta_control_AI_import_tbl'.
[SQLSTATE 01000]
DBCC results for 'cd_temp_zone'. [SQLSTATE 01000]
There are 204 rows in 2 pages for object 'cd_temp_zone'. [SQLSTATE 01000]
DBCC results for 'ajffctlf_holding_tbl'. [SQLSTATE 01000]
There are 0 rows in 1 pages for object 'ajffctlf_holding_tbl'. [SQLSTATE
01000]
DBCC results for 'cd_vendor_xref'. [SQLSTATE 01000]
There are 739871 rows in 3866 pages for object 'cd_vendor_xref'.
[SQLSTATE 01000]
DBCC results for 'cd_word'. [SQLSTATE 01000]
There are 0 rows in 0 pages for object 'cd_word'. [SQLSTATE 01000]
DBCC results for 'meta_logging_stage_errors'. [SQLSTATE 01000]
There are 0 rows in 0 pages for object 'meta_logging_stage_errors'.
[SQLSTATE 01000]
DBCC results for 'AIDEHF_tbl'. [SQLSTATE 01000]
There are 1060 rows in 70 pages for object 'AIDEHF_tbl'. [SQLSTATE 01000]
DBCC results for 'AKDFCTLF'. [SQLSTATE 01000]
There are 61 rows in 1 pages for object 'AKDFCTLF'. [SQLSTATE 01000]
DBCC results for 'AIDEHF_holding_tbl'. [SQLSTATE 01000]
There are 0 rows in 1 pages for object 'AIDEHF_holding_tbl'. [SQLSTATE
01000]
DBCC results for 'meta_control_AIDEHF_errors_tbl'. [SQLSTATE 01000]
There are 0 rows in 0 pages for object 'meta_control_AIDEHF_errors_tbl'.
[SQLSTATE 01000]
CHECKDB found 0 allocation errors and 2 consistency errors in database
'stage'. [SQLSTATE 01000]
repair_allow_data_loss is the minimum repair level for the errors found
by DBCC CHECKDB (stage ). [SQLSTATE 01000]
DBCC execution completed. If DBCC printed error messages, contact your
system administrator. [SQLSTATE 01000]|||The page is corrupt (bad page id) and won't be processed. Depending on how
corrupt the page header is you may or may not be able to work out which
object/index it's from, but that's beyond the scope of this forum. I suspect
its a data page in a heap as there are no b-tree linkage errors and no
errors that would suggest its an IAM page. That being the case, the repair
is to delete the page (and hence all the data on the page) - that's why the
repair option is called REPAIR_ALLOW_DATA_LOSS. Given that it's probably a
heap data page, there's no easy way to work out what data will be deleted.
Your best course of action is to restore from your most recent backup and
then restore your log backups to the point of failure. Failing that, you'll
have to run repair. I suggest you do root-cause analysis to find out how
this happened - look in your NT event logs and the SQL errorlog for IO
subsystem errors.
I'd further suggest that you call Product Support to help you with this if
you're uncomfortable with any of the above.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"sfibich" <sfibich@.pfgc.com> wrote in message
news:eQT7#l5YEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Paul S Randal [MS] wrote:
> > Please post the exact output from CHECKDB.
> >
> There is an error at the very top, this is the one I am refereing to.
> The error in the middle relates to a particular table that I can deal
with.
> Job 'DBCC_checkdb' : Step 7, 'dbcc checkdb stage' : Began Executing
> 2004-07-06 09:12:59
> Msg 8909, Sev 16: Table error: Object ID 0, index ID 0, page ID
> (3:391967). The PageId in the page header = (0:24). [SQLSTATE 42000]
> Msg 2536, Sev 16: DBCC results for 'stage'. [SQLSTATE 01000]
> Msg 8954, Sev 16: CHECKDB found 0 allocation errors and 1 consistency
> errors not associated with any single object. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'sysobjects'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 329 rows in 7 pages for object 'sysobjects'.
> [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'sysindexes'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 666 rows in 37 pages for object
> 'sysindexes'. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'syscolumns'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 3400 rows in 78 pages for object
> 'syscolumns'. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'systypes'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 26 rows in 1 pages for object 'systypes'.
> [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'syscomments'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 296 rows in 81 pages for object
> 'syscomments'. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'sysfiles1'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 7 rows in 1 pages for object 'sysfiles1'.
> [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'syspermissions'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 52 rows in 1 pages for object
> 'syspermissions'. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'sysusers'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 18 rows in 1 pages for object 'sysusers'.
> [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'sysproperties'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 0 rows in 0 pages for object
> 'sysproperties'. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'sysdepends'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 2423 rows in 15 pages for object
> 'sysdepends'. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'sysreferences'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 3 rows in 1 pages for object
> 'sysreferences'. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'sysfulltextcatalogs'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 0 rows in 0 pages for object
> 'sysfulltextcatalogs'. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'sysfulltextnotify'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 0 rows in 0 pages for object
> 'sysfulltextnotify'. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'sysfilegroups'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 6 rows in 1 pages for object
> 'sysfilegroups'. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'meta_control_missing_day_ends_tbl'.
> [SQLSTATE 01000]
> There are 0 rows in 0 pages for object
> 'meta_control_missing_day_ends_tbl'. [SQLSTATE 01000]
> DBCC results for 'import_opco_AFSLHF'. [SQLSTATE 01000]
> There are 6442240 rows in 715814 pages for object 'import_opco_AFSLHF'.
> [SQLSTATE 01000]
> DBCC results for 'import_opco_AGPODF'. [SQLSTATE 01000]
> There are 4601233 rows in 678964 pages for object 'import_opco_AGPODF'.
> [SQLSTATE 01000]
> DBCC results for 'BRBRDF'. [SQLSTATE 01000]
> There are 0 rows in 0 pages for object 'BRBRDF'. [SQLSTATE 01000]
> DBCC results for 'import_opco_AHPOHF'. [SQLSTATE 01000]
> There are 592812 rows in 98813 pages for object 'import_opco_AHPOHF'.
> [SQLSTATE 01000]
> DBCC results for 'CR01F'. [SQLSTATE 01000]
> There are 1570 rows in 11 pages for object 'CR01F'. [SQLSTATE 01000]
> DBCC results for 'XXX_import_pfg_CMOTYPF'. [SQLSTATE 01000]
> There are 65576 rows in 227 pages for object 'XXX_import_pfg_CMOTYPF'.
> [SQLSTATE 01000]
> DBCC results for 'XXX_import_pfg_CMPTYPF'. [SQLSTATE 01000]
> There are 5415 rows in 33 pages for object 'XXX_import_pfg_CMPTYPF'.
> [SQLSTATE 01000]
> DBCC results for 'TYTYPF'. [SQLSTATE 01000]
> There are 20800 rows in 138 pages for object 'TYTYPF'. [SQLSTATE 01000]
> DBCC results for 'XXX_import_pfg_CTOTYPF'. [SQLSTATE 01000]
> There are 340193 rows in 1516 pages for object 'XXX_import_pfg_CTOTYPF'.
> [SQLSTATE 01000]
> DBCC results for 'meta_AJFFCTLF_Status_tbl'. [SQLSTATE 01000]
> There are 5 rows in 1 pages for object 'meta_AJFFCTLF_Status_tbl'.
> [SQLSTATE 01000]
> DBCC results for 'AESLDF_tbl'. [SQLSTATE 01000]
> There are 14807362 rows in 227806 pages for object 'AESLDF_tbl'.
> [SQLSTATE 01000]
> DBCC results for 'XXX_import_pfg_CTPTYPF'. [SQLSTATE 01000]
> There are 35448 rows in 229 pages for object 'XXX_import_pfg_CTPTYPF'.
> [SQLSTATE 01000]
> DBCC results for 'AFSLHF_tbl'. [SQLSTATE 01000]
> There are 751636 rows in 11219 pages for object 'AFSLHF_tbl'. [SQLSTATE
> 01000]
> DBCC results for 'meta_MetaControlAs400Trans_Status_tbl'. [SQLSTATE 01000]
> There are 7 rows in 1 pages for object
> 'meta_MetaControlAs400Trans_Status_tbl'. [SQLSTATE 01000]
> DBCC results for 'AGPODF_tbl'. [SQLSTATE 01000]
> There are 531337 rows in 6561 pages for object 'AGPODF_tbl'. [SQLSTATE
> 01000]
> DBCC results for 'import_pfg_LOCDMAST'. [SQLSTATE 01000]
> There are 3300 rows in 27 pages for object 'import_pfg_LOCDMAST'.
> [SQLSTATE 01000]
> DBCC results for 'AHPOHF_tbl'. [SQLSTATE 01000]
> There are 68367 rows in 732 pages for object 'AHPOHF_tbl'. [SQLSTATE
01000]
> DBCC results for 'import_pfg_LOCMAST'. [SQLSTATE 01000]
> There are 42331 rows in 321 pages for object 'import_pfg_LOCMAST'.
> [SQLSTATE 01000]
> DBCC results for 'ajffctlf_tbl'. [SQLSTATE 01000]
> There are 16194 rows in 443 pages for object 'ajffctlf_tbl'. [SQLSTATE
> 01000]
> DBCC results for 'import_pfg_LOCRMAST'. [SQLSTATE 01000]
> There are 3210 rows in 26 pages for object 'import_pfg_LOCRMAST'.
> [SQLSTATE 01000]
> DBCC results for 'FM03MJF'. [SQLSTATE 01000]
> There are 3505 rows in 23 pages for object 'FM03MJF'. [SQLSTATE 01000]
> DBCC results for 'meta_control_AS400_trans'. [SQLSTATE 01000]
> There are 112799 rows in 1849 pages for object
> 'meta_control_AS400_trans'. [SQLSTATE 01000]
> DBCC results for 'meta_logging_AS400_trans'. [SQLSTATE 01000]
> There are 18121 rows in 154 pages for object 'meta_logging_AS400_trans'.
> [SQLSTATE 01000]
> DBCC results for 'sys_time'. [SQLSTATE 01000]
> There are 0 rows in 0 pages for object 'sys_time'. [SQLSTATE 01000]
> DBCC results for 'sys_time_fiscal'. [SQLSTATE 01000]
> There are 0 rows in 0 pages for object 'sys_time_fiscal'. [SQLSTATE 01000]
> DBCC results for 'meta_certification_history'. [SQLSTATE 01000]
> There are 49337 rows in 537 pages for object
> 'meta_certification_history'. [SQLSTATE 01000]
> DBCC results for 'meta_certification_current'. [SQLSTATE 01000]
> There are 400 rows in 17 pages for object 'meta_certification_current'.
> [SQLSTATE 01000]
> DBCC results for 'meta_table_types_tbl'. [SQLSTATE 01000]
> There are 4 rows in 1 pages for object 'meta_table_types_tbl'. [SQLSTATE
> 01000]
> DBCC results for 'dtproperties'. [SQLSTATE 01000]
> There are 28 rows in 1 pages for object 'dtproperties'. [SQLSTATE 01000]
> DBCC results for 'meta_control_AJ_import_tbl'. [SQLSTATE 01000]
> There are 8 rows in 4 pages for object 'meta_control_AJ_import_tbl'.
> [SQLSTATE 01000]
> Msg 8928, Sev 16: Object ID 880722190, index ID 0: Page (3:391967) could
> not be processed. See other errors for details. [SQLSTATE 42000]
> Msg 2536, Sev 16: DBCC results for 'import_opco_AESLDF_2001'. [SQLSTATE
> 01000]
> Msg 2593, Sev 16: There are 18047893 rows in 2255990 pages for object
> 'import_opco_AESLDF_2001'. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'maxdayend'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 1739 rows in 6 pages for object 'maxdayend'.
> [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'import_opco_AESLDF_2002'. [SQLSTATE
> 01000]
> Msg 2593, Sev 16: There are 39724816 rows in 5062006 pages for object
> 'import_opco_AESLDF_2002'. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'import_opco_AESLDF_2003'. [SQLSTATE
> 01000]
> Msg 2593, Sev 16: There are 51367587 rows in 7716283 pages for object
> 'import_opco_AESLDF_2003'. [SQLSTATE 01000]
> Msg 8990, Sev 16: CHECKDB found 0 allocation errors and 1 consistency
> errors in table 'import_opco_AESLDF_2003' (object ID 880722190).
> [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'import_opco_AESLDF_default'.
> [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 178 rows in 23 pages for object
> 'import_opco_AESLDF_default'. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'sf_opco_item_temp'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 18025657 rows in 559636 pages for object
> 'sf_opco_item_temp'. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'meta_operation'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 7 rows in 1 pages for object
> 'meta_operation'. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'meta_control_as400_trans_02'.
> [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 404306 rows in 2230 pages for object
> 'meta_control_as400_trans_02'. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'CLCLSF'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 18710 rows in 135 pages for object 'CLCLSF'.
> [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'CSCHNF'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 67202 rows in 505 pages for object 'CSCHNF'.
> [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'CSFRCF'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 619 rows in 9 pages for object 'CSFRCF'.
> [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'CSGROUPD'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 357624 rows in 2430 pages for object
> 'CSGROUPD'. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'CSGROUPH'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 167472 rows in 1173 pages for object
> 'CSGROUPH'. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'ITITMF'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 29342780 rows in 846483 pages for object
> 'ITITMF'. [SQLSTATE 01000]
> DBCC results for 'MIT01F'. [SQLSTATE 01000]
> There are 2158317 rows in 8969 pages for object 'MIT01F'. [SQLSTATE 01000]
> DBCC results for 'PBPBHF'. [SQLSTATE 01000]
> There are 207741 rows in 1472 pages for object 'PBPBHF'. [SQLSTATE 01000]
> DBCC results for 'SLSLSF'. [SQLSTATE 01000]
> There are 299042 rows in 2253 pages for object 'SLSLSF'. [SQLSTATE 01000]
> DBCC results for 'VNVNDF'. [SQLSTATE 01000]
> There are 2085401 rows in 15823 pages for object 'VNVNDF'. [SQLSTATE
01000]
> DBCC results for 'CSCSTF2'. [SQLSTATE 01000]
> There are 1731565 rows in 6578 pages for object 'CSCSTF2'. [SQLSTATE
01000]
> DBCC results for 'INVREGF'. [SQLSTATE 01000]
> There are 386374 rows in 3488 pages for object 'INVREGF'. [SQLSTATE 01000]
> DBCC results for 'OCOSCF'. [SQLSTATE 01000]
> There are 0 rows in 0 pages for object 'OCOSCF'. [SQLSTATE 01000]
> DBCC results for 'RMRTMF'. [SQLSTATE 01000]
> There are 0 rows in 0 pages for object 'RMRTMF'. [SQLSTATE 01000]
> DBCC results for 'ITBAAF'. [SQLSTATE 01000]
> There are 70680533 rows in 627912 pages for object 'ITBAAF'. [SQLSTATE
> 01000]
> DBCC results for 'STPCNTF'. [SQLSTATE 01000]
> There are 7217728 rows in 44833 pages for object 'STPCNTF'. [SQLSTATE
01000]
> DBCC results for 'meta_control_opco_info'. [SQLSTATE 01000]
> There are 18 rows in 1 pages for object 'meta_control_opco_info'.
> [SQLSTATE 01000]
> DBCC results for 'AIDEHF_backup'. [SQLSTATE 01000]
> There are 5553 rows in 206 pages for object 'AIDEHF_backup'. [SQLSTATE
> 01000]
> DBCC results for 'MostCurrentVersionDayEnd'. [SQLSTATE 01000]
> There are 1 rows in 1 pages for object 'MostCurrentVersionDayEnd'.
> [SQLSTATE 01000]
> DBCC results for 'RecordsMostCurrentLastSoldDate'. [SQLSTATE 01000]
> There are 18863 rows in 177 pages for object
> 'RecordsMostCurrentLastSoldDate'. [SQLSTATE 01000]
> DBCC results for 'meta_control_table_info'. [SQLSTATE 01000]
> There are 18 rows in 1 pages for object 'meta_control_table_info'.
> [SQLSTATE 01000]
> DBCC results for 'cd_attribute'. [SQLSTATE 01000]
> There are 0 rows in 0 pages for object 'cd_attribute'. [SQLSTATE 01000]
> DBCC results for 'cd_attribute_item_xref'. [SQLSTATE 01000]
> There are 0 rows in 0 pages for object 'cd_attribute_item_xref'.
> [SQLSTATE 01000]
> DBCC results for 'RCTYPF'. [SQLSTATE 01000]
> There are 4 rows in 1 pages for object 'RCTYPF'. [SQLSTATE 01000]
> DBCC results for 'cd_attribute_type'. [SQLSTATE 01000]
> There are 51 rows in 1 pages for object 'cd_attribute_type'. [SQLSTATE
> 01000]
> DBCC results for 'cd_attribute_type_xref'. [SQLSTATE 01000]
> There are 2 rows in 1 pages for object 'cd_attribute_type_xref'.
> [SQLSTATE 01000]
> DBCC results for 'CSRECAP'. [SQLSTATE 01000]
> There are 6500447 rows in 73039 pages for object 'CSRECAP'. [SQLSTATE
01000]
> DBCC results for 'cd_base_item'. [SQLSTATE 01000]
> There are 5492062 rows in 458052 pages for object 'cd_base_item'.
> [SQLSTATE 01000]
> DBCC results for 'MasterDeliveryInfo'. [SQLSTATE 01000]
> There are 275 rows in 2 pages for object 'MasterDeliveryInfo'. [SQLSTATE
> 01000]
> DBCC results for 'CSCSTF'. [SQLSTATE 01000]
> There are 15585869 rows in 833613 pages for object 'CSCSTF'. [SQLSTATE
> 01000]
> DBCC results for 'cd_brand'. [SQLSTATE 01000]
> There are 220167 rows in 2489 pages for object 'cd_brand'. [SQLSTATE
01000]
> DBCC results for 'FinalDeliveryInfo'. [SQLSTATE 01000]
> There are 275 rows in 2 pages for object 'FinalDeliveryInfo'. [SQLSTATE
> 01000]
> DBCC results for 'RCRECF'. [SQLSTATE 01000]
> There are 19468099 rows in 150916 pages for object 'RCRECF'. [SQLSTATE
> 01000]
> DBCC results for 'cd_brand_type'. [SQLSTATE 01000]
> There are 328 rows in 5 pages for object 'cd_brand_type'. [SQLSTATE 01000]
> DBCC results for 'DECTL02F'. [SQLSTATE 01000]
> There are 1252068 rows in 24082 pages for object 'DECTL02F'. [SQLSTATE
> 01000]
> DBCC results for 'cd_category'. [SQLSTATE 01000]
> There are 1961 rows in 23 pages for object 'cd_category'. [SQLSTATE 01000]
> DBCC results for 'cd_cop'. [SQLSTATE 01000]
> There are 347 rows in 6 pages for object 'cd_cop'. [SQLSTATE 01000]
> DBCC results for 'cd_item'. [SQLSTATE 01000]
> There are 2878151 rows in 33269 pages for object 'cd_item'. [SQLSTATE
01000]
> DBCC results for 'cd_item_xref'. [SQLSTATE 01000]
> There are 32862699 rows in 119501 pages for object 'cd_item_xref'.
> [SQLSTATE 01000]
> DBCC results for 'cd_packer_item'. [SQLSTATE 01000]
> There are 2875655 rows in 45046 pages for object 'cd_packer_item'.
> [SQLSTATE 01000]
> DBCC results for 'import_opco_AESLDF_2004'. [SQLSTATE 01000]
> There are 12815617 rows in 2110584 pages for object
> 'import_opco_AESLDF_2004'. [SQLSTATE 01000]
> DBCC results for 'cd_size'. [SQLSTATE 01000]
> There are 1678 rows in 10 pages for object 'cd_size'. [SQLSTATE 01000]
> DBCC results for 'cd_subcategory'. [SQLSTATE 01000]
> There are 5449 rows in 63 pages for object 'cd_subcategory'. [SQLSTATE
> 01000]
> DBCC results for 'cd_supplier'. [SQLSTATE 01000]
> There are 274329 rows in 2633 pages for object 'cd_supplier'. [SQLSTATE
> 01000]
> DBCC results for 'TETRTF'. [SQLSTATE 01000]
> There are 4302 rows in 29 pages for object 'TETRTF'. [SQLSTATE 01000]
> DBCC results for 'cd_synonym'. [SQLSTATE 01000]
> There are 0 rows in 0 pages for object 'cd_synonym'. [SQLSTATE 01000]
> DBCC results for 'SGSMGF'. [SQLSTATE 01000]
> There are 5021 rows in 35 pages for object 'SGSMGF'. [SQLSTATE 01000]
> DBCC results for 'meta_control_AI_import_tbl'. [SQLSTATE 01000]
> There are 0 rows in 4 pages for object 'meta_control_AI_import_tbl'.
> [SQLSTATE 01000]
> DBCC results for 'cd_temp_zone'. [SQLSTATE 01000]
> There are 204 rows in 2 pages for object 'cd_temp_zone'. [SQLSTATE 01000]
> DBCC results for 'ajffctlf_holding_tbl'. [SQLSTATE 01000]
> There are 0 rows in 1 pages for object 'ajffctlf_holding_tbl'. [SQLSTATE
> 01000]
> DBCC results for 'cd_vendor_xref'. [SQLSTATE 01000]
> There are 739871 rows in 3866 pages for object 'cd_vendor_xref'.
> [SQLSTATE 01000]
> DBCC results for 'cd_word'. [SQLSTATE 01000]
> There are 0 rows in 0 pages for object 'cd_word'. [SQLSTATE 01000]
> DBCC results for 'meta_logging_stage_errors'. [SQLSTATE 01000]
> There are 0 rows in 0 pages for object 'meta_logging_stage_errors'.
> [SQLSTATE 01000]
> DBCC results for 'AIDEHF_tbl'. [SQLSTATE 01000]
> There are 1060 rows in 70 pages for object 'AIDEHF_tbl'. [SQLSTATE 01000]
> DBCC results for 'AKDFCTLF'. [SQLSTATE 01000]
> There are 61 rows in 1 pages for object 'AKDFCTLF'. [SQLSTATE 01000]
> DBCC results for 'AIDEHF_holding_tbl'. [SQLSTATE 01000]
> There are 0 rows in 1 pages for object 'AIDEHF_holding_tbl'. [SQLSTATE
> 01000]
> DBCC results for 'meta_control_AIDEHF_errors_tbl'. [SQLSTATE 01000]
> There are 0 rows in 0 pages for object 'meta_control_AIDEHF_errors_tbl'.
> [SQLSTATE 01000]
> CHECKDB found 0 allocation errors and 2 consistency errors in database
> 'stage'. [SQLSTATE 01000]
> repair_allow_data_loss is the minimum repair level for the errors found
> by DBCC CHECKDB (stage ). [SQLSTATE 01000]
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator. [SQLSTATE 01000]
Consistency Errors - Large Table
I have a large table with 10 million records. It has only one clustered
index (0% fill factor) Total size of database is about 15 GB.
I am always running into consistency errors. It's frustating. Every two
days or so I run into a problem.
I have switched to another computer, and still getting errors, so I'm
not sure if it's a hardware problem or not.
Every day I run a query to update 3 million records in this table from
a flat file.
I get consistency errors often.
Object ID 802101898, index ID 0: Page (1:416734) could not be
processed. See other errors for details.
Server: Msg 8944, Level 16, State 1, Line 1
Table error: Object ID 802101898, index ID 0, page (1:416734), row 8.
Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 2477 and
535.
So I run CHECKDB with repair errors but that doesn't always help. So I
have to restore from the latest backup.
Could someone shed some light on this problem?You should contact Product Support to help you. Most likely its hardware
errors. When you switched to anothe computer, did you move the database to a
new IO subsystem and make sure it was clean of all consistency errors?
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.
<simone_maynard@.hotmail.com> wrote in message
news:1152928287.932830.124060@.h48g2000cwc.googlegroups.com...
>I have a large table with 10 million records. It has only one clustered
> index (0% fill factor) Total size of database is about 15 GB.
> I am always running into consistency errors. It's frustating. Every two
> days or so I run into a problem.
> I have switched to another computer, and still getting errors, so I'm
> not sure if it's a hardware problem or not.
> Every day I run a query to update 3 million records in this table from
> a flat file.
> I get consistency errors often.
> Object ID 802101898, index ID 0: Page (1:416734) could not be
> processed. See other errors for details.
> Server: Msg 8944, Level 16, State 1, Line 1
> Table error: Object ID 802101898, index ID 0, page (1:416734), row 8.
> Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 2477 and
> 535.
> So I run CHECKDB with repair errors but that doesn't always help. So I
> have to restore from the latest backup.
> Could someone shed some light on this problem?
>|||Version of SQLServer including SPs? OS and SPs? SQL? DDL for table?
J.
On 14 Jul 2006 18:51:27 -0700, simone_maynard@.hotmail.com wrote:
>I have a large table with 10 million records. It has only one clustered
>index (0% fill factor) Total size of database is about 15 GB.
>I am always running into consistency errors. It's frustating. Every two
>days or so I run into a problem.
>I have switched to another computer, and still getting errors, so I'm
>not sure if it's a hardware problem or not.
>Every day I run a query to update 3 million records in this table from
>a flat file.
>I get consistency errors often.
>Object ID 802101898, index ID 0: Page (1:416734) could not be
>processed. See other errors for details.
>Server: Msg 8944, Level 16, State 1, Line 1
>Table error: Object ID 802101898, index ID 0, page (1:416734), row 8.
>Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 2477 and
>535.
>So I run CHECKDB with repair errors but that doesn't always help. So I
>have to restore from the latest backup.
>Could someone shed some light on this problem?
index (0% fill factor) Total size of database is about 15 GB.
I am always running into consistency errors. It's frustating. Every two
days or so I run into a problem.
I have switched to another computer, and still getting errors, so I'm
not sure if it's a hardware problem or not.
Every day I run a query to update 3 million records in this table from
a flat file.
I get consistency errors often.
Object ID 802101898, index ID 0: Page (1:416734) could not be
processed. See other errors for details.
Server: Msg 8944, Level 16, State 1, Line 1
Table error: Object ID 802101898, index ID 0, page (1:416734), row 8.
Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 2477 and
535.
So I run CHECKDB with repair errors but that doesn't always help. So I
have to restore from the latest backup.
Could someone shed some light on this problem?You should contact Product Support to help you. Most likely its hardware
errors. When you switched to anothe computer, did you move the database to a
new IO subsystem and make sure it was clean of all consistency errors?
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.
<simone_maynard@.hotmail.com> wrote in message
news:1152928287.932830.124060@.h48g2000cwc.googlegroups.com...
>I have a large table with 10 million records. It has only one clustered
> index (0% fill factor) Total size of database is about 15 GB.
> I am always running into consistency errors. It's frustating. Every two
> days or so I run into a problem.
> I have switched to another computer, and still getting errors, so I'm
> not sure if it's a hardware problem or not.
> Every day I run a query to update 3 million records in this table from
> a flat file.
> I get consistency errors often.
> Object ID 802101898, index ID 0: Page (1:416734) could not be
> processed. See other errors for details.
> Server: Msg 8944, Level 16, State 1, Line 1
> Table error: Object ID 802101898, index ID 0, page (1:416734), row 8.
> Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 2477 and
> 535.
> So I run CHECKDB with repair errors but that doesn't always help. So I
> have to restore from the latest backup.
> Could someone shed some light on this problem?
>|||Version of SQLServer including SPs? OS and SPs? SQL? DDL for table?
J.
On 14 Jul 2006 18:51:27 -0700, simone_maynard@.hotmail.com wrote:
>I have a large table with 10 million records. It has only one clustered
>index (0% fill factor) Total size of database is about 15 GB.
>I am always running into consistency errors. It's frustating. Every two
>days or so I run into a problem.
>I have switched to another computer, and still getting errors, so I'm
>not sure if it's a hardware problem or not.
>Every day I run a query to update 3 million records in this table from
>a flat file.
>I get consistency errors often.
>Object ID 802101898, index ID 0: Page (1:416734) could not be
>processed. See other errors for details.
>Server: Msg 8944, Level 16, State 1, Line 1
>Table error: Object ID 802101898, index ID 0, page (1:416734), row 8.
>Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 2477 and
>535.
>So I run CHECKDB with repair errors but that doesn't always help. So I
>have to restore from the latest backup.
>Could someone shed some light on this problem?
Consistency Errors - Large Table
I have a large table with 10 million records. It has only one clustered
index (0% fill factor) Total size of database is about 15 GB.
I am always running into consistency errors. It's frustating. Every two
days or so I run into a problem.
I have switched to another computer, and still getting errors, so I'm
not sure if it's a hardware problem or not.
Every day I run a query to update 3 million records in this table from
a flat file.
I get consistency errors often.
Object ID 802101898, index ID 0: Page (1:416734) could not be
processed. See other errors for details.
Server: Msg 8944, Level 16, State 1, Line 1
Table error: Object ID 802101898, index ID 0, page (1:416734), row 8.
Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 2477 and
535.
So I run CHECKDB with repair errors but that doesn't always help. So I
have to restore from the latest backup.
Could someone shed some light on this problem?You should contact Product Support to help you. Most likely its hardware
errors. When you switched to anothe computer, did you move the database to a
new IO subsystem and make sure it was clean of all consistency errors?
--
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.
<simone_maynard@.hotmail.com> wrote in message
news:1152928287.932830.124060@.h48g2000cwc.googlegroups.com...
>I have a large table with 10 million records. It has only one clustered
> index (0% fill factor) Total size of database is about 15 GB.
> I am always running into consistency errors. It's frustating. Every two
> days or so I run into a problem.
> I have switched to another computer, and still getting errors, so I'm
> not sure if it's a hardware problem or not.
> Every day I run a query to update 3 million records in this table from
> a flat file.
> I get consistency errors often.
> Object ID 802101898, index ID 0: Page (1:416734) could not be
> processed. See other errors for details.
> Server: Msg 8944, Level 16, State 1, Line 1
> Table error: Object ID 802101898, index ID 0, page (1:416734), row 8.
> Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 2477 and
> 535.
> So I run CHECKDB with repair errors but that doesn't always help. So I
> have to restore from the latest backup.
> Could someone shed some light on this problem?
>|||Version of SQLServer including SPs? OS and SPs? SQL? DDL for table?
J.
On 14 Jul 2006 18:51:27 -0700, simone_maynard@.hotmail.com wrote:
>I have a large table with 10 million records. It has only one clustered
>index (0% fill factor) Total size of database is about 15 GB.
>I am always running into consistency errors. It's frustating. Every two
>days or so I run into a problem.
>I have switched to another computer, and still getting errors, so I'm
>not sure if it's a hardware problem or not.
>Every day I run a query to update 3 million records in this table from
>a flat file.
>I get consistency errors often.
>Object ID 802101898, index ID 0: Page (1:416734) could not be
>processed. See other errors for details.
>Server: Msg 8944, Level 16, State 1, Line 1
>Table error: Object ID 802101898, index ID 0, page (1:416734), row 8.
>Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 2477 and
>535.
>So I run CHECKDB with repair errors but that doesn't always help. So I
>have to restore from the latest backup.
>Could someone shed some light on this problem?
index (0% fill factor) Total size of database is about 15 GB.
I am always running into consistency errors. It's frustating. Every two
days or so I run into a problem.
I have switched to another computer, and still getting errors, so I'm
not sure if it's a hardware problem or not.
Every day I run a query to update 3 million records in this table from
a flat file.
I get consistency errors often.
Object ID 802101898, index ID 0: Page (1:416734) could not be
processed. See other errors for details.
Server: Msg 8944, Level 16, State 1, Line 1
Table error: Object ID 802101898, index ID 0, page (1:416734), row 8.
Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 2477 and
535.
So I run CHECKDB with repair errors but that doesn't always help. So I
have to restore from the latest backup.
Could someone shed some light on this problem?You should contact Product Support to help you. Most likely its hardware
errors. When you switched to anothe computer, did you move the database to a
new IO subsystem and make sure it was clean of all consistency errors?
--
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.
<simone_maynard@.hotmail.com> wrote in message
news:1152928287.932830.124060@.h48g2000cwc.googlegroups.com...
>I have a large table with 10 million records. It has only one clustered
> index (0% fill factor) Total size of database is about 15 GB.
> I am always running into consistency errors. It's frustating. Every two
> days or so I run into a problem.
> I have switched to another computer, and still getting errors, so I'm
> not sure if it's a hardware problem or not.
> Every day I run a query to update 3 million records in this table from
> a flat file.
> I get consistency errors often.
> Object ID 802101898, index ID 0: Page (1:416734) could not be
> processed. See other errors for details.
> Server: Msg 8944, Level 16, State 1, Line 1
> Table error: Object ID 802101898, index ID 0, page (1:416734), row 8.
> Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 2477 and
> 535.
> So I run CHECKDB with repair errors but that doesn't always help. So I
> have to restore from the latest backup.
> Could someone shed some light on this problem?
>|||Version of SQLServer including SPs? OS and SPs? SQL? DDL for table?
J.
On 14 Jul 2006 18:51:27 -0700, simone_maynard@.hotmail.com wrote:
>I have a large table with 10 million records. It has only one clustered
>index (0% fill factor) Total size of database is about 15 GB.
>I am always running into consistency errors. It's frustating. Every two
>days or so I run into a problem.
>I have switched to another computer, and still getting errors, so I'm
>not sure if it's a hardware problem or not.
>Every day I run a query to update 3 million records in this table from
>a flat file.
>I get consistency errors often.
>Object ID 802101898, index ID 0: Page (1:416734) could not be
>processed. See other errors for details.
>Server: Msg 8944, Level 16, State 1, Line 1
>Table error: Object ID 802101898, index ID 0, page (1:416734), row 8.
>Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 2477 and
>535.
>So I run CHECKDB with repair errors but that doesn't always help. So I
>have to restore from the latest backup.
>Could someone shed some light on this problem?
Considerations when delete records from table.
Hello, I'm developing application which monitors network packets. The monitoring data are saved into table. Monitoring table maintains the data for fixed quantum time,for example during one 1 hour. So, every minute before or after insert new data, I delete the time-expired data. I doubt that the endless delete operation would results in some problems(increasing index,etc..).
Is this mechanism safe to the dbms?
Aren't there round-robin(?) style table?
thats wat OLTP is for.... just keep the update statistics to auto (its default) , if ur using indexes.....also there may be fragmentation issues ...but thats a DBA activity/Db maintainance...
Consecutive numbering WITHOUT Identity
Hi,
I'mtrying to insert records from one table into another table. Thedestination table has a ROWID field which cannot be an identity key,but needs to 'act like' an identity key and have its value populatedwith (Max(ROWID) + 1) for each row added to the table.
To mythinking, simply using (Max(ROWID) + 1) in my SELECT statement will notwork as it will only be evaluated once so if I am adding 1000 recordsand Max(ROWID) is 1234, all 1000 entries will end up having a ROWID of1235.
Is there a way to accomplish this?
Thanks
Hi,
you can increment the row id locally in your program after doing an update in a loop:
rowID = <select max rowid blablabla>
while <there are still rows to add>
Command.executenonquery() - using rowID
rowID +=1
Loop
you can also use a trigger in the database to update the rowid column after insert
|||Yes.
You can readMax(ROWID) from database intoApplication("id") at the start of web application,e.g. 1234 to avoid frequent access to database.
After insert just add id by 1.
Hope it helps.
Consecutive Hours Query
This will challenge you. I have a table that shows time in/ time out
values per person. I need to know how many consecutive hours a person
worked. How would I get the consecutive hours?
Here's some sample data:
SSN TimeIN TimeOut HoursWorked
1 12:00 PM 8:00 PM 8
1 8:00 PM 11:00 PM 3
1 11:00 PM 12:00 AM 1
1 6:00 AM 9:00 AM 3
The records starting with 8:00 PM and 11:00 PM are consecutive with
the records before them. The result I would need would be
SSN TimeIN TimOut ConsecutiveHours
1 11:00 PM 12:00 AM 12
The result would show the TimeIn/TimeOut time at which the consecutive
hours ended.
Following Query should return you the First TimeIn/ last TimeEnd from the
first consecutive group.
CREATE TABLE [TimeLog] (
[SSN] [int] NULL ,
[TimeIn] [smalldatetime] NULL ,
[TimeOut] [smalldatetime] NULL ,
[HoursWorked] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO TimeLog (SSN,TimeIn,TimeOut,HoursWorked )
VALUES (1,'1/14/2008 12:00:00 PM','1/14/2008 8:00:00 PM',8 )
INSERT INTO TimeLog (SSN,TimeIn,TimeOut,HoursWorked )
VALUES (1,'1/14/2008 8:00:00 PM','1/14/2008 11:00:00 PM',3 )
INSERT INTO TimeLog (SSN,TimeIn,TimeOut,HoursWorked )
VALUES (1,'1/14/2008 11:00:00 PM','1/15/2008',1 )
INSERT INTO TimeLog (SSN,TimeIn,TimeOut,HoursWorked )
VALUES (1,'1/15/2008 6:00:00 AM','1/15/2008 9:00:00 AM',3 )
SELECT * FROM TimeLog
SELECT SSN,Min(TimeIn) as TimeIn,MAX(TimeOut) as TimeOut,SUM(HoursWorked) as
HoursWorked
FROM TimeLog t1
WHERE exists
(
SELECT 1
FROM TimeLog t2
WHERE
t2.ssn = t1.ssn AND t2.TimeIn <= t1.TimeIn
HAVING DATEDIFF(hh,MIN(t2.TimeIN), MAX(t2.TimeOut)) = SUM(HoursWorked)
)
GROUP BY SSN
- Sha Anand
"bean" wrote:
> This will challenge you. I have a table that shows time in/ time out
> values per person. I need to know how many consecutive hours a person
> worked. How would I get the consecutive hours?
> Here's some sample data:
> SSN TimeIN TimeOut HoursWorked
> 1 12:00 PM 8:00 PM 8
> 1 8:00 PM 11:00 PM 3
> 1 11:00 PM 12:00 AM 1
> 1 6:00 AM 9:00 AM 3
> The records starting with 8:00 PM and 11:00 PM are consecutive with
> the records before them. The result I would need would be
> SSN TimeIN TimOut ConsecutiveHours
> 1 11:00 PM 12:00 AM 12
> The result would show the TimeIn/TimeOut time at which the consecutive
> hours ended.
>
sqlsql
values per person. I need to know how many consecutive hours a person
worked. How would I get the consecutive hours?
Here's some sample data:
SSN TimeIN TimeOut HoursWorked
1 12:00 PM 8:00 PM 8
1 8:00 PM 11:00 PM 3
1 11:00 PM 12:00 AM 1
1 6:00 AM 9:00 AM 3
The records starting with 8:00 PM and 11:00 PM are consecutive with
the records before them. The result I would need would be
SSN TimeIN TimOut ConsecutiveHours
1 11:00 PM 12:00 AM 12
The result would show the TimeIn/TimeOut time at which the consecutive
hours ended.
Following Query should return you the First TimeIn/ last TimeEnd from the
first consecutive group.
CREATE TABLE [TimeLog] (
[SSN] [int] NULL ,
[TimeIn] [smalldatetime] NULL ,
[TimeOut] [smalldatetime] NULL ,
[HoursWorked] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO TimeLog (SSN,TimeIn,TimeOut,HoursWorked )
VALUES (1,'1/14/2008 12:00:00 PM','1/14/2008 8:00:00 PM',8 )
INSERT INTO TimeLog (SSN,TimeIn,TimeOut,HoursWorked )
VALUES (1,'1/14/2008 8:00:00 PM','1/14/2008 11:00:00 PM',3 )
INSERT INTO TimeLog (SSN,TimeIn,TimeOut,HoursWorked )
VALUES (1,'1/14/2008 11:00:00 PM','1/15/2008',1 )
INSERT INTO TimeLog (SSN,TimeIn,TimeOut,HoursWorked )
VALUES (1,'1/15/2008 6:00:00 AM','1/15/2008 9:00:00 AM',3 )
SELECT * FROM TimeLog
SELECT SSN,Min(TimeIn) as TimeIn,MAX(TimeOut) as TimeOut,SUM(HoursWorked) as
HoursWorked
FROM TimeLog t1
WHERE exists
(
SELECT 1
FROM TimeLog t2
WHERE
t2.ssn = t1.ssn AND t2.TimeIn <= t1.TimeIn
HAVING DATEDIFF(hh,MIN(t2.TimeIN), MAX(t2.TimeOut)) = SUM(HoursWorked)
)
GROUP BY SSN
- Sha Anand
"bean" wrote:
> This will challenge you. I have a table that shows time in/ time out
> values per person. I need to know how many consecutive hours a person
> worked. How would I get the consecutive hours?
> Here's some sample data:
> SSN TimeIN TimeOut HoursWorked
> 1 12:00 PM 8:00 PM 8
> 1 8:00 PM 11:00 PM 3
> 1 11:00 PM 12:00 AM 1
> 1 6:00 AM 9:00 AM 3
> The records starting with 8:00 PM and 11:00 PM are consecutive with
> the records before them. The result I would need would be
> SSN TimeIN TimOut ConsecutiveHours
> 1 11:00 PM 12:00 AM 12
> The result would show the TimeIn/TimeOut time at which the consecutive
> hours ended.
>
sqlsql
Consecutive Hours Query
This will challenge you. I have a table that shows time in/ time out
values per person. I need to know how many consecutive hours a person
worked. How would I get the consecutive hours?
Here's some sample data:
SSN TimeIN TimeOut HoursWorked
1 12:00 PM 8:00 PM 8
1 8:00 PM 11:00 PM 3
1 11:00 PM 12:00 AM 1
1 6:00 AM 9:00 AM 3
The records starting with 8:00 PM and 11:00 PM are consecutive with
the records before them. The result I would need would be
SSN TimeIN TimOut ConsecutiveHours
1 11:00 PM 12:00 AM 12
The result would show the TimeIn/TimeOut time at which the consecutive
hours ended.Following Query should return you the First TimeIn/ last TimeEnd from the
first consecutive group.
CREATE TABLE [TimeLog] (
[SSN] [int] NULL ,
[TimeIn] [smalldatetime] NULL ,
[TimeOut] [smalldatetime] NULL ,
[HoursWorked] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO TimeLog (SSN,TimeIn,TimeOut,HoursWorked )
VALUES (1,'1/14/2008 12:00:00 PM','1/14/2008 8:00:00 PM',8 )
INSERT INTO TimeLog (SSN,TimeIn,TimeOut,HoursWorked )
VALUES (1,'1/14/2008 8:00:00 PM','1/14/2008 11:00:00 PM',3 )
INSERT INTO TimeLog (SSN,TimeIn,TimeOut,HoursWorked )
VALUES (1,'1/14/2008 11:00:00 PM','1/15/2008',1 )
INSERT INTO TimeLog (SSN,TimeIn,TimeOut,HoursWorked )
VALUES (1,'1/15/2008 6:00:00 AM','1/15/2008 9:00:00 AM',3 )
SELECT * FROM TimeLog
SELECT SSN,Min(TimeIn) as TimeIn,MAX(TimeOut) as TimeOut,SUM(HoursWorked) as
HoursWorked
FROM TimeLog t1
WHERE exists
(
SELECT 1
FROM TimeLog t2
WHERE
t2.ssn = t1.ssn AND t2.TimeIn <= t1.TimeIn
HAVING DATEDIFF(hh,MIN(t2.TimeIN), MAX(t2.TimeOut)) = SUM(HoursWorked)
)
GROUP BY SSN
- Sha Anand
"bean" wrote:
> This will challenge you. I have a table that shows time in/ time out
> values per person. I need to know how many consecutive hours a person
> worked. How would I get the consecutive hours?
> Here's some sample data:
> SSN TimeIN TimeOut HoursWorked
> 1 12:00 PM 8:00 PM 8
> 1 8:00 PM 11:00 PM 3
> 1 11:00 PM 12:00 AM 1
> 1 6:00 AM 9:00 AM 3
> The records starting with 8:00 PM and 11:00 PM are consecutive with
> the records before them. The result I would need would be
> SSN TimeIN TimOut ConsecutiveHours
> 1 11:00 PM 12:00 AM 12
> The result would show the TimeIn/TimeOut time at which the consecutive
> hours ended.
>
values per person. I need to know how many consecutive hours a person
worked. How would I get the consecutive hours?
Here's some sample data:
SSN TimeIN TimeOut HoursWorked
1 12:00 PM 8:00 PM 8
1 8:00 PM 11:00 PM 3
1 11:00 PM 12:00 AM 1
1 6:00 AM 9:00 AM 3
The records starting with 8:00 PM and 11:00 PM are consecutive with
the records before them. The result I would need would be
SSN TimeIN TimOut ConsecutiveHours
1 11:00 PM 12:00 AM 12
The result would show the TimeIn/TimeOut time at which the consecutive
hours ended.Following Query should return you the First TimeIn/ last TimeEnd from the
first consecutive group.
CREATE TABLE [TimeLog] (
[SSN] [int] NULL ,
[TimeIn] [smalldatetime] NULL ,
[TimeOut] [smalldatetime] NULL ,
[HoursWorked] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO TimeLog (SSN,TimeIn,TimeOut,HoursWorked )
VALUES (1,'1/14/2008 12:00:00 PM','1/14/2008 8:00:00 PM',8 )
INSERT INTO TimeLog (SSN,TimeIn,TimeOut,HoursWorked )
VALUES (1,'1/14/2008 8:00:00 PM','1/14/2008 11:00:00 PM',3 )
INSERT INTO TimeLog (SSN,TimeIn,TimeOut,HoursWorked )
VALUES (1,'1/14/2008 11:00:00 PM','1/15/2008',1 )
INSERT INTO TimeLog (SSN,TimeIn,TimeOut,HoursWorked )
VALUES (1,'1/15/2008 6:00:00 AM','1/15/2008 9:00:00 AM',3 )
SELECT * FROM TimeLog
SELECT SSN,Min(TimeIn) as TimeIn,MAX(TimeOut) as TimeOut,SUM(HoursWorked) as
HoursWorked
FROM TimeLog t1
WHERE exists
(
SELECT 1
FROM TimeLog t2
WHERE
t2.ssn = t1.ssn AND t2.TimeIn <= t1.TimeIn
HAVING DATEDIFF(hh,MIN(t2.TimeIN), MAX(t2.TimeOut)) = SUM(HoursWorked)
)
GROUP BY SSN
- Sha Anand
"bean" wrote:
> This will challenge you. I have a table that shows time in/ time out
> values per person. I need to know how many consecutive hours a person
> worked. How would I get the consecutive hours?
> Here's some sample data:
> SSN TimeIN TimeOut HoursWorked
> 1 12:00 PM 8:00 PM 8
> 1 8:00 PM 11:00 PM 3
> 1 11:00 PM 12:00 AM 1
> 1 6:00 AM 9:00 AM 3
> The records starting with 8:00 PM and 11:00 PM are consecutive with
> the records before them. The result I would need would be
> SSN TimeIN TimOut ConsecutiveHours
> 1 11:00 PM 12:00 AM 12
> The result would show the TimeIn/TimeOut time at which the consecutive
> hours ended.
>
Subscribe to:
Posts (Atom)