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