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 23 Jun 2006 10:22 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: Michael Epprecht [MSFT] on 23 Jun 2006 10:26 Hi Does the SQL Server Service Account have the "lock pages in memory" permission set on it? http://msdn2.microsoft.com/en-us/ms190730.aspx Regards -- Mike This posting is provided "AS IS" with no warranties, and confers no rights. "Ron" <ron(a)myalias.postalias> wrote in message news:Xns97EB5F55C49CDWBT39212565(a)207.46.248.16... >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 25 Jun 2006 22:01 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/trblsql/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: Ron on 25 Jun 2006 22:02 I have enabled it now. It was not enabled. I was under the impression in the 64-bit world this was not needed. Thanks. "Michael Epprecht [MSFT]" <michael.epprecht(a)online.microsoft.com> wrote in news:OXgxHEtlGHA.3588(a)TK2MSFTNGP02.phx.gbl: > Hi > > Does the SQL Server Service Account have the "lock pages in memory" > permission set on it? > > http://msdn2.microsoft.com/en-us/ms190730.aspx > > Regards
From: Vincent Xu [MSFT] on 27 Jun 2006 02:08
Hi Ron, Feel free to post back if you still have concerns in this issue. :) Have a good day! 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> <OXgxHEtlGHA.3588(a)TK2MSFTNGP02.phx.gbl> >>Organization: Wonderbox Tech >>Message-ID: <Xns97EDD616DE567WBT39212565(a)207.46.248.16> >>User-Agent: Xnews/??.01.30 >>Newsgroups: microsoft.public.sqlserver.server >>Date: Sun, 25 Jun 2006 19:02:45 -0700 >>NNTP-Posting-Host: 66-195-159-90.static.twtelecom.net 66.195.159.90 >>Lines: 1 >>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP03.phx.gbl >>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:436200 >>X-Tomcat-NG: microsoft.public.sqlserver.server >> >>I have enabled it now. It was not enabled. I was under the impression in >>the 64-bit world this was not needed. >> >>Thanks. >> >> >>"Michael Epprecht [MSFT]" <michael.epprecht(a)online.microsoft.com> wrote in >>news:OXgxHEtlGHA.3588(a)TK2MSFTNGP02.phx.gbl: >> >>> Hi >>> >>> Does the SQL Server Service Account have the "lock pages in memory" >>> permission set on it? >>> >>> http://msdn2.microsoft.com/en-us/ms190730.aspx >>> >>> Regards >> >> |