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.
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.
- SQL SERVER – DBCC commands List – documented and undocumented
- SQL SERVER – Identify Oldest Active Transaction with DBCC OPENTRAN
- SQL SERVER – Shrinking Database is Bad – Increases Fragmentation – Reduces Performance
Reference: Pinal Dave (http://www.SQLAuthority.com)
