You are currently offline, waiting for your internet to reconnect

How to troubleshoot SQL Server error 8645

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.

This article was previously published under Q309256
SUMMARY
This step-by-step article describes how to troubleshoot SQL Server error 8645. You may receive the error 8645 when there is a high server workload or when the servers are experiencing high memory pressure.

back to the top

Symptoms

You may see the following 8645 error sporadically:
Error 8645: A time out occurred while waiting for memory resources to execute the query. Re-run the query.

Additionally, you may also see one or more of the following symptoms:
  • When new users try to connect to SQL Server, they receive a "login failed" error message.
  • Users are disconnected and they receive various error messages.
  • CPU usage is very high on the server.
  • When you run
    select * from sysprocesses						
    you see that SPIDs have a waittype of 0x40 or 0x0040 and a last_waittype of RESOURCE_SEMAPHORE.
  • The System Monitor object SQLServer:MemoryManager displays a non-zero value for Memory Grants Pending.
  • SQL Profiler displays the event "Execution Warnings" that includes the "Wait For Memory" or the "Wait For Memory Timeout" text.
back to the top

Causes

The error message 8645 is raised when memory-intensive queries, such as those involving sorting and hashing, are queued and are unable to obtain the requested memory before a time-out period. SQL Server waits for memory for a length of time that is based on the query wait configuration value. By default, the query wait value is set to -1, which means SQL Server waits for 25 times the estimated cost of the query. If you modify the query wait value to a non-negative number, SQL Server waits for the number of seconds that you specify in the value of the query wait option.



You may also receive error 8645 under the following circumstances:
  • You have queries that are not properly optimized and therefore consume more resources than necessary.
  • The memory that you allocated to SQL Server is not big enough for the work load that you are running on SQL Server.
  • You do not process all results immediately and therefore memory is held longer than necessary.
back to the top

Troubleshooting Steps

You can use one or more of the following steps to help reduce the chances of receiving error 8645.
  1. Try to optimize performance for any long running queries. To identify long running queries, run a SQL Profiler trace that captures the following counters:

    SQL Profiler:

    • Errors and Warning: Exception
    • Misc: Execution Plan
    • TSQL: Batch Completed
    You can also run the Index Tuning Wizard to identity and to optimize any long running queries.For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
    311826 INF: Index Tuning Wizard Best Practices
  2. Keep distribution statistics up to date, particularly after large amounts of data modifications.For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
    195565 INF: How SQL Server 7.0 and SQL Server 2000 Autostats Work
  3. Look for any memory pressure on SQL Server by capturing a System Monitor trace. Capture the following counters to identify any memory pressure on SQL Server.

    System Monitor:

    • SQLServer:Lock Requests/sec
    • SQLServer:Memory Manager
    • Process:Private bytes
    • Process:Working Set bytes
    To relieve memory pressure, allocate more memory to SQL Server by increasing the max server memory configuration option. You can add more physical memory to your computer and you may also want to use the /3GB parameter.

    If you are running SQL Server Enterprise Edition, consider using the AWE option to increase the memory that SQL Server can use. In some cases, you may want to increase the number of processors on your computer, which can also increase the virtual memory page file size.For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
    197379 Configuring page files for optimization and recovery in Windows Server 2003, in Windows 2000, and in Windows NT
    197379 Configuring Page Files for Optimization and Recovery
  4. If you are running SQL Server 7.0, test disabling parallelism for SQL Server 7.0 by turning the max degree of parallelism configuration option off. This may affect the performance of some parallel queries, but this also increases query concurrency for SQL Server 7.0 and this helps reduce the possibilities of experiencing error 8645.
  5. Make sure that SQL Server clients process all result sets immediately. SQL Server cannot release resources that are dedicated to a query until it receives notification from the client that all result sets have been processed.For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
    180775 INF: Client Effects on SQL Server Throughput
  6. Check the SQL Server configuration settings for the proper values. Sub-optimal settings for values such as Max Server Memory or Query Wait can increase the chance of this error occurring.For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
    319942 HOW TO: Determine Proper SQL Server Configuration Settings
back to the top
REFERENCES
For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
243588 HOW TO: Troubleshoot the Performance of Ad-Hoc Queries
311826 INF: Index Tuning Wizard Best Practices
For additional information, see the "Troubleshooting 8645" SQL Server Books Online topic.

back to the top
100% cpu hang freeze error message 8645
Properties

Article ID: 309256 - Last Review: 12/22/2005 23:34:20 - Revision: 5.4

Microsoft SQL Server 2000 Standard Edition, Microsoft SQL Server 2000 64-bit Edition, Microsoft SQL Server 7.0 Standard Edition, Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Enterprise Edition, Microsoft SQL Server 2005 Express Edition, Microsoft SQL Server 2005 Workgroup Edition

  • kbhowtomaster KB309256
Feedback
/html>