strange dbcc checkdb error

Question

Post reply

strange dbcc checkdb error

george sibbald

SSC Guru

Points: 104210
More actions
December 14, 2008 at 6:30 am

#202892

Hi,
running a dbcc checkdb against a database I was getting error
'DBCC failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. [SQLSTATE 42000] (Error 1934). The step failed.'
so I tried it with quoted_identifier on and got this error
' Arithmetic overflow error converting numeric to data type numeric. [SQLSTATE 22003] (Error 8115). The step failed.'
I have tracked this down to a particular index and the way it is specified, table schema below
CREATE TABLE [dbo].[Observances] (
[ObservanceID] [bigint] NOT NULL ,
[ObservanceTime] [datetime] NOT NULL ,
[ObservanceType] [tinyint] NULL ,
[SecChkID] [int] NULL ,
[SensorID] [int] NOT NULL ,
[SourceID] [int] NOT NULL ,
[TargetID] [int] NOT NULL ,
[ObservanceCount] [int] NULL ,
[ObjectID] [int] NULL ,
[SeverityID] [tinyint] NULL ,
[ClearedCount] [int] NULL ,
[VulnStatus] [tinyint] NULL ,
[LastModifiedAt] [datetime] NULL ,
[VLanID] [int] NULL ,
[VirtualSensorID] [int] NULL ,
[EventUserID] [int] NULL ,
[SensorInterfaceID] [int] NULL ,
[CheckSumID] AS (100000000000000000000000000000000 * convert(numeric(6),(convert(int,((convert(numeric(7,2),[ObservanceTime],126) - 35000.00) * 100.0)) % 1000000)) + 1000000000000000000000000.0 * ([SourceID] % 100000000) + 10000000000000000.0 * ([TargetID] % 100000000) + 1000000000.0 * ([SecChkID] % 10000000) + 10000.0 * (isnull([EventUserID],0) % 100000) + 1.0 * ([ObjectID] % 10000))
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [Observances_AK4] ON [dbo].[Observances]([ObservanceTime]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Observances] ADD
CONSTRAINT [Def_Zero8] DEFAULT (0) FOR [ClearedCount],
CONSTRAINT [Observances_PK] PRIMARY KEY NONCLUSTERED
(
[ObservanceID]
) ON [PRIMARY]
GO
CREATE INDEX [Observances_AK7] ON [dbo].[Observances]([SecChkID]) ON [PRIMARY]
GO
set ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS on
GO
set NUMERIC_ROUNDABORT off
GO
CREATE INDEX [Observances_AK1] ON [dbo].[Observances]([CheckSumID]) ON [PRIMARY]
GO
set quoted_identifier OFF
GO
CREATE INDEX [Observances_AK10] ON [dbo].[Observances]([LastModifiedAt]) ON [PRIMARY]
GO
CREATE INDEX [Observances_AK3] ON [dbo].[Observances]([SourceID]) ON [PRIMARY]
GO
CREATE INDEX [Observances_AK2] ON [dbo].[Observances]([TargetID]) ON [PRIMARY]
GO
the problem index is Observances_AK1 which was created with quoted_identifier on. Why would this be and can I safely drop and recreate with quoted_identifer off?
SQL version is 8.00.2187.
---------------------------------------------------------------------

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic. Login to reply

Paul Randal One Orange Chip Points: 29544 More actions · Answer 1

Right - you've got a computed column in the table and the SET options have to be on correctly for DBCC CHECKDB to run.

Thanks

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

george sibbald SSC Guru Points: 104210 More actions · Answer 2

thanks for replying Paul. Are you saying quoted_identifier incorrectly set when index created? Because checkdb fails whether it is on or off when checkdb runs

---------------------------------------------------------------------

Paul Randal One Orange Chip Points: 29544 More actions · Answer 3

I think its arithabort that's causing you problems - there's a section in Books Online called Session Settings That Affect Results - look in there. The problem isn't DBCC, its your settings.

Thanks

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

george sibbald SSC Guru Points: 104210 More actions · Answer 4

according to MSDN

http://msdn.microsoft.com/en-us/library/aa933138(SQL.80).aspx

SET options were correct for creating index on a computed column, but I cannot get dbcc to run on this index with quoted_identifier set on or off and all other set options set to default.

quoted_identifier on - Arithmetic overflow error converting numeric to data type numeric

quoted_identifier off - 'DBCC failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'

---------------------------------------------------------------------

Paul Randal One Orange Chip Points: 29544 More actions · Answer 5

Try changing the setting of arithabort when you have quoted_identifier on... what happens?

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

george sibbald SSC Guru Points: 104210 More actions · Answer 6

set ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,QUOTED_IDENTIFIER,ANSI_NULLS on

GO

set NUMERIC_ROUNDABORT,ARITHABORT off

GO

dbcc checktable(observances,3)

gives

DBCC failed because the following SET options have incorrect settings: 'ARITHABORT'.

set arithabort on (so the exact settings used when index created)

and I get:

Arithmetic overflow error converting numeric to data type numeric.

I seem to be in a catch 22 here

---------------------------------------------------------------------

Paul Randal One Orange Chip Points: 29544 More actions · Answer 7

So it looks like you've got some corrupt data values in the table - can you select * all the data without any errors? My guess is that one or more rows will fail with the same error.

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

george sibbald SSC Guru Points: 104210 More actions · Answer 8

cheers paul, I'll give it a try but table has 2.6 million rows so may be a while..........

---------------------------------------------------------------------

Paul Randal One Orange Chip Points: 29544 More actions · Answer 9

I'd try doing it in ranges, to limit the result set in each case. I'm not sure whether you'll get an indication of which row failde - at least with ranges you can find the range that fails and then narrow the range down.

This type of thing is handled more gracefully in DBCC in 2005+.

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

george sibbald SSC Guru Points: 104210 More actions · Answer 10

Paul, succesfully read whole of table without errors, so I'm going to call it quits and enjoy whats left of my sunday (its 7:30 here).

I'm satisfied data is ok and no corruptions so I will tailor dbcc job to work round this index. data is collected into this table on the hour every hour and kept for a month, so maybe the problem (if there is one) will cycle out.

thanks for your time, enjoy the rest of the weekend.

---------------------------------------------------------------------

Paul Randal One Orange Chip Points: 29544 More actions · Answer 11

Hey George - I had a thought - can you try changing the setting of NUMERIC_ROUNDABORT and see if CHECKTABLE completes? If it does, you might be able to find the dodgy data using data purity checks - try turning on trace flag 2570 when you run it. (You can read about data purity checks in the DBCC CHECKDB BOL in 2005 - there were some available in 2000 but only under that trace flag).

Thanks

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

george sibbald SSC Guru Points: 104210 More actions · Answer 12

I'll give it a whirl Paul.

---------------------------------------------------------------------

george sibbald SSC Guru Points: 104210 More actions · Answer 13

sorry Paul , it don't like numeric_roundabort set on -

'DBCC failed because the following SET options have incorrect settings: 'NUMERIC_ROUNDABORT'.

---------------------------------------------------------------------

ta.bu.shi.da.yu Hall of Fame Points: 3985 More actions · Answer 14

george sibbald (12/14/2008)
CREATE TABLE [dbo].[Observances] (
[ObservanceID] [bigint] NOT NULL ,
[ObservanceTime] [datetime] NOT NULL ,
[ObservanceType] [tinyint] NULL ,
[SecChkID] [int] NULL ,
[SensorID] [int] NOT NULL ,
[SourceID] [int] NOT NULL ,
[TargetID] [int] NOT NULL ,
[ObservanceCount] [int] NULL ,
[ObjectID] [int] NULL ,
[SeverityID] [tinyint] NULL ,
[ClearedCount] [int] NULL ,
[VulnStatus] [tinyint] NULL ,
[LastModifiedAt] [datetime] NULL ,
[VLanID] [int] NULL ,
[VirtualSensorID] [int] NULL ,
[EventUserID] [int] NULL ,
[SensorInterfaceID] [int] NULL ,
[CheckSumID] AS (100000000000000000000000000000000 * convert(numeric(6),(convert(int,((convert(numeric(7,2),[ObservanceTime],126) - 35000.00) * 100.0)) % 1000000)) + 1000000000000000000000000.0 * ([SourceID] % 100000000) + 10000000000000000.0 * ([TargetID] % 100000000) + 1000000000.0 * ([SecChkID] % 10000000) + 10000.0 * (isnull([EventUserID],0) % 100000) + 1.0 * ([ObjectID] % 10000))
) ON [PRIMARY]

Er, is it just me, but isn't 100,000,000,000,000,000,000,000,000,000,000 greater than even 2^64? 🙂 And you are multiplying this!

Perhaps this might be the reason for the arithabort? Surely this hits an overflow condition.

Random Technical Stuff[/url]