DetailPage-MSS-KB

Microsoft small business knowledge base

Article ID: 2002606 - Last Review: August 9, 2012 - Revision: 5.0

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

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

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 (http://go.microsoft.com/fwlink/?LinkId=151500) for other considerations.

Applies to
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2008 Enterprise
Keywords: 
KB2002606
Share
Additional support options
Ask The Microsoft Small Business Support Community
Contact Microsoft Small Business Support
Find Microsoft Small Business Support Certified Partner
Find a Microsoft Store For In-Person Small Business Support