SQL 2005 Memory Error
in [Server Configuration]
|
Prev: link from sql2005 (64-bit) to sql 7 (32-bit).
Next: The SQLBrowser service was unable to process a client request error
From: Ron on 28 Jun 2006 10:08 We just had the problem again. I had given the service accout lock pages in memory permission. 1. The server is 64 gig with the sql server using 61440000 bytes of that. (according to perfmon). The Sql server is configured to use between 40000mb and 60000mb as configured on the memory screen in 2005. 2. I have now increased the size of the virtual paging file to 20 gig. It was about 2 gig before. The server had never done any great amount of paging in the past. 3. This server is a sql server only. no other non os apps running except Norton anti-virus which we have to have on all servers. 4. I checked perfmon before rebooting the server and everything looked "normal". The odd part was that the server was "busy" enough that perfmon had problems getting the stats. That normally does not happen The sql server just stopped being able to allocate memory again. I could connect to the OS fine, the sql server was accepting connections but they would fail during the handshake. here is the log arround the time the errors started: 2006-06-28 08:45:14.83 spid24s MEMORYBROKER_FOR_STEAL Allocations = 3026 Rate = 0 Target Allocations = 5765925 Future Allocations = 0 Last Notification = GROW 2006-06-28 08:45:14.83 spid24s MEMORYBROKER_FOR_RESERVE Allocations = 2320 Rate = 0 Target Allocations = 6156000 Future Allocations = 1439612 Last Notification = GROW 2006-06-28 08:45:15.07 spid85 Error: 701, Severity: 17, State: 135. 2006-06-28 08:45:15.07 spid85 There is insufficient system memory to run this query. 2006-06-28 08:45:15.92 Logon Error: 17803, Severity: 20, State: 13. 2006-06-28 08:45:15.92 Logon There was a memory allocation failure during connection establishment. Reduce nonessential memory load, or increase system memory. The connection has been closed. [CLIENT: <local machine>] 2006-06-28 08:45:15.92 Logon Error: 17803, Severity: 20, State: 13. 2006-06-28 08:45:15.92 Logon There was a memory allocation failure during connection establishment. Reduce nonessential memory load, or increase system memory. The connection has been closed. [CLIENT: <local machine>] 2006-06-28 08:45:18.94 Logon Error: 17803, Severity: 20, State: 13. 2006-06-28 08:45:18.94 Logon There was a memory allocation failure during connection establishment. Reduce nonessential memory load, or increase system memory. The connection has been closed. [CLIENT: <local machine>] v-xuwen(a)online.microsoft.com (Vincent Xu [MSFT]) wrote in news:G8#1wRMmGHA.4928(a)TK2MSFTNGXA01.phx.gbl: > Hi, > > You can try to increase server memory: > > 1. Check the settings for both min server memory (MB) and max server > memory (MB). > If max server memory (MB) is a value close to the value of min server > memory (MB), then increase the max server memory (MB) value. > > 2. Check the size of the virtual memory paging file. > If possible, increase the size of the file. > > 3. Shut down any other applications running, if applicable, on the > server. > > 4. View the current memory usage information in Windows NT Performance > Monitor. > To view current memory usage information in preparation for tuning > memory configuration, use the Total Server Memory (KB) Performance > Monitor Counter of the SQLServer:General Statistics object. > ><http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsq ><l/tr > _reslsyserr_1_26r5.asp> > > Hope the information helps. > > Best regards, > > Vincent Xu > Microsoft Online Partner Support > >====================================================== > Get Secure! - www.microsoft.com/security >====================================================== > When responding to posts, please "Reply to Group" via your newsreader > so that others > may learn and benefit from this issue. >====================================================== > This posting is provided "AS IS" with no warranties,and confers no > rights. >====================================================== > > > > -------------------- >>>Subject: SQL 2005 Memory Error >>>From: Ron <ron(a)myalias.postalias> >>>Organization: Doral Dental >>>Message-ID: <Xns97EB5F55C49CDWBT39212565(a)207.46.248.16> >>>User-Agent: Xnews/??.01.30 >>>Newsgroups: microsoft.public.sqlserver.server >>>Date: Fri, 23 Jun 2006 07:22:18 -0700 >>>NNTP-Posting-Host: 66-195-159-90.static.twtelecom.net 66.195.159.90 >>>Lines: 1 >>>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP05.phx.gbl >>>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:436046 >>>X-Tomcat-NG: microsoft.public.sqlserver.server >>> >>>I have a 64 bit sql 2005 server run on a 64 gig windows 2003 64bit >>>server. The sever has been running fine for the last few months. In >>>the last couple of days a problem has come up though. >>> >>>Twice in the last few days the SQL server started reporting not >>>enough memory to run a query and stopped allowing connections. Thw >>>OS was fine during this. >>> >>>The event log on the OS does not have any relavent messages. >>> >>>I do not see a pattern to the problems because one happened early in >>>the business day and one later in the business day. >>> >>>A reboot of the server resolves the problem. (or at least appears to >>>resolve it) >>> >>>I doubt a user could write a query that would run the server out of >>>memory on a 64 gig box. >>> >>>What could be causing this and how do I correct the issue? >>> >>>Thanks, >>>Ron >>> >>> >>>Sql server version info: >>>Microsoft SQL Server 2005 - 9.00.2153.00 (X64) May 9 2006 13:58:37 >>> Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition >>>(64- bit) on Windows NT 5.2 (Build 3790: Service Pack 1) >>> >>> >>>Here is the last few entries for the sql log just before the first >>>reboot. >>>MEMORYBROKER_FOR_CACHE >>> Allocations = 71043 >>> Rate = 0 >>> Target Allocations = 5851491 >>> Future Allocations = 0 >>> Last Notification = GROW >>> >>>2006-06-21 08:11:43.39 spid25s >>>MEMORYBROKER_FOR_STEAL >>> Allocations = 4897 >>> Rate = 0 >>> Target Allocations = 5785345 >>> Future Allocations = 0 >>> Last Notification = GROW >>> >>>2006-06-21 08:11:43.39 spid25s >>>MEMORYBROKER_FOR_RESERVE >>> Allocations = 0 >>> Rate = 0 >>> Target Allocations = 6156000 >>> Future Allocations = 1439612 >>> Last Notification = GROW >>> >>>2006-06-21 08:11:48.22 spid92 Error: 701, Severity: 17, State: >>>135. 2006-06-21 08:11:48.22 spid92 There is insufficient system >>>memory to run this query. >>>2006-06-21 08:12:02.97 Server Error: 17300, Severity: 16, State: >>>1. (Params:). The error is printed in terse mode because there was >>>error during formatting. Tracing, ETW, notifications etc are skipped. >>>2006-06-21 08:12:13.71 Server SQL Server is terminating because >>>of a system shutdown. This is an informational message only. No user >>>action is required. >>>2006-06-21 08:12:14.02 spid92 Error: 701, Severity: 17, State: >>>135. 2006-06-21 08:12:14.02 spid92 There is insufficient system >>>memory to run this query. >>>2006-06-21 08:12:14.02 spid92 Error: 17054, Severity: 16, State: >>>1. 2006-06-21 08:12:14.02 spid92 The current event was not >>>reported to the Windows Events log. Operating system error = 1717(The >>>interface is unknown.). You may need to clear the Windows Events log >>>if it is full. 2006-06-21 08:12:14.02 spid93 Error: 701, >>>Severity: 17, State: 130. 2006-06-21 08:12:14.02 spid93 There is >>>insufficient system memory to run this query. >>>2006-06-21 08:12:14.04 spid81 Error: 701, Severity: 17, State: >>>135. 2006-06-21 08:12:14.04 spid81 There is insufficient system >>>memory to run this query. >>>2006-06-21 08:12:14.04 spid82 Error: 701, Severity: 17, State: >>>130. 2006-06-21 08:12:14.04 spid82 There is insufficient system >>>memory to run this query. >>>2006-06-21 08:12:14.04 spid93 Error: 701, Severity: 17, State: >>>130. 2006-06-21 08:12:14.04 spid93 There is insufficient system >>>memory to run this query. >>>2006-06-21 08:12:14.04 spid82 Error: 701, Severity: 17, State: >>>130. 2006-06-21 08:12:14.04 spid82 There is insufficient system >>>memory to run this query. >>>2006-06-21 08:12:14.04 spid87 Error: 18056, Severity: 20, State: >>>23. (Params:). The error is printed in terse mode because there was >>>error during formatting. Tracing, ETW, notifications etc are skipped. >>>2006-06-21 08:12:14.07 spid109 Error: 701, Severity: 17, State: >>>135. 2006-06-21 08:12:14.07 spid109 There is insufficient system >>>memory to run this query. >>> >>> >>> >>>Here are the entries just before the second reboot >>>MEMORYBROKER_FOR_CACHE >>> Allocations = 116869 >>> Rate = 0 >>> Target Allocations = 5852776 >>> Future Allocations = 0 >>> Last Notification = GROW >>> >>>2006-06-22 15:33:09.55 spid103 >>>MEMORYBROKER_FOR_STEAL >>> Allocations = 3612 >>> Rate = 0 >>> Target Allocations = 5739519 >>> Future Allocations = 0 >>> Last Notification = GROW >>> >>>2006-06-22 15:33:09.55 spid103 >>>MEMORYBROKER_FOR_RESERVE >>> Allocations = 2064 >>> Rate = 0 >>> Target Allocations = 6156000 >>> Future Allocations = 1439612 >>> Last Notification = GROW >>> >>>2006-06-22 15:33:09.55 spid103 Error: 701, Severity: 17, State: >>>135. 2006-06-22 15:33:09.55 spid103 There is insufficient system >>>memory to run this query. >>>2006-06-22 15:34:35.04 Server SQL Server is terminating because >>>of a system shutdown. This is an informational message only. No user >>>action is required. >>>2006-06-22 15:34:36.90 Server The connection has been lost with >>>Microsoft Distributed Transaction Coordinator (MS DTC). Recovery of >>>any in-doubt distributed transactions involving Microsoft Distributed >>>Transaction Coordinator (MS DTC) will begin once the connection is >>>re- established. This is an informational message only. No user >>>action is required. >>>2006-06-22 15:34:36.90 Server Error: 17054, Severity: 16, State: >>>1. >>> >
From: Vincent Xu [MSFT] on 29 Jun 2006 00:36
Hi, I'd like to suggest you use the DBCC MEMORYSTATUS command to monitor memory usage on SQL Server 2005 Check following article: 907877 How to use the DBCC MEMORYSTATUS command to monitor memory usage on SQL Server 2005 http://support.microsoft.com/default.aspx?scid=kb;EN-US;907877 From the log entries, the memory allocation failure may occur at a single operation unit which create a small memory block for use, however, it can not quite address the roor cause of the memory consumption issue. Also, SQL Server 2005 involve managed CLR engine which make memory consumption divided into managed and unmanaged parts. Here is one technet article discussing on general SQL 2005 performance issue throubleshooting: Troubleshooting Performance Problems in SQL Server 2005 http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx Best regards, Vincent Xu Microsoft Online Partner Support ====================================================== Get Secure! - www.microsoft.com/security ====================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from this issue. ====================================================== This posting is provided "AS IS" with no warranties,and confers no rights. ====================================================== -------------------- >>Subject: RE: SQL 2005 Memory Error >>From: Ron <ron(a)myalias.postalias> >>References: <Xns97EB5F55C49CDWBT39212565(a)207.46.248.16> <G8#1wRMmGHA.4928(a)TK2MSFTNGXA01.phx.gbl> >>Organization: Wonderbox Tech >>Message-ID: <Xns97F05D1145887WBT39212565(a)207.46.248.16> >>User-Agent: Xnews/??.01.30 >>Newsgroups: microsoft.public.sqlserver.server >>Date: Wed, 28 Jun 2006 07:08:55 -0700 >>NNTP-Posting-Host: 67-39-59-3.ded.ameritech.net 67.39.59.3 >>Lines: 1 >>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP04.phx.gbl >>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:436593 >>X-Tomcat-NG: microsoft.public.sqlserver.server >> >>We just had the problem again. I had given the service accout lock pages >>in memory permission. >> >>1. The server is 64 gig with the sql server using 61440000 bytes of >>that. (according to perfmon). The Sql server is configured to use >>between 40000mb and 60000mb as configured on the memory screen in 2005. >> >>2. I have now increased the size of the virtual paging file to 20 gig. >>It was about 2 gig before. The server had never done any great amount of >>paging in the past. >> >>3. This server is a sql server only. no other non os apps running >>except Norton anti-virus which we have to have on all servers. >> >>4. I checked perfmon before rebooting the server and everything looked >>"normal". The odd part was that the server was "busy" enough that >>perfmon had problems getting the stats. That normally does not happen >> >>The sql server just stopped being able to allocate memory again. I could >>connect to the OS fine, the sql server was accepting connections but >>they would fail during the handshake. >> >>here is the log arround the time the errors started: >> >>2006-06-28 08:45:14.83 spid24s >>MEMORYBROKER_FOR_STEAL >> Allocations = 3026 >> Rate = 0 >> Target Allocations = 5765925 >> Future Allocations = 0 >> Last Notification = GROW >> >>2006-06-28 08:45:14.83 spid24s >>MEMORYBROKER_FOR_RESERVE >> Allocations = 2320 >> Rate = 0 >> Target Allocations = 6156000 >> Future Allocations = 1439612 >> Last Notification = GROW >> >>2006-06-28 08:45:15.07 spid85 Error: 701, Severity: 17, State: 135. >>2006-06-28 08:45:15.07 spid85 There is insufficient system memory to >>run this query. >>2006-06-28 08:45:15.92 Logon Error: 17803, Severity: 20, State: 13. >>2006-06-28 08:45:15.92 Logon There was a memory allocation failure >>during connection establishment. Reduce nonessential memory load, or >>increase system memory. The connection has been closed. [CLIENT: <local >>machine>] >>2006-06-28 08:45:15.92 Logon Error: 17803, Severity: 20, State: 13. >>2006-06-28 08:45:15.92 Logon There was a memory allocation failure >>during connection establishment. Reduce nonessential memory load, or >>increase system memory. The connection has been closed. [CLIENT: <local >>machine>] >>2006-06-28 08:45:18.94 Logon Error: 17803, Severity: 20, State: 13. >>2006-06-28 08:45:18.94 Logon There was a memory allocation failure >>during connection establishment. Reduce nonessential memory load, or >>increase system memory. The connection has been closed. [CLIENT: <local >>machine>] >> >> >> >> >>v-xuwen(a)online.microsoft.com (Vincent Xu [MSFT]) wrote in >>news:G8#1wRMmGHA.4928(a)TK2MSFTNGXA01.phx.gbl: >> >>> Hi, >>> >>> You can try to increase server memory: >>> >>> 1. Check the settings for both min server memory (MB) and max server >>> memory (MB). >>> If max server memory (MB) is a value close to the value of min server >>> memory (MB), then increase the max server memory (MB) value. >>> >>> 2. Check the size of the virtual memory paging file. >>> If possible, increase the size of the file. >>> >>> 3. Shut down any other applications running, if applicable, on the >>> server. >>> >>> 4. View the current memory usage information in Windows NT Performance >>> Monitor. >>> To view current memory usage information in preparation for tuning >>> memory configuration, use the Total Server Memory (KB) Performance >>> Monitor Counter of the SQLServer:General Statistics object. >>> >>><http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsq >>><l/tr >>> _reslsyserr_1_26r5.asp> >>> >>> Hope the information helps. >>> >>> Best regards, >>> >>> Vincent Xu >>> Microsoft Online Partner Support >>> >>>====================================================== >>> Get Secure! - www.microsoft.com/security >>>====================================================== >>> When responding to posts, please "Reply to Group" via your newsreader >>> so that others >>> may learn and benefit from this issue. >>>====================================================== >>> This posting is provided "AS IS" with no warranties,and confers no >>> rights. >>>====================================================== >>> >>> >>> >>> -------------------- >>>>>Subject: SQL 2005 Memory Error >>>>>From: Ron <ron(a)myalias.postalias> >>>>>Organization: Doral Dental >>>>>Message-ID: <Xns97EB5F55C49CDWBT39212565(a)207.46.248.16> >>>>>User-Agent: Xnews/??.01.30 >>>>>Newsgroups: microsoft.public.sqlserver.server >>>>>Date: Fri, 23 Jun 2006 07:22:18 -0700 >>>>>NNTP-Posting-Host: 66-195-159-90.static.twtelecom.net 66.195.159.90 >>>>>Lines: 1 >>>>>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP05.phx.gbl >>>>>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:436046 >>>>>X-Tomcat-NG: microsoft.public.sqlserver.server >>>>> >>>>>I have a 64 bit sql 2005 server run on a 64 gig windows 2003 64bit >>>>>server. The sever has been running fine for the last few months. In >>>>>the last couple of days a problem has come up though. >>>>> >>>>>Twice in the last few days the SQL server started reporting not >>>>>enough memory to run a query and stopped allowing connections. Thw >>>>>OS was fine during this. >>>>> >>>>>The event log on the OS does not have any relavent messages. >>>>> >>>>>I do not see a pattern to the problems because one happened early in >>>>>the business day and one later in the business day. >>>>> >>>>>A reboot of the server resolves the problem. (or at least appears to >>>>>resolve it) >>>>> >>>>>I doubt a user could write a query that would run the server out of >>>>>memory on a 64 gig box. >>>>> >>>>>What could be causing this and how do I correct the issue? >>>>> >>>>>Thanks, >>>>>Ron >>>>> >>>>> >>>>>Sql server version info: >>>>>Microsoft SQL Server 2005 - 9.00.2153.00 (X64) May 9 2006 13:58:37 >>>>> Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition >>>>>(64- bit) on Windows NT 5.2 (Build 3790: Service Pack 1) >>>>> >>>>> >>>>>Here is the last few entries for the sql log just before the first >>>>>reboot. >>>>>MEMORYBROKER_FOR_CACHE >>>>> Allocations = 71043 >>>>> Rate = 0 >>>>> Target Allocations = 5851491 >>>>> Future Allocations = 0 >>>>> Last Notification = GROW >>>>> >>>>>2006-06-21 08:11:43.39 spid25s >>>>>MEMORYBROKER_FOR_STEAL >>>>> Allocations = 4897 >>>>> Rate = 0 >>>>> Target Allocations = 5785345 >>>>> Future Allocations = 0 >>>>> Last Notification = GROW >>>>> >>>>>2006-06-21 08:11:43.39 spid25s >>>>>MEMORYBROKER_FOR_RESERVE >>>>> Allocations = 0 >>>>> Rate = 0 >>>>> Target Allocations = 6156000 >>>>> Future Allocations = 1439612 >>>>> Last Notification = GROW >>>>> >>>>>2006-06-21 08:11:48.22 spid92 Error: 701, Severity: 17, State: >>>>>135. 2006-06-21 08:11:48.22 spid92 There is insufficient system >>>>>memory to run this query. >>>>>2006-06-21 08:12:02.97 Server Error: 17300, Severity: 16, State: >>>>>1. (Params:). The error is printed in terse mode because there was >>>>>error during formatting. Tracing, ETW, notifications etc are skipped. >>>>>2006-06-21 08:12:13.71 Server SQL Server is terminating because >>>>>of a system shutdown. This is an informational message only. No user >>>>>action is required. >>>>>2006-06-21 08:12:14.02 spid92 Error: 701, Severity: 17, State: >>>>>135. 2006-06-21 08:12:14.02 spid92 There is insufficient system >>>>>memory to run this query. >>>>>2006-06-21 08:12:14.02 spid92 Error: 17054, Severity: 16, State: >>>>>1. 2006-06-21 08:12:14.02 spid92 The current event was not >>>>>reported to the Windows Events log. Operating system error = 1717(The >>>>>interface is unknown.). You may need to clear the Windows Events log >>>>>if it is full. 2006-06-21 08:12:14.02 spid93 Error: 701, >>>>>Severity: 17, State: 130. 2006-06-21 08:12:14.02 spid93 There is >>>>>insufficient system memory to run this query. >>>>>2006-06-21 08:12:14.04 spid81 Error: 701, Severity: 17, State: >>>>>135. 2006-06-21 08:12:14.04 spid81 There is insufficient system >>>>>memory to run this query. >>>>>2006-06-21 08:12:14.04 spid82 Error: 701, Severity: 17, State: >>>>>130. 2006-06-21 08:12:14.04 spid82 There is insufficient system >>>>>memory to run this query. >>>>>2006-06-21 08:12:14.04 spid93 Error: 701, Severity: 17, State: >>>>>130. 2006-06-21 08:12:14.04 spid93 There is insufficient system >>>>>memory to run this query. >>>>>2006-06-21 08:12:14.04 spid82 Error: 701, Severity: 17, State: >>>>>130. 2006-06-21 08:12:14.04 spid82 There is insufficient system >>>>>memory to run this query. >>>>>2006-06-21 08:12:14.04 spid87 Error: 18056, Severity: 20, State: >>>>>23. (Params:). The error is printed in terse mode because there was >>>>>error during formatting. Tracing, ETW, notifications etc are skipped. >>>>>2006-06-21 08:12:14.07 spid109 Error: 701, Severity: 17, State: >>>>>135. 2006-06-21 08:12:14.07 spid109 There is insufficient system >>>>>memory to run this query. >>>>> >>>>> >>>>> >>>>>Here are the entries just before the second reboot >>>>>MEMORYBROKER_FOR_CACHE >>>>> Allocations = 116869 >>>>> Rate = 0 >>>>> Target Allocations = 5852776 >>>>> Future Allocations = 0 >>>>> Last Notification = GROW >>>>> >>>>>2006-06-22 15:33:09.55 spid103 >>>>>MEMORYBROKER_FOR_STEAL >>>>> Allocations = 3612 >>>>> Rate = 0 >>>>> Target Allocations = 5739519 >>>>> Future Allocations = 0 >>>>> Last Notification = GROW >>>>> >>>>>2006-06-22 15:33:09.55 spid103 >>>>>MEMORYBROKER_FOR_RESERVE >>>>> Allocations = 2064 >>>>> Rate = 0 >>>>> Target Allocations = 6156000 >>>>> Future Allocations = 1439612 >>>>> Last Notification = GROW >>>>> >>>>>2006-06-22 15:33:09.55 spid103 Error: 701, Severity: 17, State: >>>>>135. 2006-06-22 15:33:09.55 spid103 There is insufficient system >>>>>memory to run this query. >>>>>2006-06-22 15:34:35.04 Server SQL Server is terminating because >>>>>of a system shutdown. This is an informational message only. No user >>>>>action is required. >>>>>2006-06-22 15:34:36.90 Server The connection has been lost with >>>>>Microsoft Distributed Transaction Coordinator (MS DTC). Recovery of >>>>>any in-doubt distributed transactions involving Microsoft Distributed >>>>>Transaction Coordinator (MS DTC) will begin once the connection is >>>>>re- established. This is an informational message only. No user >>>>>action is required. >>>>>2006-06-22 15:34:36.90 Server Error: 17054, Severity: 16, State: >>>>>1. >>>>> >>> >> >> |