On a SQL Server computer, assume that 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 on the 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, Windows Vista, and later versions of server and client operating systems
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 occurs if a large number of ATTRIBUTE_LIST_ENTRY instances are needed to maintain a heavily fragmented file in NFTS. This behavior is explained in following KB article:
- 967351 A 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:
- How It Works: SQL Server 2005 Database Snapshots (Replica)
- SQL Server reports operating system error 1450 or 1452 or 665 (retries)
- How It Works: SQL Server Sparse Files (DBCC and Snapshot Databases) Revisited
- How Database Snapshots Work
- DBCC CHECKDB (Transact-SQL) [See Remarks section for "Internal Database Snapshot"]
- New extended event to track writes to the snapshot sparse file
- Break up the large database into smaller files. For example, if you have one 8-TB data file you can break it up into eight 1-TB data files. High-level these are the steps to accomplish this:
- Add the 7 new 1 TB files to the same file group.
- Rebuild the clustered indexes of the existing tables and this will automatically spread the data of each table among the 8 files. If a table does not have a clustered index, then create one and then drop it to accomplish the same.
- Shrink the original 8 TB file down , now that is about about 12-15% full.
- Consider using a ReFS which does not have the same ATTRIBUTE_LIST_ENTRY limits that NTFS presents. You must reformat the volume by using ReFS.
- Consider defragmenting the volume where the database files reside. For more information, see Operating System Error (665 – File System Limitation) Not just for DBCC Anymore.
- Apply the Windows Fix from KB article 967351 and then format the volume by using the /L option to obtain large FRS.
- 3029977 FIX: OS error 665 when you execute DBCC CHECKDB command for database that contains columnstore index in SQL Server 2014
- 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:
- 957065 Error 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:
- 961123 FIX: 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
- 967164 FIX: 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
- 967470 FIX: 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, see the following Microsoft resources: