DBCC CheckDB SQL Job

This job should be ran weekly or monthly. Not recommended to run daily as it can take a long time.

Type: Transaction-SQL script (T-SQL)

Database: master

Command:

DECLARE @DB SYSNAME  

DECLARE curDB CURSOR FORWARD_ONLY STATIC
FOR
SELECT  [name]
    FROM    master.sys.databases
    WHERE   [name] NOT IN ( 'model', 'tempdb' )
            AND state_desc = 'ONLINE'
			AND is_read_only = 0
            AND [name] NOT IN (
				--DBCC CheckDB should be skipped on non-primary replicas
				SELECT  dc.database_name
				FROM    sys.availability_replicas r
						JOIN sys.availability_databases_cluster dc ON dc.group_id = r.group_id
						LEFT JOIN sys.dm_hadr_availability_group_states gs ON gs.group_id = r.group_id
				  AND gs.primary_replica = r.replica_server_name
				WHERE   gs.primary_replica IS NULL
				AND r.replica_server_name = @@SERVERNAME  )				
    ORDER BY [name] 
   

OPEN curDB  
FETCH NEXT FROM curDB INTO @DB  
WHILE @@FETCH_STATUS = 0 
    BEGIN  
		PRINT @DB
        
		DBCC CHECKDB ( @DB) WITH   NO_INFOMSGS
        FETCH NEXT FROM curDB INTO @DB  
    END  
    
CLOSE curDB  
DEALLOCATE curDB