Sparse File Errors: 1450 or 665 due to file fragmentation: Fixes and Workarounds

You might be familiar with the sparse file problem that Bob Dorr has blogged about in the past. https://blogs.msdn.com/psssql/archive/2008/07/10/sql-server-reports-operating-system-error-1450-or-1452-or-665-retries.aspx. We wanted to update you with the work we have been involved in the past few months. There are several fixes that we plan to release or already released to address the different aspects of this problem. The list provided below summarizes all of the documentation and fix available for you regarding this problem. We will keep this list updated as we come across more information or changes.

Windows Server 2003, 2008 Windows Vista		967351	A heavily fragmented file in an NTFS volume may not grow beyond a certain size	The article explains the root cause of this problem and the technical details of the limitation.
Windows Server 2008 RTM Windows Vista SP1	Regular QFE	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"	Windows Vista RTM and Win 20008 RTM had a problem that reduces the attribute limits to a much smaller number than what Windows 2003 had. This hotfix increases the attribute limitation in Windows 2008 and Vista OS and brings it on par with the Windows 2003 limit. If you are running Windows Vista or Windows 2008 and you encounter this problem, the first step is to apply this fix.
SQL Server 2005	SP2 CU12	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	When running DBCC CHECKDB command and if you run into this limitation, you could encounter Out Of Memory errors in SQL Server. This is because the children threads associated with the DBCC CHECKDB command starts accumulating error messages relating to the internal snapshot failures and quickly consumes all the available memory configured for the SQL Server instance. Under such conditions, you will notice that MEMORYCLERK_SQLGENERAL consuming a big portion of the SQL Server memory. Applying this fix will make sure that such memory consumption will not happen.
SQL Server 2005	SP3 CU1
SQL Server 2008	RTM

Comments

• Anonymous
  November 25, 2013
  I have Windows 2008 R2 x64 in a DW environment with 200+ gb of ram, and after a integrity check maintenance plan (DBCC CHECKDB) runs, I get : DESCRIPTION: 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 0x00002a6a490000 in file 'L:MSSQL11.MSSQLSERVERMSSQLDatamydb.mdf:MSSQL_DBCC10'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

• Anonymous
  September 09, 2014
  You will need to format your disk with /L again. This will increase Bytes Per FileRecord Segment from 1024  to 4K bytes. And possibly solve your problem.

• Anonymous
  January 13, 2015
  Sharing some windows updates which are applicable on Win 2012 R2 & worthy instead of /L. There ‘ve been some changes in the NTFS on Win 2012 R2 once these HF are applied. With the NTFS allocation logic changed, the improvement is seen with file performance and fragmentation as the OS allocates larger blocks. support.microsoft.com/.../2919355 support.microsoft.com/.../2964438

• Anonymous
  September 20, 2015
  Hi there, we have recently encountered the same error. This involves very big database files in terabytes. So we have formatted the relevant mountpoints to /L. But after that it is still spitting out the same error ie Error 665... and followed by "The Database ... was shutdown due to error 3314 in routine 'XdesRMReadWrite::RollbackToLsn." Can you tell us how to best resolve this issue please? Your feedback is greatly appreciated.