Yesterday it also happened to me in a server that will be in production in a week, so I could do all the tests I want now. I wasn't at the office when it happened and the developers restarted the server to solve it.
It's a Server with 1 processor (4 cores) and 4Gb of RAM (without using /3G neither /AWE).
At that time, there had mainly been executed stored procedures. From '2009-05-12 18:03:51.293' to '2009-05-12 18:05:03.293' there was 650 stored procedures that made inserts/updates and deletes. No extended procedures were executed.
It's SQL Server 2008 Standard Edition RTM version and I think I'll install Service Pack 1. Because in things that have been solve, there's:
"SQL Server 2008 seems to make slow progress and may return error 701 if the buffer pool size is very small." and I'm not sure if it has something to do with it...
http://support.microsoft.com/?scid=kb%3Ben-us%3B968369&x=3&y=4
Here is a summary of what I've found in the error file:
2009-05-12 18:15:37.48 spid30235 Failed allocate pages: FAIL_PAGE_ALLOCATION 1
2009-05-12 18:15:37.48 spid30235
Memory Manager KB
---------------------------------------- ----------
VM Reserved 1681456
VM Committed 1641124
AWE Allocated 0
Reserved Memory 1024
Reserved Memory In Use 0
[....]
2009-05-12 18:15:37.60 spid30235
Memory Pool (internal) Pages
---------------------------------------- ----------
Allocations 194519
Predicted 236669
Private Target 0
Private Limit 0
Total Target 194529
Total Limit 194529
OOM Count 1
[...]
2009-05-12 18:15:37.60 spid30235
Buffer Pool Value
---------------------------------------- ----------
Committed 199936
Target 204768
Database 5280
Dirty 426
In IO 0
Latched 1
Free 137
Stolen 194519
Reserved 0
Visible 204768
Stolen Potential 10
Limiting Factor 11
Last OOM Factor 0
Page Life Expectancy 522708
2009-05-12 18:15:37.60 spid30235
Process/System Counts Value
---------------------------------------- ----------
Available Physical Memory 1617272832
Available Virtual Memory 227524608
Available Paging File 3527794688
Working Set 1762062336
Percent of Committed Memory in WS 100
Page Faults 492460
System physical memory high 1
System physical memory low 0
Process physical memory low 1
Process virtual memory low 0
2009-05-12 18:15:37.60 spid30235
Procedure Cache Value
---------------------------------------- ----------
TotalProcs 1
TotalPages 534
InUsePages 0
2009-05-12 18:15:37.60 spid30235
Global Memory Objects Pages
---------------------------------------- ----------
Resource 191
Locks 180
XDES 21523
SETLS 4
SE Dataset Allocators 8
SubpDesc Allocators 4
SE SchemaManager 215
SE Column Metadata Cache 313
SQLCache 497
Replication 2
ServerGlobal 26
XP Global 2
SortTables 68
[...]
2009-05-12 18:15:37.71 spid30238 Error: 701, gravedad: 17, estado: 130.
2009-05-12 18:15:37.71 spid30238 There is insufficient system memory in resource pool 'internal' to run this query.
[...]
2009-05-12 18:15:38.46 spid30241 There is insufficient system memory in resource pool 'internal' to run this query.
2009-05-12 18:15:38.46 Error: 18456, Severity: 14, State: 40. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2009-05-12 18:15:38.48 spid30241 Error: 701, gravedad: 17, estado: 89.
2009-05-12 18:15:38.48 spid30241 There is insufficient system memory in resource pool 'internal' to run this query.
[...]
2009-05-12 18:28:56.91 Servidor Error: 17186, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2009-05-12 18:28:56.91 Servidor Error: 17186, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2009-05-12 18:28:56.91 Servidor Error: 18052, Severity: -1, State: 0. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2009-05-12 18:28:56.91 Servidor Failed to enqueue kill_session task. There may be insufficient memory.
2009-05-12 18:28:56.91 Servidor Error: 17186, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2009-05-12 18:28:56.91 Servidor Error: 17186, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
And continues with the following line until the SQL Server service is restarted:
2009-05-12 18:28:56.91 Servidor Error: 17186, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.