Running DBCC CHECKDB on all databases at once !!!

Issue ::

some times it so happens .. that the  storage assigned to SQL servers may take a sudden

unexpected reboot .. and in those moments we may have LOST WRITES in

          databases .. which can cause them to be marked as suspect or causing 

          issues while accessing objects … 

 

in those moments we have to ensure consistancy and allocation across all DBs

are intact .. running DBCC CheckDB manually on each and every option is time

consuming (plus multiple windows  are to be opened)

SCRIPT ::  below script will help us run DBCC checkDB on all DBs once we have count of all DBs present on our instance ..  (the other option you have to achieve the same is to use CHECKDB job .. but with below script you will be able to view the errors in Query analyser itself)

 

1> use sp_helpdb .. to check total number of databases present on your instance …

according to the total number of DBs .. use dbid >= * and dbid <= * to cover all DBs

—Script to Run in different windows

—— by commenting and dbid >= * and dbid <= * part in the where clause

 

 

—————————————-SCRIPT START ————————————————-

DECLARE @name VARCHAR(50) — database name
DECLARE @SQL VARCHAR(256) — SQL command

 

 

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN (‘tempdb’)
and dbid >= 1 and dbid <= 20 —- window 1
–and dbid >= 21 and dbid <= 50 —- uncomment and run from QA window 2
–and dbid >= 51 and dbid <= 100 —- uncomment this and run from QA window 3
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

 

 

WHILE @@FETCH_STATUS = 0
BEGIN
–SET @fileName = @path + @name + ‘_’ + @fileDate + ‘.BAK’
—BACKUP DATABASE @name TO DISK = @fileName

 

 

set @SQL = ‘DBCC CHECKDB’ + ‘ ‘ + ‘(‘+@name + ‘)’
print @SQL

exec (@SQL)

 

 

FETCH NEXT FROM db_cursor INTO @name
END

 

 

CLOSE db_cursor
DEALLOCATE db_cursor

 

 

—————————————-SCRIPT END————————————————-

 

 

you should see the output similar to above ..

go throught the errors and see if Allocation or integrity errors are reported for any DB …

accordingly use Repair_REBUILD or REPAIR_ALLOW_DATA_LOSS of DBCC CheckDB ..

alway prefer to use lastest Good backup over using above repair option !!!!

ENJOY 🙂