CSS SQL Server Engineers

This is the official team Web Log for Microsoft Customer Service and Support (CSS) SQL Support. Posts are provided by the CSS SQL Escalation Services

SQL Server reports operating system error 1450 or 1452 or 665 (retries)

SQL Server reports operating system error 1450 or 1452 or 665 (retries)

  • Comments 9

Overview

The error: 1450/1452 insufficient system resources exist to complete the requested service.

These are not a SQL Server based errors. The error cause is related to a depleted system resource (non-paged pool, paged pool ,...). These issues have to be tracked down at the operating system level. This involves collection of system level performance monitor counters and evaluating the basic health of the overall system.

/3GB
A commonly overlooked configuration option is the use of /3GB. The /3GB boot switch limits the operating system space to 1GB on a 32 a bit system. If your system is running with /3GB and you can't pinpoint the resource depletion consider removing the /3GB boot switch.

SQL Server Retries

When these errors occur they usually appear during SQL Server I/O operations (ReadFile, WriteFile, ReadFileScatter, WriteFileGather, DeviceIoControl, …) SQL Server identifies the situation, pauses briefly (a few milliseconds) to allow the system to respond and retries the I/O operation.

References: http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx and http://www.microsoft.com/technet/prodtechnol/sql/2005/iobasics.mspx

Specialized Symptom

For SQL Server 2005 there is another 1450/665 condition that can arise when performing an online DBCC CHECKDB or if you have snapshot databases (Create Database For Snapshot) defined.

Reference: http://blogs.msdn.com/psssql/archive/2008/02/07/how-it-works-sql-server-2005-database-snapshots-replica.aspx

When a sparse file (used for snapshot database files) is populated Windows limits the amount of data that may reside in the file. Once the amount of data stored in the sparse file exceeds the limit further data storage in the file may be prevented.

· Windows 2003 - 64GB (Error 1450 returned)
· Windows 2008 and Vista - 16GB (Error 665 returned)

Windows 2003 Error: The operating system returned error 1450(Insufficient system resources exist to complete the requested service.) to SQL Server during a write at offset 0x000031abb4e000 in file with handle 0x00000F74. 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 2008 Error: 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 0x000005bd3dc000 in file 'Test.mdf:MSSQL_DBCC8'

Online DBCC uses an internal snapshot database (secondary file stream like H:\MSSQL\Data\MyDB_Data.MDF:MSSQL_DBCC8). If the total copy on write, page copy activity exceeds these limits DBCC may stall, hang or fail. Some of the messages you may see in the SQL Server error log are shown.

The operating system returned error 1450(Insufficient system resources exist to complete the requested service.) to SQL Server during a write at offset 0x000031abb4e000 in file with handle 0x00000F74. 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.

Timeout occurred while waiting for latch: class 'ACCESS_METHODS_HOBT_COUNT', id 6092AFA4, type 4, Task 0x00BD4208 : 0, waittime 600, flags 0x98, owning task 0x00000000. Continuing to wait.

*******************************************************************************
* BEGIN STACK DUMP:
* 06/26/08 12:08:00 spid 89
* Latch timeout

Other actions that are attempting to modify pages may become stalled attempting to write to the snapshot file and checkpoint can be a secondary victim.

SQL Server Mini-Dumps

The built in SQL Server health checks can detect several of the conditions, log errors and generate mini-dumps in the LOG directory. Using the debug techniques outlined in the following article http://www.microsoft.com/technet/prodtechnol/sql/2005/diagandcorrecterrs.mspx you too can look at the information.

Using the Windows Debugging Tools and the public symbols for Windows and SQL Server the following information can be obtained. You can then search for the Sleep call. There are some threads that this is valid for but for this issue you don’t want to see the SyncWritePreemptive calling Sleep.

0:000> .sympath
Symbol search path is: http://msdl.microsoft.com/download/symbols

0:000> ~*kb


27 Id: 804.b30 Suspend: 1 Teb: 7ff9a000 Unfrozen
ntdll!KiFastSystemCallRet
ntdll!NtDelayExecution+0xc
kernel32!SleepEx+0x68
kernel32!Sleep+0xf
sqlservr!FCB::SyncWritePreemptive+0x228
sqlservr!FCB::CopyPageToReplicas+0x327
sqlservr!BUF::CopyOnWrite+0x56

The previous stack shows the CopyOnWrite behavior attempting to secure the page in the replica (snapshot) before the page can be changed. Sleep is only called when 1450 or 1452 is encountered.

Resolution:

· Run DBCC CHECKDB in OFFLINE mode (WITH TABLOCK)

· Run DBCC CHECKDB at a time when less data modification is taking place

· Divide the database into a few more files. The limitations are per sparse file and each database snapshot creates matching sparse files for each database file. For example if the database files are each limited to 64GB in total size you can't exceed the limitation on a per file basis.

· Remove snapshot databases that have grown large.

Aug 14, 2008 Additional Information

For optimization, SQL Server can allocate a 64K extent when writing the first 8K page to the snapshot database.   The other pages in the extent are zero'ed including the page header to indicate the page has not been copied to the replica yet.   As such, a scattered set of dirty activity could approach 1/8th of the limit (8 pages per extent and only one page dirty) so the limit may be encountered at a lower dirty page rate than expected.

Aug 25, 2008 Additional Information (Limitations)

