How to run checkDB on several databases concurrently

Question

Hi,

I am trying to get run a database integrity check on several databases at once.
When I run the following it does them sequentially. Any idea on how to do them all at the same time?

declare @cmd varchar(500)    
set @cmd='USE ? DBCC CHECKDB'   
exec sp_MSforeachdb @cmd   

Thanks,

Answer 1

Not easily.

You can create a number of Agent jobs, in which you hard code the database names, and then your controlling script will start them using sp_start_job (which doesn't wait for the job to finish). But now you have hard-wired the database names in those jobs.

You can use something similar using Service Broker, but I doubt that will buy you anything compared to able. More a matter of which of those you find more attractive.

But why would you want to rum them concurrently. The risk is that you trash the system so that it will just take longer time compared to running them serially.

If you still want to do them in parallel, I would check out Ola's script which has such an option. But you still need more than one job. https://ola.hallengren.com/sql-server-integrity-check.html

Answer 2

sp_MSforeachDb execute the SQL statement for each database one by one, not parallel and it's not possible to run parallel SQL statement in on session.

Answer 3

This should help you in getting the checkdb run on all the databases. sp_MSforeachdb is old and sometimes not reliable

BEGIN
IF OBJECT_ID('tempdb..#Checkdb') IS NOT NULL DROP TABLE #Checkdb
Create table #Checkdb
(
ID INT IDENTITY (1,1) NOT NULL ,
DB_name sysname NOT NULL,
State_desc nvarchar(60)
)


Insert into  #Checkdb

Select name,state_desc from Sys.databases where state_desc='Online'


Declare @Command1 nvarchar(max),
@ID Int,
@DBName1 sysname,
@State_Desc1 nvarchar(60)

SET @ID=1

While (1=1)
BEGIN
Select 
@ID=ID,
@DBName1=DB_name,
@State_Desc1=State_desc
from #Checkdb
where ID=@ID
IF @@ROWCOUNT=0
BREAK

SET @command1 = N'DBCC CHECKDB([' + @DBName1 + N']) WITH NO_INFOMSGS, ALL_ERRORMSGS'
EXEC @command1
Set @ID=@ID+1

END
END

Answer 4

If you want to do it all inside SQL Server, you will need to either write a CLR stored procedure that spawns multiple threads, or use Service Broker and have a queue with multiple queue readers.

It would be more reasonable to orchestrate it from the outside with PowerShell or similar.

Answer 5

Hi @zoe Ohara ,

Code from this doc. will be helpful: simple-dbcc-checkdb-process-to-report-on-database-corruption-for-all-sql-server-databases

USE [master]  
GO  
CREATE TABLE [dbo].[CheckDB](  
   [instance] [varchar](255) NOT NULL,  
   [database] [varchar](255) NOT NULL,  
   [size] [int] NOT NULL,  
   [result] [varchar](max) NULL,  
   [checkdb_type] [varchar](255) NULL,  
   [data_collection_timestamp] [smalldatetime] NULL,  
   [completion_time] [int] NULL,  
   [last_good_dbcc] [datetime] NULL  
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]  
GO  
  
  
USE [master]  
GO  
  
/****** Object:  StoredProcedure [dbo].[Simple_CHECKDB]    Script Date: 8/30/2018 3:45:26 PM ******/  
SET ANSI_NULLS ON  
GO  
  
SET QUOTED_IDENTIFIER ON  
GO  
  
-- =============================================  
-- Author: Alejandro Cobar  
-- Create date: 8/22/2018  
-- Description: Runs DBCC CHECKDB ON each database and stores the output message   
-- =============================================  
CREATE PROCEDURE [dbo].[Simple_CHECKDB]  
@dbSizeThreshold INT = 512000,  
@force INT = 0  
AS  
BEGIN  
 SET NOCOUNT ON;  
  
 -- Temporal table to obtain the "Last Known Good DBCC CHECKDB" execution for each database  
 IF NOT OBJECT_ID('tempdb..#DBInfo') IS NULL    
 DROP TABLE #DBInfo;   
  
 CREATE TABLE #DBInfo  
 ([ParentObject] VARCHAR(255)  
 ,[Object] VARCHAR(255)  
 ,[Field] VARCHAR(255)  
 ,[Value] VARCHAR(255)  
 )  
  
 -- Depending on the SQL Server version, the respective temporal table will be created to store the CHECKDB results  
 DECLARE @version INT;  
 SELECT @version = RIGHT(LEFT(@@VERSION,25),4);  
  
 --Starting from SQL Server 2012, new fields were introduced to the output of DBCC CHECKDB WITH TABLERESULTS  
 IF NOT OBJECT_ID('tempdb..#CheckDB_old') IS NULL    
 DROP TABLE #CheckDB_old;   
  
 IF NOT OBJECT_ID('tempdb..#CheckDB_new') IS NULL    
 DROP TABLE #CheckDB_new;  
  
 IF @version >= 2012  
 CREATE TABLE #CheckDB_new   
 ([Error] INT   
 ,[Level] INT   
 ,[State] INT   
 ,[MessageText] VARCHAR(MAX)   
 ,[RepairLevel] INT   
 ,[Status] INT   
 ,[DbId] INT   
 ,[DbFragId] INT   
 ,[ObjectID] INT   
 ,[IndexId] INT   
 ,[PartitionId] INT   
 ,[AllocUnitId] INT   
 ,[RidDbId] INT   
 ,[RidPruId] INT   
 ,[File] INT   
 ,[Page] INT   
 ,[Slot] INT   
 ,[RefDbID] INT   
 ,[RefPruId] INT   
 ,[RefFile] INT   
 ,[RefPage] INT   
 ,[RefSlot] INT   
 ,[Allocation] INT);  
 ELSE  
 CREATE TABLE #CheckDB_old   
 ([Error] INT   
 ,[Level] INT   
 ,[State] INT   
 ,[MessageText] VARCHAR(MAX)   
 ,[RepairLevel] INT   
 ,[Status] INT   
 ,[DbId] INT   
 ,[ObjectID] INT   
 ,[IndexId] INT   
 ,[PartitionId] INT   
 ,[AllocUnitId] INT   
 ,[File] INT   
 ,[Page] INT   
 ,[Slot] INT   
 ,[RefFile] INT   
 ,[RefPage] INT   
 ,[RefSlot] INT   
 ,[Allocation] INT);  
  
 -- We don't want to keep all the CHECKDB results here for a very long time...  
 TRUNCATE TABLE master.dbo.CheckDB;  
  
 -- Insert all the databases that will be checked  
 -- Only consider those in ONLINE state and exclude the SNAPSHOTS  
 INSERT INTO master.dbo.CheckDB  
 SELECT CAST(SERVERPROPERTY('SERVERNAME') AS VARCHAR(255)), DB_NAME(mf.database_id), SUM(CAST(mf.size AS BIGINT)*8)/1024, NULL, NULL, NULL, NULL,NULL  
 FROM sys.master_files mf  
 JOIN sys.databases db ON mf.database_id = db.database_id  
 WHERE db.state = 0 AND db.source_database_id IS NULL  
 GROUP BY mf.database_id;  
  
 -- Prepare a cursor to have a better control of which databases where checked and which weren't  
 -- A sudden server or instance reboot might affect this whole process...  
 DECLARE @db VARCHAR(255);  
  
 DECLARE checkdb_cursor CURSOR FOR   
 SELECT [database]   
 FROM master.dbo.CheckDB  
 WHERE result IS NULL;  
  
 OPEN checkdb_cursor    
 FETCH NEXT FROM checkdb_cursor INTO @db  
  
 WHILE @@FETCH_STATUS = 0    
 BEGIN    
 DECLARE @startTime DATETIME;  
 DECLARE @endTime DATETIME;  
  
       DECLARE @databaseSize BIGINT;  
 SELECT @databaseSize = size FROM master.dbo.CheckDB WHERE [database] = @db;  
  
 SET @startTime = GETDATE();  
  
 IF @databaseSize <= @dbSizeThreshold OR @force = 1  
 BEGIN  
 IF @version >= 2012  
 BEGIN  
 INSERT INTO #CheckDB_new   
 ([Error], [Level], [State], [MessageText], [RepairLevel],   
 [Status], [DbId], [DbFragId], [ObjectID], [IndexId], [PartitionId],   
 [AllocUnitId], [RidDbId], [RidPruId], [File], [Page], [Slot], [RefDbID],   
 [RefPruId], [RefFile], [RefPage], [RefSlot], [Allocation])   
 EXEC ('DBCC CHECKDB(['+@db+']) WITH TABLERESULTS');  
  
 SET @endTime = GETDATE();  
  
 UPDATE master.dbo.CheckDB  
 SET result = MessageText, checkdb_type = 'FULL', data_collection_timestamp = GETDATE(), completion_time = DATEDIFF(ss, @startTime, @endTime)  
 FROM #CheckDB_new  
 WHERE [Error] = 8989 AND [database] = @db;  
  
 IF @db = 'master'  
 INSERT INTO master.dbo.CheckDB   
 SELECT CAST(SERVERPROPERTY('SERVERNAME') AS VARCHAR(255)), 'mssqlsystemresource', ISNULL((SELECT CONVERT(DECIMAL(10,2),SUM(size / 1024.0)) AS 'size' FROM sys.sysaltfiles WHERE DBID = 32767),0), MessageText, 'FULL', GETDATE(), completion_time = DATEDIFF(ss, @startTime, @endTime), NULL  
 FROM #CheckDB_new  
 WHERE [Error] = 8989 AND DbId = 32767;  
  
 TRUNCATE TABLE #CheckDB_new;  
 END;  
 ELSE  
 BEGIN  
 INSERT INTO #CheckDB_old  
 ([Error], [Level], [State], [MessageText], [RepairLevel],   
 [Status], [DbId], [ObjectID], [IndexId], [PartitionId],   
 [AllocUnitId], [File], [Page], [Slot], [RefFile], [RefPage], [RefSlot], [Allocation])   
 EXEC ('DBCC CHECKDB(['+@db+']) WITH TABLERESULTS');  
  
 SET @endTime = GETDATE();  
  
 UPDATE master.dbo.CheckDB  
 SET result = MessageText, checkdb_type = 'FULL', data_collection_timestamp = GETDATE(), completion_time = DATEDIFF(ss, @startTime, @endTime)  
 FROM #CheckDB_old  
 WHERE [Error] = 8989 AND [database] = @db;  
  
 IF @db = 'master'  
 INSERT INTO master.dbo.CheckDB   
 SELECT CAST(SERVERPROPERTY('SERVERNAME') AS VARCHAR(255)), 'mssqlsystemresource', ISNULL((SELECT CONVERT(DECIMAL(10,2),SUM(size / 1024.0)) AS 'size' FROM sys.sysaltfiles WHERE DBID = 32767),0), MessageText, 'FULL', GETDATE(), completion_time = DATEDIFF(ss, @startTime, @endTime), NULL  
 FROM #CheckDB_old  
 WHERE [Error] = 8989 AND DbId = 32767;  
  
 TRUNCATE TABLE #CheckDB_old;  
 END;  
 END;  
 ELSE  
 BEGIN  
 IF @version >= 2012  
 BEGIN  
 INSERT INTO #CheckDB_new   
 ([Error], [Level], [State], [MessageText], [RepairLevel],   
 [Status], [DbId], [DbFragId], [ObjectID], [IndexId], [PartitionId],   
 [AllocUnitId], [RidDbId], [RidPruId], [File], [Page], [Slot], [RefDbID],   
 [RefPruId], [RefFile], [RefPage], [RefSlot], [Allocation])   
 EXEC ('DBCC CHECKDB(['+@db+']) WITH TABLERESULTS, PHYSICAL_ONLY');  
  
 SET @endTime = GETDATE();  
  
 UPDATE master.dbo.CheckDB  
 SET result = MessageText, checkdb_type = 'PHYSICAL ONLY', data_collection_timestamp = GETDATE(), completion_time = DATEDIFF(ss, @startTime, @endTime)  
 FROM #CheckDB_new  
 WHERE [Error] = 8989 AND [database] = @db;  
  
 IF @db = 'master'  
 INSERT INTO master.dbo.CheckDB   
 SELECT CAST(SERVERPROPERTY('SERVERNAME') AS VARCHAR(255)), 'mssqlsystemresource', ISNULL((SELECT CONVERT(DECIMAL(10,2),SUM(size / 1024.0)) AS 'size' FROM sys.sysaltfiles WHERE DBID = 32767),0), MessageText, 'PHYSICAL ONLY', GETDATE(), completion_time = DATEDIFF(ss, @startTime, @endTime), NULL  
 FROM #CheckDB_new  
 WHERE [Error] = 8989 AND DbId = 32767;  
  
 TRUNCATE TABLE #CheckDB_new;  
 END;  
 ELSE  
 BEGIN  
 INSERT INTO #CheckDB_old  
 ([Error], [Level], [State], [MessageText], [RepairLevel],   
 [Status], [DbId], [ObjectID], [IndexId], [PartitionId],   
 [AllocUnitId], [File], [Page], [Slot], [RefFile], [RefPage], [RefSlot], [Allocation])   
 EXEC ('DBCC CHECKDB(['+@db+']) WITH TABLERESULTS, PHYSICAL_ONLY');  
  
 SET @endTime = GETDATE();  
  
 UPDATE master.dbo.CheckDB  
 SET result = MessageText, checkdb_type = 'PHYSICAL ONLY', data_collection_timestamp = GETDATE(), completion_time = DATEDIFF(ss, @startTime, @endTime)  
 FROM #CheckDB_old  
 WHERE [Error] = 8989 AND [database] = @db;  
  
 IF @db = 'master'  
 INSERT INTO master.dbo.CheckDB   
 SELECT CAST(SERVERPROPERTY('SERVERNAME') AS VARCHAR(255)), 'mssqlsystemresource', ISNULL((SELECT CONVERT(DECIMAL(10,2),SUM(size / 1024.0)) AS 'size' FROM sys.sysaltfiles WHERE DBID = 32767),0), MessageText, 'PHYSICAL ONLY', GETDATE(), completion_time = DATEDIFF(ss, @startTime, @endTime), NULL  
 FROM #CheckDB_old  
 WHERE [Error] = 8989 AND DbId = 32767;  
  
 TRUNCATE TABLE #CheckDB_old;  
 END;  
 END;  
  
 -- Get the information for the "Last Known Good DBCC CHECKDB" execution   
 INSERT INTO #DBInfo ([ParentObject], [Object], [Field], [Value])   
     EXEC ('DBCC DBINFO(['+@db+']) WITH TABLERESULTS');  
  
 UPDATE master.dbo.CheckDB  
 SET last_good_dbcc = [Value]  
 FROM #DBInfo  
 WHERE [Field] = 'dbi_dbccLastKnownGood' AND [database] = @db;  
  
 IF @db = 'master'  
 UPDATE master.dbo.CheckDB  
 SET last_good_dbcc = (SELECT last_good_dbcc FROM master.dbo.CHECKDB WHERE [database] = 'master')  
 WHERE [database] = 'mssqlsystemresource';  
  
 TRUNCATE TABLE #DBInfo;   
  
 FETCH NEXT FROM checkdb_cursor INTO @db   
 END  
  
 CLOSE checkdb_cursor    
 DEALLOCATE checkdb_cursor    
  
 -- Drop whichever temporal table was created  
 IF NOT OBJECT_ID('tempdb..#CheckDB_old') IS NULL    
 DROP TABLE #CheckDB_old;   
  
 IF NOT OBJECT_ID('tempdb..#CheckDB_new') IS NULL  
 DROP TABLE #CheckDB_new;   
  
 IF NOT OBJECT_ID('tempdb..#DBInfo') IS NULL    
 DROP TABLE #DBInfo;   
END  
GO  
  
EXEC Simple_CHECKDB  
  
select * from master.dbo.CheckDB  

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.