MICROSOFT SQL SERVER 2005/2008:CHECK DATABASE INTEGIRY BY DBCC CHECKDB

Checks the logical and physical integrity of all the objects in the specified database by performing the following operations:

·         Runs DBCC CHECKALLOC on the database.

·         Runs DBCC CHECKTABLE on every table and view in the database.

·         Runs DBCC CHECKCATALOG on the database.

·         Validates the contents of every indexed view in the database.

·         Validates link-level consistency between table metadata and file system directories and files when storing varbinary(max) data in the file system using FILESTREAM.

·         Validates the Service Broker data in the database.

This means that the DBCC CHECKALLOC, DBCC CHECKTABLE, or DBCC CHECKCATALOG commands do not have to be run separately from DBCC CHECKDB. For more detailed information about the checks that these commands perform, see the descriptions of these commands.

Do this task as weekly/monthly basis.

Use this steps to create as scheduled job.

1.Create table to maintain process log.

USE [master]
GO

/****** Object:  Table [dbo].[PROCESS_LOG]    Script Date: 07/19/2012 11:50:11 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[PROCESS_LOG](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [PROCESSNAME] [varchar](1000) NULL,
    [PROCESSTIME] [datetime] NULL,
    [COMMENTS] [varchar](max) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


2.Create Procedure  [CHECK_DATABASE_INTEGRITY]

USE [master]
GO
/******************************************************************************/   
/* Copyright: 2012 Database Experts
*    
* Name : [CHECK_DATABASE_INTEGRITY]$   
* Version : $Revision: 1    
* Last Updated : $Modtime:
* Created By : $Author: Bhavesh Thakkar
* Modified By : $Author:    
* Script : This procedure is used to check database integrity
* Input:
* Version Info:   
* No.                 Author                            Date                                                           Comments   
* --- ------ ---- --------   
    1                Bhavesh Thakkar                        2012-07-19  
*/
/*****************************************************************************/   
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[CHECK_DATABASE_INTEGRITY]

AS

DECLARE @name VARCHAR(50) -- database name

DECLARE @Sql varchar(500)-- Script to EXECUTE DBCC CHECKDB

DECLARE db_cursor CURSOR FOR

SELECT name

FROM master.dbo.sysdatabases

WHERE name  not IN ('master','model','msdb','tempdb')

OPEN db_cursor 

FETCH NEXT FROM db_cursor INTO @name 

WHILE @@FETCH_STATUS = 0 

BEGIN 
      
       SET @Sql='USE [' + @name + '] DBCC CHECKDB '
      
       print 'RUNNING CHECKDB ON ...'+@name       
      
       print '   '
      
       EXEC(@Sql)
         
       INSERT INTO [master].[dbo].[PROCESS_LOG]
           ([PROCESSNAME]
           ,[PROCESSTIME]
           ,[COMMENTS])
       VALUES
           ('PROCESS OF DBCC CHECKDB:DATABASE '+@name
           ,GETDATE()
           ,'PROCESSED FROM [CHECK_DATABASE_INTEGRITY]')
          
       print '   '       
      
       print 'CHECKDB PROCESS Completed FOR ...'+@name       
      
       print '   '
      
       FETCH NEXT FROM db_cursor INTO @name 
      
END 

CLOSE db_cursor 

DEALLOCATE db_cursor

3.Schedule it for daily/weekly/monthly basis.

About Me