DBCheck

Running DBCC CHECKDB against a busy database means taking it single-user, checking (or repairing) it, and putting it back - with optional flags and an UPDATEUSAGE at the end. This stored procedure packages that whole sequence so you can run it from a job: pass the database name, an optional repair mode (REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD), optional CHECKDB options (ALL_ERRORMSGS | NO_INFOMSGS | TABLOCK | ESTIMATEONLY | PHYSICAL_ONLY | DATA_PURITY), and whether to run UPDATEUSAGE afterwards.

Example: EXEC DBCheck @DB = 'MyDatabase', @JobType = '', @Options = 'NO_INFOMSGS', @UpdateUsage = 1

-- =============================================
-- Author:      Josh Patterson
-- Create date: 6/9/2006
-- Description: Repairs / checks a specified database.
-- =============================================
CREATE PROCEDURE DBCheck
(
	--@DB      --Name of database to be checked / repaired.
	--@Options --ALL_ERRORMSGS | NO_INFOMSGS | TABLOCK | ESTIMATEONLY | PHYSICAL_ONLY | DATA_PURITY
	--@JobType --REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
	@DB VarChar(MAX), @JobType VarChar(MAX), @Options VarChar(MAX), @UpdateUsage Bit
)
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @SQL VarChar(MAX)

	IF RTrim(LTrim(@Options)) <> ''
	BEGIN
		SET @Options = ' WITH ' + @Options
	END
	ELSE BEGIN
		SET @Options = ''
	END

	IF RTrim(LTrim(@JobType)) <> ''
	BEGIN
		SET @JobType = ' , ' + @JobType
	END
	ELSE BEGIN
		SET @JobType = ''
	END

	SET @SQL =
	'USE [Master];
	ALTER DATABASE [' + @DB + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
	DBCC CHECKDB(''' + @DB + '''' + @JobType + ')' + @Options + ';
	ALTER DATABASE [' + @DB + '] SET MULTI_USER WITH ROLLBACK IMMEDIATE;'
	+ CASE @UpdateUsage WHEN 1 THEN ' DBCC UPDATEUSAGE(''' + @DB + ''');' ELSE '' END

	EXEC(@SQL)
END
GO