Home > T-sql > Running DBCC CHECKDB on all databases at once !!!

Running DBCC CHECKDB on all databases at once !!!

Writing with Ink

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————————————————-

 

DBCC_CHECKDB_ALL

 

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 🙂

 

 

Advertisements
  1. Alex Jahn
    March 19, 2015 at 7:23 am

    Your Script will never work in an Enterprise Environment. You sould consider that there are DB-Names with Special characters (e.g. “DB-Name1”). Most of SharePoint-DBs have Special characters. So add ‘[‘ and ‘]’ to your script. Also your Script will be interrupted if DBCC runs into an error. For te missing DBs DBCC will not be executed. Why printing the Statement ? That makes no sense. DBCC should be implemeted as a Job.

  2. April 24, 2015 at 12:09 pm

    I think the admin of this website is actually working
    hard for his site, because here every material is quality based
    stuff.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: