DBCC CHECKFILEGROUP Command On SQL Server

dbtut January 1, 2019 MSSQL

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.

USE Test
GO
DBCC CHECKFILEGROUP ('PRIMARY')

USE Test

DBCC CHECKFILEGROUP ('PRIMARY')

Consistency Check With NOINDEX:

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

USE Test
GO
DBCC CHECKFILEGROUP (N'PRIMARY',NOINDEX)

USE Test

DBCC CHECKFILEGROUP (N'PRIMARY',NOINDEX)

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.

USE Test
GO
ALTER DATABASE Test SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DBCC CHECKFILEGROUP (N'PRIMARY',REPAIR_REBUILD) WITH NO_INFOMSGS, ALL_ERRORMSGS;
GO
ALTER DATABASE Test SET MULTI_USER;

USE Test

ALTER DATABASE Test SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

DBCC CHECKFILEGROUP (N'PRIMARY',REPAIR_REBUILD) WITH NO_INFOMSGS, ALL_ERRORMSGS;

ALTER DATABASE Test SET MULTI_USER;

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.

USE Test
GO
ALTER DATABASE [Test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DBCC CHECKFILEGROUP (N'PRIMARY', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
GO
ALTER DATABASE [Test] SET MULTI_USER;

USE Test

ALTER DATABASE [Test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

DBCC CHECKFILEGROUP (N'PRIMARY', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;

ALTER DATABASE [Test] SET MULTI_USER;

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“.

USE Test
GO

DBCC CHECKFILEGROUP (N'PRIMARY') WITH TABLOCK,NO_INFOMSGS, ALL_ERRORMSGS;

USE Test

DBCC CHECKFILEGROUP (N'PRIMARY') WITH TABLOCK,NO_INFOMSGS, ALL_ERRORMSGS;

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.

USE Test
GO
DBCC CHECKFILEGROUP (N'PRIMARY') WITH ESTIMATEONLY,NO_INFOMSGS, ALL_ERRORMSGS;
Estimated TEMPDB space (in KB) needed for CHECKFILEGROUP on database Test = 467.

USE Test

DBCC CHECKFILEGROUP (N'PRIMARY') WITH ESTIMATEONLY,NO_INFOMSGS, ALL_ERRORMSGS;

Estimated TEMPDB space (in KB) needed for CHECKFILEGROUP on database Test = 467.

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“.

USE Test
GO
DBCC CHECKFILEGROUP (N'PRIMARY') WITH PHYSICAL_ONLY,NO_INFOMSGS, ALL_ERRORMSGS;

USE Test

DBCC CHECKFILEGROUP (N'PRIMARY') WITH PHYSICAL_ONLY,NO_INFOMSGS, ALL_ERRORMSGS;

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“,