September 28, 2011 at 5:11 am
Hi all,
One of my test server has given this error below when we execute the job named"DBCC checkdb"
Executed as user: CSL\BackupAdmin. Arithmetic overflow error for data type smallint, value = 33292. [SQLSTATE 22003] (Error 220). The step failed.
I did not understand what it says ?
The following are the three steps for the running the job.
1.I created table like this
USE [master]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__dba_check__Log_d__1940BAED]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[dba_checkdb] DROP CONSTRAINT [DF__dba_check__Log_d__1940BAED]
END
GO
USE [master]
GO
/****** Object: Table [dbo].[dba_checkdb] Script Date: 07/15/2011 14:40:30 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dba_checkdb]') AND type in (N'U'))
DROP TABLE [dbo].[dba_checkdb]
GO
USE [master]
GO
/****** Object: Table [dbo].[dba_checkdb] Script Date: 07/15/2011 14:40:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[dba_checkdb](
[Error_id] [int] IDENTITY(1,1) NOT NULL,
[Log_date] [datetime] NULL,
[Error] [smallint] NULL,
[Level] [tinyint] NULL,
[State] [tinyint] NULL,
[MessageText] [varchar](500) NULL,
[RepairLevel] [varchar](40) NULL,
[Status] [tinyint] NULL,
[Dbid] [smallint] NULL,
[ObjectId] [int] NULL,
[IndexId] [int] NULL,
[PartitionId] [bigint] NULL,
[AllocUnitId] [bigint] NULL,
[File] [int] NULL,
[Page] [int] NULL,
[Slot] [int] NULL,
[reffile] [int] NULL,
[refpage] [int] NULL,
[refslot] [int] NULL,
[Allocation] [int] NULL,
PRIMARY KEY CLUSTERED
(
[Error_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[dba_checkdb] ADD DEFAULT (getdate()) FOR [Log_date]
GO
2. create stored procedure
USE [master]
GO
/****** Object: StoredProcedure [dbo].[usp_dba_db_checker] Script Date: 07/15/2011 12:04:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_dba_db_checker]
@dbname varchar(50) = NULL,
@option varchar(50) = NULL
AS
SET NOCOUNT ON
-- =============================================
-- Create date: 08/05/2010
-- Description: Database Integrity Check and Error Capture
-- Modified : 11/04/2011
-- Comments: Added Error handling using TRY CATCH and updated recipient Email Address
-- Usage1: Execute master.dbo.usp_dba_db_checker 'master'
-- Usage2: Execute master.dbo.usp_dba_db_checker NULL - for all databases, all checks
-- Usage3: Execute master.dbo.usp_dba_db_checker @option = 'PHYSICAL_ONLY' - for all databases, physical only
-- Modified : 15/07/2011
-- comments: Added custom print for individual database
-- =============================================
DECLARE @msg varchar(250),
@body varchar(250),
@profile varchar(50),
@subject varchar(50),
@recipients varchar(70),
@sql nvarchar(500),
@ctr smallint,
@var smallint,
--@count_prev smallint,
--@count_now smallint,
@info_msg_log varchar(60);
DECLARE @dbset TABLE (cnt tinyint IDENTITY(1,1), dbname varchar(50))
-- SET Variables
SET @ctr = 1
SET @info_msg_log = 'NO_INFOMSGS'
-- Gather database to work on
INSERT INTO @dbset
select name from sys.databases where name = ISNULL(@dbname,name) AND
state = 0
--SELECT @count_prev = COUNT(*) from master.dbo.dba_checkdb
SELECT @var = COUNT(*) from @dbset
WHILE (@ctr <= @var)
BEGIN
SELECT @dbname = dbname from @dbset where cnt = @ctr
IF @info_msg_log IS NOT NULL
IF @option IS NULL
SET @sql = ('dbcc checkdb('''+ @dbname +''')WITH ' + @info_msg_log +'
, TABLERESULTS')
ELSE
SET @sql = ('dbcc checkdb('''+ @dbname +''')WITH ' + @info_msg_log +
',' + @option + ', TABLERESULTS')
BEGIN TRY
INSERT INTO master.dbo.dba_checkdb
( Error , [Level] , [State] , MessageText ,RepairLevel ,
[Status] , [Dbid] , ObjectId , IndexId ,PartitionId , AllocUnitId ,
[File] , Page , Slot ,reffile , refpage , refslot , Allocation )
Execute sp_executesql @sql
IF @@ROWCOUNT > 0
PRINT 'Checkdb failed for database' + @dbname + '. Check table master.dbo.dba_checkdb for errors'
ELSE
PRINT 'CHECKDB Success'
END TRY
BEGIN CATCH
DECLARE @error varchar(800)
SET @error = 'Error Severity: ' + CONVERT(varchar(2) ,ERROR_SEVERITY()) + ' , Error Message: '
+ ERROR_MESSAGE() + ', Error statement: ' + @sql
RAISERROR (@error,16,1)
END CATCH
SET @ctr = @ctr + 1
WAITFOR DELAY '00:00:10'
END
3.job has created for the following scripts
USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'dba_error_check',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'Database Maintenance',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Check database error] Script Date: 07/15/2011 14:32:25 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Check database error',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'execute master.dbo.usp_dba_db_checker',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Saturday @ 21.15',
@enabled=1,
@freq_type=8,
@freq_interval=64,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20110715,
@active_end_date=99991231,
@active_start_time=211500,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
September 28, 2011 at 5:25 am
The table you defined has the column Error defined as a smallest, however error numbers can go above 32767. Change that column to an integer.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 28, 2011 at 6:54 am
I have a similar setup here.
What I do is use the ALL_ERRORMSGS & NO_INFOMSGS switches that way I get data back only when there's corruption.
Also if you do really have corruption, post back here for help. You can't ask for better support than Gail's.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply