There may come a time that you get errors, indicating corruption, in to your MS-SQL database. Do not panic, as most of the time these are recoverable.
The first place to start is using the database check utility, and that is what I am going to discuss today.
The MS-SQL database check utility is DBCC. There are several options that can be run via DBCC, but the place to start is with the checkdb. The checkdb function of DBCC checks several different areas of the database. The MS-SQL documentation states that checkdb:
- Runs DBCC CHECKALLOC on the database.
- Runs DBCC CHECKTABLE on every table and view in the database.
- Runs DBCC CHECKCATALOG on the database.
- Validates the contents of every indexed view in the database.
- Validates link-level consistency between table metadata and file system directories and files when storing varbinary(max) data in the file system using FILESTREAM.
- Validates the Service Broker data in the database.
That is a pretty thorough checking of the database, and should uncover any issues with corruption, or database consistency.
The basic command is "DBCC checkdb(‘Database-name’)". So, if you have a database called "MyDatabase", running "DBCC checkdb(‘MyDatabase’)" will do all of the checking mentioned above on your database.
I will warn you, however, that you will get a report on every table, index, and such, as to what checkdb finds. Since we are concerned here with finding any problems, and not looking at a list of the possibly hundreds of tables that do not have any issues, let’s tell DBCC and checkdb not to report those. This is done with the "WITH NO_INFOMSGS" clause. So, the full command is now "DBCC checkdb(‘MyDatabase’) WITH NO_INFOMSGS". Now, what you will see is information for only tables that have any corruption or consistency issues. Hopefully, you do not see anything reported, which means that no problems were detected. If there are problems detected, then it will also report information as to what the problem is, and the minimum repair level that would be required. Getting into doing those repairs will be the subject of a future blog.