DBCC CheckDB All Databases

If you use the SQL Server maintenance plans to run DBCC Check DB, you are not allowed to include TempDB in your DBCC CheckDB sequence. For a while I was using maintenance plans, and ending up adding a separate job step to run TSQL to just check TempDB.

Rather than using two different steps, the regular maintenance plan, and a special step for Temp DB, I have instead dumped the regular maintenance plan step, and just used the following TSQL code to run DBCC CheckDB against all databases on the SQL Server.

Option 1: DBCC CheckDB All Databases using sp_msforeachdb

I have included Option 2 below since there are some pretty serious flaws in the sp_msforeachdb script as shown in option 1, specifically sp_msforeachdb will occasionally skip databases. It also has trouble with databases created with certain characters. Anyway if you want it, here is, but I would recommend paging down to Option 2 which is much more reliable. I would like to thank Patrick Flynn who showed me the flaws in sp_msforeachdb.

The stored procedure sp_msforeachdb takes a parameter of query that will get run against all databases on your SQL Server Instance.


EXEC sp_msforeachdb 'DBCC CHECKDB(''?'') WITH NO_INFOMSGS;';

You can set it up as a maintenance plan like this:

You could instead use a specific list of databases, but then when you add a new database it won’t be included in the CheckDB process.

Option 2: DBCC CheckDB All Databases A Better Way

So what you need is a list of databases and a way to iterate through them. There are a number of generic replacements for sp_msforeachdb from other SQL professionals, however they are more complex that I wanted to use for a script to check all the databases on my server, so I created the following script.


DECLARE @databaseList as CURSOR;
DECLARE @databaseName as NVARCHAR(500);
DECLARE @tsql AS NVARCHAR(500);

SET @databaseList = CURSOR  LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT QUOTENAME([name])
FROM sys.databases
WHERE [state] = 0
AND [is_read_only] = 0;
OPEN @databaseList;
FETCH NEXT FROM @databaseList into @databaseName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @tsql = N'DBCC CheckDB(' + @databaseName + ') WITH NO_INFOMSGS;';
EXECUTE (@tsql);
FETCH NEXT FROM @databaseList into @databaseName;
END
CLOSE @databaseList;
DEALLOCATE @databaseList;

You can use this script in a maintenance plan as shown in Option 1 above, or you could place it in a stored procedure like this:

CREATE PROCEDURE checkAllDatabases
AS
BEGIN
DECLARE @databaseList as CURSOR;
DECLARE @databaseName as NVARCHAR(500);
DECLARE @tsql AS NVARCHAR(500);

SET @databaseList = CURSOR  LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT QUOTENAME([name])
FROM sys.databases
WHERE [state] = 0
AND [is_read_only] = 0;
OPEN @databaseList;
FETCH NEXT FROM @databaseList into @databaseName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @tsql = N'DBCC CheckDB(' + @databaseName + ') WITH NO_INFOMSGS;';
EXECUTE (@tsql);
FETCH NEXT FROM @databaseList into @databaseName;
END
CLOSE @databaseList;
DEALLOCATE @databaseList;
END
GO
EXEC checkAllDatabases;

You can set the checkAllDatabases stored procedure up on a maintenance plan.

To check on which databases have had DBCC CheckDB run lately, and which haven’t you can use the LAST DBCC CheckDB Known Good report in the Database Health Monitor application.

Just a quick way to run DBCC CheckDB All Databases. This applies to SQL Server 2005, SQL Server 2008, SQL Server 2008R2, SQL Server 2012, SQL Server 2014, and SQL Server 2016.

DBCC CheckDB All Databases

Option 1: DBCC CheckDB All Databases using sp_msforeachdb

Option 2: DBCC CheckDB All Databases A Better Way

Related Links

Leave a Reply Cancel reply