Consider the following scenario: On a SQL Server 2005 or SQL Server 2008 server, you perform one of the following actions:
You create a database snapshot on a large database. After this you perform numerous data modification operations or maintenance operations in the source database.
You create a database snapshot on a mirror database
You execute DBCC CHECKDB family of commands to check the consistency of a large database and you also perform a large number of data changes in that database.
In this scenario, you notice the following errors reported in the SQL Server Error log depending onthe environment SQL Server is running on:
Windows Server 2003
The operating system returned error 1450(Insufficient system resources exist to complete the requested service.) to SQL Server during a write at offset 0x00002a3ef96000 in file with handle 0x0000000000000D5C. This is usually a temporary condition and the SQL Server will keep retrying the operation. If the condition persists then immediate action must be taken to correct it.
Windows Server 2008 and Windows Vista
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 0x00002a3ef96000 in file 'Sam.mdf:MSSQL_DBCC18'
In addition to these errors, you may also notice Latch Timeout errors as shown below:
Timeout occurred while waiting for latch: class 'DBCC_MULTIOBJECT_SCANNER', id 000000002C61DF40, type 4, Task 0x00000000038089B8 : 16, waittime 600, flags 0x1a, owning task 0x0000000006A09828. Continuing to wait.
Timeout occurred while waiting for latch: class 'ACCESS_METHODS_HOBT_COUNT', id 000000002C61DF40, type 4, Task 0x00000000038089B8 : 16, waittime 600, flags 0x1a, owning task 0x0000000006A09828. Continuing to wait.
Additionally, you may also notice blocking when you view various dynamic management views (DMV) like sys.dm_exec_requests, sys.dm_os_waiting_tasks, etc.
This problem happens if a large number of ATTRIBUTE_LIST_ENTRY is needed to maintain a heavily fragmented file in NFTS. This behavior is explained in following KB article:
967351A heavily fragmented file in an NTFS volume may not grow beyond a certain size
The sparse files created by SQL Server for the database snapshots can get fragmented to these levels when large amounts of data modifications happen for the life of these snapshot files.
For a complete background of how SQL Server Engine uses NTFS sparse files and alternate data streams, refer to the following links:
3029977 FIX: OS error 665 when you execute DBCC CHECKDB command for database that contains columnstore index in SQL Server 2014
Apply the Windows Fix from KB article 967351 and then format the volume by using the /L option to obtain large FRS.
Reduce the lifetime of DBCC CHECK commands by using the following performance enhancements and consequently avoid the 665 errors:
2634571 Improvements for the DBCC CHECKDB command may result in faster performance when you use the PHYSICAL_ONLY option Enable trace flags -T2562 and -T2549
945770 FIX: The DBCC CHECKDB statement takes longer to run Enable trace flag -T2566
3029825 FIX: DBCC CHECKDB take longer to run when SQL CLR UDTs are involved
Windows Server 2008 customers should apply the following fix:
957065Error status message from the ntfs.sys driver when some applications update very large files in Windows Server 2008 or in Windows Vista systems: "0xc0000427 STATUS_FILE_SYSTEM_LIMITATION"
SQL Server 2005 or SQL Server 2008 customers should strongly consider applying the following fixes as applicable to the version they are running:
961123FIX: The SQL Server service stops when you run one of the DBCC CHECK commands on a SQL Server 2005 database or when you create a database snapshot for a SQL Server 2005 database
967164FIX: The SQL Server service stops responding, and you receive error 1450 when you create database snapshot or run a DBCC CHECK statement on a database in SQL Server 2005 and in SQL Server 2008
967470FIX: Error message when you perform an update or a delete operation on a table that does not have a clustered index created in SQL Server 2005 and in SQL Server 2008: "The operating system returned error 1450"
Under certain conditions, you might still encounter the above mentioned errors even after applying these fixes. In that scenario, you can evaluate some of the workarounds discussed in the following blog post:
For more information about the products or tools that automatically check for this condition on your instance of SQL Server and on the versions of the SQL Server product, see the following table:
Product versions against which the rule is evaluated
System Center Advisor
Windows operating system missing update KB957065 to prevent NTFS sparse file fragmentation errors.
System Center Advisor checks the version of NTFS.SYS file on the monitored computer and generates this alert. Review the information in this article and apply the fix to prevent encountering the operating system error 665.
SQL Server 2008
SQL Server 2008 R2
SQL Server 2012
Windows Server 2008