Database Consistency Checks DBCCs Tips

With most tasks that access database level objects there is always some type of locking that occurs that causes potential blocking situations. Unfortunately even processes such as DBCC SHOWCONTIG, which allows you to get table fragmentation information, also uses locks that could cause blocking issues. With SQL Server 2005 these issues have been resolved, but this is still an issue with SQL Server 2000. So how do you get around this issue?

The DBCC CHECKDB command checks the integrity of the objects in a database and should be run on a regular basis. One thing that this command does not check in databases created in versions prior to SQL Server 2005 is the integrity of the data in the columns until it has been run once with the DATA_PURITY option. Is there a way to tell which databases on my SQL Server instance are not using this option?

Every company wants to ensure the database integrity of their database systems. If you are not running a regular database consistency check against your databases, then your databases could have corruption without your knowledge. Therefore, it is important to run DBCC CHECKDB as part of your daily, weekly, monthly, etc. database maintenance routine. In fact, in my previous article on this topic, I showed you how we can setup a process to Capture and Store SQL Server Database Integrity History using DBCC CHECKDB. However, there is a cost to running DBCC CHECKDB as well. Clearly, the pros outweigh the cons, but there are considerations. What is the performance overhead of running a DBCC CHECKDB on your production systems, as it is unavoid

One process that should be run on a set basis to ensure integrity in your database is to run either DBCC CHECKDB or DBCC CHECKTABLE. These processes check allocation, structural, and logical integrity of the object or objects and report back any inconsistencies that are found. One thing that these processes have not check for in the past is the data itself to see if there are column values that are not valid or out-of-range.

In a previous tip we discussed 10 items that should be addressed to manage your SQL Server environment. These items included; backups, security, disaster recovery, etc... The second item on the list was to check database and table integrity. This is pretty much a simple task to perform and there is not much if any interaction that needs to take place between the DBA and SQL Server. Although this is pretty straightforward to setup and run, this is not always implemented and secondly the output is seldom reviewed to see if there are any issues. This tip will address some of the items you should consider when putting your integrity check process in place.

Microsoft does not guarantee that table and database space allocation information will be maintained accurately. In a high transaction OLTP database or after a large bulk copy of data has been performed, this can lead to inaccurate database space reporting when using the system stored procedure sp_spaceused.

I've read a lot of articles that talk about database pages and how they're used to store table data and index data. Is there some way I can actually look at this information?

Every company wants to ensure the database integrity of their database systems. If you are not running a regular database consistency check against your databases, then your databases could have corruption without your knowledge. Therefore, it is important to run DBCC CHECKDB as part of your daily, weekly, monthly, etc. database maintenance routine. In fact, in my previous article on this topic, I showed you how we can setup a process to Capture and Store SQL Server Database Integrity History using DBCC CHECKDB. However, there is a cost to running DBCC CHECKDB as well. Clearly, the pros outweigh the cons, but there are considerations. What is the performance overhead of running a DBCC CHECKDB on your production systems, as it is unavoid

I've read a lot of articles that talk about database pages and how they're used to store table data and index data. Is there some way I can actually look at this information?

In a previous tip we discussed 10 items that should be addressed to manage your SQL Server environment. These items included; backups, security, disaster recovery, etc... The second item on the list was to check database and table integrity. This is pretty much a simple task to perform and there is not much if any interaction that needs to take place between the DBA and SQL Server. Although this is pretty straightforward to setup and run, this is not always implemented and secondly the output is seldom reviewed to see if there are any issues. This tip will address some of the items you should consider when putting your integrity check process in place.

Microsoft does not guarantee that table and database space allocation information will be maintained accurately. In a high transaction OLTP database or after a large bulk copy of data has been performed, this can lead to inaccurate database space reporting when using the system stored procedure sp_spaceused.

One process that should be run on a set basis to ensure integrity in your database is to run either DBCC CHECKDB or DBCC CHECKTABLE. These processes check allocation, structural, and logical integrity of the object or objects and report back any inconsistencies that are found. One thing that these processes have not check for in the past is the data itself to see if there are column values that are not valid or out-of-range.

With most tasks that access database level objects there is always some type of locking that occurs that causes potential blocking situations. Unfortunately even processes such as DBCC SHOWCONTIG, which allows you to get table fragmentation information, also uses locks that could cause blocking issues. With SQL Server 2005 these issues have been resolved, but this is still an issue with SQL Server 2000. So how do you get around this issue?

The DBCC CHECKDB command checks the integrity of the objects in a database and should be run on a regular basis. One thing that this command does not check in databases created in versions prior to SQL Server 2005 is the integrity of the data in the columns until it has been run once with the DATA_PURITY option. Is there a way to tell which databases on my SQL Server instance are not using this option?

Every company wants to ensure the database integrity of their database systems. If you are not running a regular database consistency check against your databases, then your databases could have corruption without your knowledge. Therefore, it is important to run DBCC CHECKDB as part of your daily, weekly, monthly, etc. database maintenance routine. In fact, in my previous article on this topic, I showed you how we can setup a process to Capture and Store SQL Server Database Integrity History using DBCC CHECKDB. However, there is a cost to running DBCC CHECKDB as well. Clearly, the pros outweigh the cons, but there are considerations. What is the performance overhead of running a DBCC CHECKDB on your production systems, as it is unavoid

The DBCC CHECKDB command checks the integrity of the objects in a database and should be run on a regular basis. One thing that this command does not check in databases created in versions prior to SQL Server 2005 is the integrity of the data in the columns until it has been run once with the DATA_PURITY option. Is there a way to tell which databases on my SQL Server instance are not using this option?

I've read a lot of articles that talk about database pages and how they're used to store table data and index data. Is there some way I can actually look at this information?

Microsoft does not guarantee that table and database space allocation information will be maintained accurately. In a high transaction OLTP database or after a large bulk copy of data has been performed, this can lead to inaccurate database space reporting when using the system stored procedure sp_spaceused.

In a previous tip we discussed 10 items that should be addressed to manage your SQL Server environment. These items included; backups, security, disaster recovery, etc... The second item on the list was to check database and table integrity. This is pretty much a simple task to perform and there is not much if any interaction that needs to take place between the DBA and SQL Server. Although this is pretty straightforward to setup and run, this is not always implemented and secondly the output is seldom reviewed to see if there are any issues. This tip will address some of the items you should consider when putting your integrity check process in place.

With most tasks that access database level objects there is always some type of locking that occurs that causes potential blocking situations. Unfortunately even processes such as DBCC SHOWCONTIG, which allows you to get table fragmentation information, also uses locks that could cause blocking issues. With SQL Server 2005 these issues have been resolved, but this is still an issue with SQL Server 2000. So how do you get around this issue?

One process that should be run on a set basis to ensure integrity in your database is to run either DBCC CHECKDB or DBCC CHECKTABLE. These processes check allocation, structural, and logical integrity of the object or objects and report back any inconsistencies that are found. One thing that these processes have not check for in the past is the data itself to see if there are column values that are not valid or out-of-range.