DBCC CHECKDB -- Script use osql to check all Databases

DBCC CheckDB (’dbname’) is used to check and optionally repair the Database Allocation Consistency and the Index Pointer Correctness of the Database. It is the combination of DBCC CheckAlloc and DBCC CheckTable commands.



DBCC CheckDB can run when the system is running, but it is not recommended to run when Intensive Transactions are in progress, or when the database backup is running. DBCC CheckDB is using tempdb to sort and store working data, so it is recommended to make tempdb big enough to speed up the Check/Repair Process.



If the database only reports allocation error, we can just run DBCC CheckAlloc to check and repair, save time and resources. If we just want to check/repair the data, index, and field type text, ntext, and image fields of certain table, we can just run DBCC CheckTable (’tablename’).



Below are the command syntax and the examples of DBCC CheckDB:



Syntax
DBCC CHECKDB
( ’database_name’
[ , NOINDEX
| { REPAIR_ALLOW_DATA_LOSS
| REPAIR_FAST
| REPAIR_REBUILD
} ]
) [ WITH { [ ALL_ERRORMSGS ]
[ , [ NO_INFOMSGS ] ]
[ , [ TABLOCK ] ]
[ , [ ESTIMATEONLY ] ]
[ , [ PHYSICAL_ONLY ] ]
}
]



Examples:
==========
A. Check both the current and the pubs database
This example executes DBCC CHECKDB for the current database and for the pubs database:

-- Check the current database. DBCC CHECKDB GO -- Check the pubs database without nonclustered indexes:

DBCC CHECKDB (’pubs’, NOINDEX)
GO

B. Check the current database, suppressing informational messages
This example checks the current database and suppresses all informational messages:


DBCC CHECKDB WITH NO_INFOMSGS
GO


/*
**Author: Bahaa Fadam
**Email: bahaa.Fadam@gmail.com
**Creation Date 12-11-2008
**Script that Executes DBCC ChekDB in all the databases
**and records the result in a log file named C:\CheckDB\DatabaseNameGeneratedNumber.log
**It is necesary to create a directory in c:\ with the name "Checkdb".
**In this script is null, but change it with your own.
*/
Set Nocount on
Declare @dbname varchar(100)

Declare db Cursor For --Cursor that holds the names of the databases without Pubs and Northwind
Select name from master.dbo.sysdatabases
Where name not in ('Pubs','Northwind')



DECLARE @dDate datetime
DECLARE @sTime varchar(8)

SELECT @dDate = getdate()
--Time to name the .log file
SELECT @sTime = RIGHT(REPLICATE('0', 8) +
CONVERT(varchar,(3600 * DATEPART(hour, @dDate)) + (60 * DATEPART(minute, @dDate)) +
DATEPART(second, @dDate)) + CONVERT(varchar,DATEPART(ms, @dDate)), 8)



Declare @osql varchar(1000)
/*
**Then I create a Subdirectory in c:\Checkdb\ with the Random number as name.
*/
Declare @mkdir varchar(100)
Set @mkdir='EXEC master.dbo.xp_cmdshell '+''''+'mkdir c:\CheckDb\'+@sTime+''''
EXEC (@mkdir)

/*
**I use the cursor to execute the osql statement in all the databases
**which log every DBCC Checkdb in the path created before.
**If the password of the SA is not null, change it here.
*/

Open db
Fetch Next from db into @dbname
While @@Fetch_status=0
Begin
Set @osql='EXEC master.dbo.xp_cmdshell '+''''+'osql -E -Q"DBCC Checkdb ("'+@dbname+'") with no_infomsgs" -o C:\CheckDB\'+@sTime+'\'+@dbname+'_'+@sTime+'.log'+''''
EXEC (@osql) --Execute the osql statement
Fetch Next from db into @dbname
End
Close db

/*
**This section is in coments because it enable you to send the log files by E-mail.
**If you want to recieve all the log files by email uncoment this section to enable the script.
**You must have configured a mail client in your server

Declare @mail Varchar(1000)
Open db
Fetch Next from db into @dbname
While @@Fetch_status=0
Begin
Set @mail='EXEC master.dbo.xp_sendmail @recipients='+''''+'your@adress.com'+''''+',
@subject='+''''+'Log of execution of CheckDB in Database '+@dbname+''''+',
@message='+''''+'Check the log to see if an error ocurred while executing CheckDB in database '+@dbname+''''+',
@attachments='+''''+'C:\CheckDB\'+@date+'\'+@dbname+'_'+@date+'.log'+''''
Fetch Next from db into @dbname
EXEC (@mail)
End

--Close and Deallocate the Cursor
Close db
**Uncoment up to here to use
*/
Deallocate db
======================================================================
OR:
----
EXEC sp_MSforeachdb 'DBCC CHECKDB(?)'
EXEC sp_MSforeachdb @command1='PRINT ''Listing ?''',@command2= 'USE ?
SELECT DB_NAME()'
=======================================================================