Check for Corruption Across Multiple Databases

Check Multiple Databases Quickly

If you work in a shop with many SQL Servers, you’ve either already dealt with a storage issue or you will. Storage controllers going down, unexpected shutdowns, someone hitting the power button on a database server — all of these are good ways to end up with corruption. That’s why weekly DBCC checks are non-negotiable, but sometimes you need to run ad-hoc checks across a lot of databases quickly.

I ran into this situation after a storage controller failure knocked down several SQL instances. The task: check a large number of smaller databases for corruption quickly without blocking everything else for hours. The approach we settled on:

• Physical-only checks with DBCC CHECKDB (faster, still catches page corruption)
• Only target databases under 1GB (larger ones could be queued separately)
• Dynamic SQL so it could run across multiple servers via a management tool

Here’s the script:

1
-- store database names under 1GB (data files only)
2
DECLARE @dbcc_physical TABLE (
3
    name NVARCHAR(128) NOT NULL
4
);
5

6
INSERT INTO @dbcc_physical
7
SELECT d.name
8
FROM sys.databases d
9
INNER JOIN sys.master_files f ON d.database_id = f.database_id AND f.type = 0
10
WHERE d.name NOT IN ('master', 'model', 'msdb', 'tempdb')
11
GROUP BY d.name
12
HAVING SUM(f.size) < 131072; -- 131072 pages * 8KB = 1GB
13

14
-- cursor to run DBCC CHECKDB on each database
15
DECLARE @tableCursor CURSOR;
15 collapsed lines
16
DECLARE @databaseName NVARCHAR(128);
17

18
SET @tableCursor = CURSOR FOR SELECT name FROM @dbcc_physical;
19

20
OPEN @tableCursor;
21
FETCH NEXT FROM @tableCursor INTO @databaseName;
22

23
WHILE (@@FETCH_STATUS = 0)
24
BEGIN
25
    EXECUTE ('DBCC CHECKDB(' + QUOTENAME(@databaseName) + ') WITH PHYSICAL_ONLY;');
26
    FETCH NEXT FROM @tableCursor INTO @databaseName;
27
END;
28

29
CLOSE @tableCursor;
30
DEALLOCATE @tableCursor;

A few notes on the implementation:

• sys.master_files stores size in 8KB pages, so 131072 pages = 1GB. The original version used size < 1024 which filtered to databases under ~8MB — far too small.
• sys.databases gives you the database name; sys.master_files gives you the file sizes. You need to join them — querying name directly from sys.master_files returns the logical file name, not the database name.
• QUOTENAME() wraps the database name to handle spaces or special characters safely.
• PHYSICAL_ONLY skips logical consistency checks and focuses on structural integrity — significantly faster, appropriate for routine or recovery triage checks.

Adjust the size threshold and the system database exclusion list to fit your environment.

Key Takeaways

• sys.master_files.size is in 8KB pages — multiply by 8192 to get bytes, or compare to 131072 for a 1GB threshold.
• Use sys.databases for database names, not sys.master_files.name (which is the logical file name).
• DBCC CHECKDB WITH PHYSICAL_ONLY is significantly faster and appropriate for triage or high-volume checks.
• QUOTENAME() is non-optional when building dynamic SQL with database names.