You are currently offline, waiting for your internet to reconnect

OS errors 1450 and 665 are reported for database data files

Extended support for SQL Server 2005 ends on April 12, 2016

If you are still running SQL Server 2005 after April 12, 2016, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

Symptoms

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

Cause

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:

  • 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:

Resolution
  • 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:
    • 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:

Operating system error (665 – File System Limitation) not just for DBCC anymore

Sparse files – supported on both NTFS and REFS

DBCC CHECKDB behavior when the SQL Server database is located on an ReFS volume 

More Information


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:

 Rule software

 Rule title

 Rule description

 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

 

Note This is a "FAST PUBLISH" article created directly from within the Microsoft support organization. The information contained herein is provided as-is in response to emerging issues. As a result of the speed in making it available, the materials may include typographical errors and may be revised at any time without notice. See Terms of Use for other considerations.
Properties

Article ID: 2002606 - Last Review: 08/05/2015 20:56:00 - Revision: 8.0

Microsoft SQL Server 2005 Enterprise Edition, Microsoft SQL Server 2008 Enterprise

  • KB2002606
Feedback
osoft.com/ms.js"> > arCustomerTracking = 1; var Route = "76500"; var Ctrl = ""; document.write(" var Ctrl = ""; document.write(" p;did=1&t=">