Today I was doing my daily checks on one of my biggest SQL instances which has over 1,000 databases and noticed that SQL Agent was stopped. I couldn’t see any errors and was able to simply start it without any problems but I was concerned that the daily integrity check jobs had not run and I am way too cautious to get caught by not doing it manually.
Initially I wasn’t too sure how to do it very quickly without creating a script using DBCC CHECKDB, querying sys.databases with some dynamic T-SQL but decided to give the below script a try and luckily it worked.
EXEC sp_MSforeachDB ‘DBCC CHECKDB (?)’