Testing shows that the size limit for the spare file can vary based on the allocation pattern.   The limits shown above should be considered the MAXIMUM limit but due to allocation your mileage may vary.

Bob Dorr
SQL Server Senior Escalation Engineer

Leave a Comment
  • Please add 4 and 5 and type the answer here:
  • Post
  • PingBack from http://soci.hu/blog/index.php/2008/08/13/the-requested-operation-could-not-be-completed-due-to-a-file-system-limitation/

  • Sarah and I have been approached by several customers wanting to know more about sparse file allocation

  • Sparse Files are an important concept and the first time I saw that with SQL Server 2005 I was really

  • You might be familiar with the sparse file problem that Bob Dorr has blogged about in the past. http://blogs.msdn.com/psssql/archive/2008/07/10/sql-server-reports-operating-system-error-1450-or-1452-or-665-retries.aspx

  • Hi,

    I have the same problem, but with backup routine, can you help me?

    2010-06-30 06:51:44.31 spid76      BackupDatabase: Database Calibrator

    2010-06-30 06:51:44.34 spid76      Backup: Streams open

    2010-06-30 06:51:44.43 spid76      BackupDatabase: Checkpoint done

    2010-06-30 06:51:44.45 spid76      BackupDatabase: Work estimates done

    2010-06-30 06:51:44.46 spid76      BackupDatabase: Configuration section done

    2010-06-30 06:51:44.48 spid76      Backup:Copying data

    2010-06-30 06:53:18.32 spid76      The operating system returned error 1450(Insufficient system resources exist to complete the requested service.) to SQL Server during a write at offset 0x00000000003200 in file with handle 0x0000101C. 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.

    2010-06-30 06:54:52.08 spid76      The operating system returned error 1450(Insufficient system resources exist to complete the requested service.) to SQL Server during a write at offset 0x00000000003200 in file with handle 0x0000101C. 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.

    2010-06-30 06:56:25.85 spid76      The operating system returned error 1450(Insufficient system resources exist to complete the requested service.) to SQL Server during a write at offset 0x00000000003200 in file with handle 0x0000101C. 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.

    2010-06-30 06:57:59.63 spid76      The operating system returned error 1450(Insufficient system resources exist to complete the requested service.) to SQL Server during a write at offset 0x00000000003200 in file with handle 0x0000101C. 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.

    2010-06-30 06:59:33.38 spid76      The operating system returned error 1450(Insufficient system resources exist to complete the requested service.) to SQL Server during a write at offset 0x00000000003200 in file with handle 0x0000101C. 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.

    2010-06-30 07:01:07.16 spid76      The operating system returned error 1450(Insufficient system resources exist to complete the requested service.) to SQL Server during a write at offset 0x00000000003200 in file with handle 0x0000101C. 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.

    2010-06-30 07:02:40.91 spid76      The operating system returned error 1450(Insufficient system resources exist to complete the requested service.) to SQL Server during a write at offset 0x00000000003200 in file with handle 0x0000101C. 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.

    2010-06-30 07:04:14.69 spid76      The operating system returned error 1450(Insufficient system resources exist to complete the requested service.) to SQL Server during a write at offset 0x00000000003200 in file with handle 0x0000101C. 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.

    2010-06-30 07:05:48.45 spid76      The operating system returned error 1450(Insufficient system resources exist to complete the requested service.) to SQL Server during a write at offset 0x00000000003200 in file with handle 0x0000101C. 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.

    2010-06-30 07:07:22.21 spid76      The operating system returned error 1450(Insufficient system resources exist to complete the requested service.) to SQL Server during a write at offset 0x00000000003200 in file with handle 0x0000101C. 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.

    2010-06-30 07:08:55.99 spid76      The operating system returned error 1450(Insufficient system resources exist to complete the requested service.) to SQL Server during a write at offset 0x00000000003200 in file with handle 0x0000101C. 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.

    SQL Server 2005 x86 SP3 Cumulative update pack 4 - 4226

    Att,

    Thiago

    thiago.leite@ilegra.com

  • From above:

    "When a sparse file (used for snapshot database files) is populated Windows limits the amount of data that may reside in the file. Once the amount of data stored in the sparse file exceeds the limit further data storage in the file may be prevented."

    Is the maximum snapshot sparse file size limit fixed, or computed at creation?  If computed, do input parameters include source file size, source file data footprint, LUN free space, or other factors?

    Seeing some errors 665, think its just a matter of the checkdb running long and getting into a high change rate window.

    But in the future I'd like to monitor sparse file size, for some advance warning of when the limit is approaching.

    Thanks!

  • Good blog post on current size of dbcc checkdb internal snapshot sparse files.  But the limit is still unclear.

    sqlscope.wordpress.com/.../dbcc-checkdb-internal-snapshot-size

  • Hi,

    We have one production DB of 1.6 TB size & we configured mirroring with Async mode on it. On mirrored server we created snapshot of mirrored DB. But after 2-3 hours when Sparse file size increases to 30 GB, our snapshot DB gets corrupted & we have to create the snapshot again & same thing happens after 2-3 hours.

    My question is:- After googling the limitations of Sql Server Sparse File, somewhere i found that sparse file has limitation of 38 GB. But on MSDN it is said that "Sparse file can grow up to the file size when snapshot was taken".

    Would like to know the fix for that if available.

  • Is there any limitation on the size of the sparse file?

Page 1 of 1 (9 items)