0

Scheduled CHECKDB Taking Long Time To Run and Slowing Down Applications

Emeka OkekeDec 18, 2019

This error occurs sometimes when you issue TSQL CHECKDB command. You have about four-Terabytes OLTP database with seven or more file groups. You scheduled CHECKDB to run but it is taking long to execute and is slowing down applications and eventually fails with following errors  :

The Error

Error: 17053, Severity: 16, State: 1. Operating system error 665(The requested operation could not be completed due to a file system limitation) encountered. The operating system returned error 665(The requested operation could not be completed due to a file system limitation) to SQL Server during a write at offset %d in file

The Cause

Since NTFS size limit is usually a few Exabyte and the database volume here is 4TB, this error is not an NTFS size limit constraint. Because DBCC CHECKDB uses VSS (Volume Shadow Copy Service) to perform its operations, this error could have been caused by the VSS size limit constraint.

CHECKDB keeps a database online during its operations. As a result, its VSS backup operations adds more files to the existing files which could easily use up the volume's space for a large database with 4TB size. It could also be that at the time the DBCC CHECKDB command was issued, the server was experiencing high volume of transactions, therefore causing the snapshot file to grow beyond the 4TB size.

The Resolution

Typically VSS default size is less than 10% of the total drive size. We can set the Shadow Copies property of Disk Management option to "no limit" on windows server. We could also create new volumes and file groups, and then redistribute data across by moving larger data files to the new file group. We could do this with the following sample query:


ALTER DATABASE [DBNAME] ADD FILEGROUP [FILEGROUPNAME]
GO

ALTER DATABASE [DBNAME]
ADD FILE
( NAME = [NAME],
FILENAME = 'filepath',
SIZE = [SIZE],
MAXSIZE = [MAXSIZE],
FILEGROWTH = [FILEGROWTH])
TO FILEGROUP [FILEGROUPNAME]
GO

Emeka Okeke@sqldibia+ Follow
0
Emeka Okeke+ Follow
locationPennsburg, PennsylvaniajoinedDec 11, 2019

More from @sqldibia