October 15, 2009 at 11:00 am
Hi,
I would like to know whether you guys use Maintenance plan task for running Database Integrity check for a Prodcution SQL Instance OR
Write a script "DBCC CHECKDB (db_name) with no_infomsgs" and put it in a SQL Agent job and run it?
Currently I'm using Maintenance Plan Task for Database Integrity Check for Development servers and want to know what is the Best practice for PRODUCTION Server
thank you
October 15, 2009 at 11:06 am
I use a customized version of this MS code which is from the Pro SQL Server 2005 High Availability book. I like it because it verifies the databases are on line and available before attempting to run against them.
use msdb
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SQLDbccAll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SQLDbccAll]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE SQLDbccAll
AS
----------------------------------------------------------------------------------------------
-- Author Mark G. Pohto / Microsoft SQL Operations
----------------------------------------------------------------------------------------------
-- This procedure performs a DBCC on system and user databases.
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET ANSI_NULLS ON
DECLARE @DBName VARCHAR(100)
,@SQLString VARCHAR(255)
DECLARE DB_Cursor CURSOR
FOR
select a.name
from master..sysdatabases a
where databaseproperty(a.name,'isReadOnly') = 0
and databaseproperty(a.name,'isOffline') = 0
and databaseproperty(a.name,'IsSuspect') = 0
and databaseproperty(a.name,'IsShutDown') = 0
and databaseproperty(a.name,'IsNotRecovered') = 0
and databaseproperty(a.name,'IsInStandBy') = 0
and databaseproperty(a.name,'IsInRecovery') = 0
and databaseproperty(a.name,'IsInLoad') = 0
and databaseproperty(a.name,'IsEmergencyMode') = 0
and databaseproperty(a.name,'IsDetached') = 0
and name != 'tempdb'
and not exists
(select b.name
from msdb..SQLDBDBCCExclusions b
where a.name = b.name)
OPEN DB_Cursor
FETCH NEXT
FROM DB_Cursor
INTO @DBName
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
-- DBCC database
SELECT @SQLString = 'SET QUOTED_IDENTIFIER ON SET ARITHABORT ON DBCC CHECKDB ('+@DBName+')'
--print @SQLString
EXEC (@SQLString)
--Insert the database in to the DBExclusions table so that a DBCC does not get rerun for that database in case of retry of the job step
if not exists(select 1 from msdb.dbo.SQLDBDBCCExclusions where name = @DBName)
insert into msdb.dbo.SQLDBDBCCExclusions values(@DBName)
END
FETCH NEXT
FROM DB_Cursor
INTO @DBName
END
CLOSE DB_Cursor
DEALLOCATE DB_Cursor
GO
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
O
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply