DBCC CHECKFILEGROUP Command On SQL Server

DBCC CHECKFILEGROUP checks the consistency of all tables and indexed views on the filegroup. To examine in more detail what Filegroup is, you can read the article “How To Create a Database On SQL Server

DBCC CHECKFILEGROUP actually runs DBCC CHECKALLOC on the filegroup in the background and DBCC CHECKTABLE on all tables on the filegroup. Works very similar to DBCC CHECKDB and DBCC CHECKTABLE. You may want to read the following articles about these commands.

DBCC CHECKDB Command On SQL Server“,

DBCC CHECKALLOC Command On SQL Server“,

DBCC CHECKTABLE Command On SQL Server

DBCC CHECKFILEGROUP has several different uses. Let’s examine them all one by one:

Consistency Check:

The following command detects logical and physical errors in the PRIMARY filegroup in the Test database.

Consistency Check With NOINDEX:

With the help of the following query, errors other than non clustered indexes in the PRIMARY Filegroup are detected.

Correct Errors With REPAIR_REBUILD:

You can run the DBCC CHECKFILEGROUP command to correct errors on non-clustereded indexes in the tables on the PRIMARY filegroup by using the following command without data loss.

The REPAIR_REBUILD operation does not fix any errors that contain filestream data.

Correct Errors With REPAIR_ALLOW_DATA_LOSS:

You can run the DBCC HECKFILEGROUP command to correct all errors in the table with the risk of data loss using the command below.

Even if you don’t write ALL_ERRORMSGS at the end of the script, this is the default value. It means to show all errors received for each object.

NO_INFOMSGS at the end of the script means that it does not display informational messages.

NOTE1: After you make sure that the database is suspect, the first thing we need to do before we start the attempt to recover is to get a backup of the suspect database !!!

NOTE2: After running DBCC CHECKFILEGROUP with REPAIR_REBUILD or REPAIR_ALLOW_DATA_LOSS, I recommend that you run the DBCC CHECKCONSTRAINTS command. You can find information about this command in my article “DBCC CHECKCONSTRAINTS Command On SQL Server

Consistency Check With Lock:

If you run using the following command, this command puts the lock instead of using the internal database snapshot. You may want to read my article “SQL Server Lock Types“.

Calculate Necessary Space For Consistency Check:

If you use the following command, no consistency check is performed. Only the amount of space required in tempdb is calculated so that a consistency test can be performed, and it produces an output as follows.

Only PHYSICAL Consistency Check:

When you run it using the following command, only the physical consistency check is performed. It is not done logically.

If you ask me, using this command doesn’t make much sense. Because it doesn’t control its logical consistency. Of course, they offered us such an option to be used in special cases.

With the PHYSICAL_ONLY command, you cannot perform any REPAIR operation. Also, when you use PHYSICAL_ONLY, the file stream data is not checked. You can find information about File Stream in my article “What is File Stream On SQL Server“.

For faster completion of DBCC CHECKFILEGROUP, you can increase maxdop (max degree of paralellism) from server configurations before running DBCC CHECKFILEGROUP. If your system is an oltp system and consists entirely of small transactions, increasing the maxdop may slow the performance of the running system. Therefore, after increasing the maxdop, you should inspect the system and undo the change in any performance problem. To correctly set the maxdop value, you can use the article “sp_configure (Server-Level Configurations in SQL Server)“.

If a table with partition is on more than one filegroup, only the part of the table on the corresponding filegroup is checked. For more information about Partition, please refer to “How To Create Partition On SQL Server“.

When to use DBCC CHECKFILEGROUP?

Usually used in large databases. For example, a large table in a large database was partitioned, and filegroups from previous years were set to read only. To create Partition, I suggest you read my article “How To Create Partition On SQL Server“.

Consider that the total size is 300TB and read only data is 280TB. Instead of running DBCC CHECKDB for the entire database, we may want to run DBCC CHECKFILEGROUP only for non-read only file groups.

For read only file groups, we may run PHYSICAL_ONLY periodically.

For large databases, getting backup of 300 TB each time will be very costly. That’s why we may need FileGroup backup-restore. The following article may be useful.

FileGroup Backup and FileGroup Restore Operations On SQL Server

You must also run DBCC CHECKALLOC and DBCC CHECKCATALOG in active file groups. Below you will find articles about these commands.

DBCC CHECKALLOC Command On SQL Server“,

DBCC CHECKCATALOG Command On SQL Server

You can also use Maintenance Plan to run these commands regularly. You can also find articles on Maintenance Plans below.

Checking Database Consistency Using Maintenance Plan“,

“Reorganizing Indexes Using Maintenance Plan“,

Rebuilding Indexes Using SQL Server Maintenance Plan“,

Get Full Backup Using SQL Server Maintenance Plan“,

Get Differential Backup Using SQL Server Maintenance Plan“,

Get Log Backup Using SQL Server Maintenance Plan“,

Updating Statistics Using Maintenance Plan“,

Deleting Backup, Restore, Job, or Maintenance Plan History Using Maintenance Plan“,

Running an Existing Job Using Maintenance Plan“,

Deleting Files with a Specified Extension in a Folder Using  Maintenance Plan(Maintenance Cleanup Task)

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *