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)
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.
Regards,
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
EXEC sp_who
If any entry with status runnable for that database, it is in use
i want to know which other pc is connecting my database.
how can i check this sate.
You can use a profiler or sp_who
EXEC sp_who