Check multiple databases quickly

If you work or have worked, in a shop with lots of SQL servers then you either have, or will, experience an issue with storage. Like for instance, you’re storage controllers go down, knocking down your SQL instances. As we all know, this is a good way to corrupt databases. That’s why Sys Admins should never just hit the power button on a database server, but I digress.

I’ve recently encountered this problem, a couple of times in fact. While weekly DBCC checks are run, I was tasked with a way to check lots of databases for corruption quickly. It was decided that we would only do a physical check with DBCC, the databases should be under 1GB in size, and we wanted to be able to run the query across multiple servers quickly. Obviously, the size of the database can be modified.

I came up with this.

1
--create table variable
2
declare @dbcc_physical Table (
3
     name varchar(50) not null
4
);
5

6
-- select database names who are under 1GB in size, and store in table variable
7
INSERT INTO @dbcc_physical
8
SELECT name from sys.master_files
9
WHERE type = 0
10
AND size < 1024
11
AND name != 'modeldev';
12

13
-- create dynamic sql to execute dbcc check
14
DECLARE @tableCursor CURSOR,
15
@databaseName VARCHAR(100);
13 collapsed lines
16

17
SET @tableCursor = CURSOR FOR SELECT * FROM @dbcc_physical;
18

19
OPEN @tableCursor;
20
FETCH NEXT FROM @tableCursor INTO @databaseName;
21
WHILE(@@FETCH_STATUS = 0)
22
BEGIN
23
  EXECUTE ('DBCC CHECKDB(' + @databaseName + ') WITH PHYSICAL_ONLY;');
24

25
  FETCH NEXT FROM @tableCursor INTO @databaseName
26
END;
27
CLOSE @tableCursor;
28
DEALLOCATE @tableCursor;