on 02-14-2011 7:40 AM
Hi experts,
It takes a few weeks to run checkdb in our database. Could I use check all tables in parallel instead? Is it equivalent?
Hi,
yes, checkdb contains a checktable for all tables and a check alloc, so if you run a DBCC CHECKALLOC and a DBCC CHECKTABLE for all tables, you should have the same result. You can use this script for the CHECKTABLE part:
DECLARE @tablename sysname
DECLARE @username sysname
DECLARE @cmd varchar(4000)
DECLARE crs CURSOR FOR
SELECT user_name(uid), name FROM sysobjects WHERE type IN ('U' ,'S') ORDER BY name
OPEN crs
FETCH NEXT FROM crs INTO @username, @tablename
WHILE (@@fetch_status = 0) BEGIN
PRINT convert(char(25),getdate()) + @username + '.' + @tablename
SET @cmd = 'DBCC checktable ([' +@username + '.' + @tablename +']) WITH tablock, no_infomsgs'
EXEC (@cmd)
FETCH NEXT FROM crs INTO @username, @tablename
END
PRINT 'FINISHED'
CLOSE crs
DEALLOCATE crs
You can change the select for the cursor for your needs.
Best regards
Clas
Edited by: Clas Hortien on Feb 14, 2011 8:48 AM
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
90 | |
10 | |
10 | |
10 | |
7 | |
7 | |
6 | |
5 | |
4 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.