Troubleshooting SQL Server error “The operating system returned error 1453”
I worked on an interesting SQL startup problem today. I don’t see this error very often, but since there wasn’t much on the web, I thought I would write it up and post it on my blog.
Errorlog
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
2010-12-27 03:03:31.41 spid2s Warning: unable to allocate 'min server memory' of 13312MB. 2010-12-27 03:11:54.71 spid52 The operating system returned error 1453(Insufficient quota to complete the requested service.) to SQL Server during a read at offset 0000000000000000 in file with handle 0x00000B28. This <span class="kwrd">is</span> usually a <span class="kwrd">temporary</span> condition <span class="kwrd">and</span> the <span class="kwrd">SQL</span> Server will keep retrying the <span class="kwrd">operation</span>. <span class="kwrd">If</span> the condition persists <span class="kwrd">then</span> <span class="kwrd">immediate</span> <span class="kwrd">action</span> must be taken <span class="kwrd">to</span> correct it. 2010-12-27 03:21:16.56 Server Error: 26040, Severity: 17, State: 1. 2010-12-27 03:21:16.56 Server Server TCP provider has stopped listening on port [ 1433 ] due to a failure. Error: 0x2747, state: 2. The server will automatically attempt to reestablish listening. 2010-12-27 03:23:14.49 Server Error: 26040, Severity: 17, State: 1. 2010-12-27 03:23:14.49 Server Server TCP provider has stopped listening on port [ 1433 ] due to a failure. Error: 0x2747, state: 2. The server will automatically attempt to reestablish listening. 2010-12-27 03:25:14.51 spid15s Server TCP provider has successfully reestablished listening on port [ 1433 ]. 2010-12-27 03:29:14.78 Server Error: 26040, Severity: 17, State: 1. 2010-12-27 03:29:14.78 Server Server TCP provider has stopped listening on port [ 1433 ] due to a failure. Error: 0x2747, state: 2. The server will automatically attempt to reestablish listening. 2010-12-27 03:35:24.51 spid15s Server TCP provider has successfully reestablished listening on port [ 1433 ]. 2010-12-27 03:35:49.21 spid2s SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [D:\databases\\templog.ldf] in database [tempdb] (2). The OS file handle is 0x0000065C. The offset of the latest long I/O is: 0x00000000120000 2010-12-27 03:37:04.26 spid52 The operating system returned error 1453(Insufficient quota to complete the requested service.) to SQL Server during a write at offset 0x00000000120000 <span class="kwrd">in</span> <span class="kwrd">file</span> <span class="kwrd">with</span> handle 0x0000065C. 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. |
Findings
SQL Server was configured for Max/Min RAM to 11gb on a 16gig system. Sounds reasonable as this leaves 5gb for OS, SQL code, etc. Task Manager even shows 16gb, however we had a very active 17gb page file. Taking an OS kernel dumps shows only 6gb physical ram. So why don’t we see all 16gb ram?
Discovered the VMWare “Memory” “Resource Allocation” setting was restricting physical memory to 6gb.
Resolution
We changed the VMWare memory allocation for this server to “unlimited” and restarted the server. Now all 16gb ram was available and SQL no longer reported memory allocation errors.
This is a great find. Thanks for sharing.
Thanks for this…my SQL Server was trying to use more memory than we have on the box, and very likely starving the OS….all just to backup a t-log 🙂