October 3, 2011 at 3:06 am
Dear All,
When i was executed the job, it gives the following error message ?
what is the meaning of this ?
Executed as user: D1ADS\bctopsql. Error Severity: 16 , Error Message: DBCC could not obtain a lock on object 4 because the lock request timeout period was exceeded., Error statement: dbcc checkdb('tempdb')WITH NO_INFOMSGS , TABLERESULTS [SQLSTATE 42000] (Error 50000) CHECKDB Success [SQLSTATE 01000] (Error 0). The step failed.
regards,
Satish
October 3, 2011 at 3:13 am
you cant run CHECK DB on TempDB,
It is not needed as it will be recreated when the server starts.
October 3, 2011 at 7:04 am
steveb. (10/3/2011)
you cant run CHECK DB on TempDB
We can run DBCC checkdb on tempdb.
However, CheckDB will not perform allocation or catalog checks but it will do table checks in tempdb. More explanation in this link under 'Internal Database Snapshot' section of
http://msdn.microsoft.com/en-us/library/ms176064.aspx
M&M
October 3, 2011 at 8:36 pm
Actually i used the below code.
1.create table
2.create stored procedure
3.create job on msdb
1.create table :
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
-- =============================================
-- Author: Akhil Patel
-- 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.create job
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply