SQL SERVER – Check Database Integrity for All Databases of Server – DBCC CHECKDB

Today we will see quick script which will check integrity of all the databases of SQL Server. We will learn about DBCC CHECKDB in this blog post.

EXEC sp_msforeachdb 'DBCC CHECKDB(''?'')'

The above script will return you lots of messages in resultset. If there are any errors in resultset they will be displayed in red text. If everything is black text there is no error. The typical output of above script will be like image included in the article.

SQL SERVER - Check Database Integrity for All Databases of Server - DBCC CHECKDB dbcccheckdb

The image displayed above is only partial image.

Note from MSDN: DBCC CHECKDB is supported on databases that contain memory-optimized tables, but validation only occurs on disk-Based tables. However, as part of database backup and recovery, a CHECKSUM validation is done for files in memory-optimized filegroups. Since DBCC repair options are not available for memory-optimized tables, you must back up your databases regularly and test the backups. If data integrity issues occur in a memory-optimized table, you must restore from the last known good backup.

Here are few relevant blog posts about DBCC.

Reference: Pinal Dave (http://www.SQLAuthority.com)

Best Practices, SQL Scripts, SQL Server, SQL Server DBCC
Previous Post
SQLAuthority News – SQL Server 2008 Book Online Updated in October 2008
Next Post
SQL SERVER – Delete Backup History – Cleanup Backup History

Related Posts

7 Comments. Leave new

  • Yes this is a neat trick, however, sp_msforeachdb is going to be deprecated in a future release of SQL and Microsoft doesn’t recommend using it in scripts.

  • Hi Pinal,

    Its really a nice script & I am using this in our enviornment. But I need you to kindly explain the results of DBCC CHECKDB.How to read the results of this query.

    Thanks in advance.


    Sanjeev Kumar

  • Pls help me……….
    i have design the website in asp.net……..
    but have some problem…..
    I set the question and answers randomly from the sql database….. and also check the result of the question in the database. checking is the problem……….
    When page load occurs every questions are checked before answered…….. i have confused with this……

    thanks in advance

  • how to find sql server database is in use yes/no

  • i want to know which other pc is connecting my database.
    how can i check this sate.


Leave a Reply