September 3, 2008 at 5:01 am
Hi,
Anybody pls help me, Iam getting insufficient memory error while starting sql agent service.
Iam having 4gb RAM & enough space on C drive
pls help
Event Type:Error
Event Source:MSSQLSERVER
Event Category:(2)
Event ID:701
Date:9/3/2008
Time:4:08:23 PM
User:N/A
Computer:TTSL-MOSSDBACK
Description:
There is insufficient system memory to run this query.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: bd 02 00 00 11 00 00 00 ½.......
0008: 0f 00 00 00 54 00 54 00 ....T.T.
0010: 53 00 4c 00 2d 00 4d 00 S.L.-.M.
0018: 4f 00 53 00 53 00 44 00 O.S.S.D.
0020: 42 00 41 00 43 00 4b 00 B.A.C.K.
0028: 00 00 00 00 00 00 ......
September 3, 2008 at 5:31 am
The error that you posted is from MSSQLSERVER (note the EVENT SOURCE: label). So, there definitely appears like their might be an issue with the memory utilization for the instance. If possible you might want to try stopping MSSQLSERVER then restarting and then try starting SQL SERVER AGENT. If there are other error messages specific to the AGENT service then please post them as well so that we can get a better idea of what is happening.
Thanks in advance.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
September 3, 2008 at 11:18 am
Hi David,
Thank You very much for your response.
Iam not getting any other error in eventvwr.
It is like a intermitent problem. SQL Agent itself is not starting sometimes, How i found this error means, Iam trying out databases mirroring, For few databases iam able to do mirroring,While doing mirroring for other databases iam getting error in transaction sql statement then i found error in mirrored server not on principal server.
Please help me why this type error will come.
Please let me know if any other details you required
Thank you once again.
September 3, 2008 at 12:35 pm
Anything worthwhile in the error log (sp_readderrorlog)?
September 5, 2008 at 3:44 am
Iam not able to find this proc, When can i find? Pls.
September 5, 2008 at 5:17 am
? It is a system stored procedure accessible from any database ... try xp_readerrorlog. You can also view the error logs through the management tree in SSMS or on the file system under LOGS.
September 6, 2008 at 12:51 pm
If you are not aware of the SP sp_readerrorlog or xp_readerrorlog..Please learn about them since there are option to read previous logs and also search for particular string combination inside the error logs...
Now best thing you can do is note down the time showd in the event viewer and go to Program Files\Microsoft SQL Server\MSSQL....\LOG\ and open both SQLServers latest log and SQL Server Agent's latest log in notepad and see for the detailed error...
Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc
September 7, 2008 at 4:01 am
Dear Sakthi ,
Thank You for the reply.Please find the logs
SQL Agent Log Error:
2008-09-05 01:55:00 - ! [382] Logon to server '(local)' failed (ConnUpdateStartExecutionDate)
2008-09-05 01:55:00 - ! [298] SQLServer Error: 233, Shared Memory Provider: No process is on the other end of the pipe. [SQLSTATE 08S01]
2008-09-05 01:55:00 - ! [298] SQLServer Error: 233, Communication link failure [SQLSTATE 08S01]
2008-09-05 01:55:00 - ! [382] Logon to server '(local)' failed (JobManager)
2008-09-05 01:55:05 - ! [298] SQLServer Error: 233, Shared Memory Provider: No process is on the other end of the pipe. [SQLSTATE 08S01]
2008-09-05 01:55:05 - ! [298] SQLServer Error: 233, Communication link failure [SQLSTATE 08S01]
2008-09-05 01:55:05 - ! [382] Logon to server '(local)' failed (JobManager)
2008-09-05 01:55:10 - ! [298] SQLServer Error: 233, Shared Memory Provider: No process is on the other end of the pipe. [SQLSTATE 08S01]
2008-09-05 01:55:10 - ! [298] SQLServer Error: 233, Communication link failure [SQLSTATE 08S01]
2008-09-05 01:55:10 - ! [382] Logon to server '(local)' failed (JobManager)
2008-09-05 02:22:01 - ! [298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'TTSL\kmsadmin', error code 0x3a. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
2008-09-05 02:23:01 - ! [298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'TTSL\kmsadmin', error code 0x3a. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
2008-09-05 02:24:08 - ! [298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'TTSL\kmsadmin', error code 0x3a. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
2008-09-05 02:25:00 - ! [298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'TTSL\kmsadmin', error code 0x3a. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
2008-09-05 02:26:00 - ! [298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'TTSL\kmsadmin', error code 0x3a. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
2008-09-05 02:27:00 - ! [298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'TTSL\kmsadmin', error code 0x3a. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
2008-09-05 02:28:00 - ! [298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'TTSL\kmsadmin', error code 0x3a. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
2008-09-05 02:29:00 - ! [298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'TTSL\kmsadmin', error code 0x3a. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
2008-09-05 02:30:00 - ! [298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'TTSL\kmsadmin', error code 0x3a. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
2008-09-05 02:30:00 - ! [298] SQLServer Error: 233, Shared Memory Provider: No process is on the other end of the pipe. [SQLSTATE 08S01]
2008-09-05 02:30:00 - ! [298] SQLServer Error: 233, Communication link failure [SQLSTATE 08S01]
SQL error Log:
2008-09-05 01:12:47.02 spid1s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 115230 seconds. Working set (KB): 117432, committed (KB): 300520, memory utilization: 39%.
2008-09-05 01:53:12.07 spid68 Error: 701, Severity: 17, State: 123.
2008-09-05 01:53:12.07 spid68 There is insufficient system memory to run this query.
2008-09-05 02:00:59.76 spid1s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 189360, committed (KB): 371304, memory utilization: 50%.
2008-09-05 02:06:28.84 spid1s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 329 seconds. Working set (KB): 146024, committed (KB): 328152, memory utilization: 44%.
2008-09-05 02:11:56.12 spid1s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 656 seconds. Working set (KB): 146532, committed (KB): 328872, memory utilization: 44%.
2008-09-05 02:16:19.04 spid1s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 919 seconds. Working set (KB): 145660, committed (KB): 327496, memory utilization: 44%.
2008-09-05 02:21:48.72 spid1s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 1248 seconds. Working set (KB): 146072, committed (KB): 328424, memory utilization: 44%.
2008-09-05 02:26:11.59 spid1s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 1511 seconds. Working set (KB): 118944, committed (KB): 300944, memory utilization: 39%.
2008-09-05 02:31:40.16 spid1s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 1840 seconds. Working set (KB): 110028, committed (KB): 291952, memory utilization: 37%.
2008-09-05 02:35:15.27 spid87 Error: 701, Severity: 17, State: 123.
2008-09-05 02:35:15.27 spid87 There is insufficient system memory to run this query
Please help me if you get anything from these logs.
September 7, 2008 at 7:17 am
Hi Ram,
Is it a 64-bit server? Seems like HardTrim is happening because OS finds that Low memory condition.. Can you try below things?
1) Setting a limit for Maximum Server Memory...
2) Try restarting the server and see whether the issue goes?
3) Is there any other application something like Sharepoint Server or some major applications running on the same server?
Note:
For security purpose remove the domain name while posting any log/report if needed. For example, people can come to know which company e.t.c from [TTSL] your domain name... Just to ensure privacy...
Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc
September 7, 2008 at 7:35 am
Hi Sakthi,
Thank You for speedy response
It is 32 bit, Server having 4GB RAm, I already kept virtual memory fro 2 GB to 8 GB on c & D Drive. Al primary troubleshooting i had done.
Is there anyware can i mention the sql server to use the fixed memory.
September 7, 2008 at 8:12 am
Ram, How about reply to other two questions? Also Maximum Memory Configuration is in SQL Server options and it is not Virtual Memory which you have given...
Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc
September 7, 2008 at 8:14 am
Are there other SQL instances, other SQL services (SSIS, SSAS, SSRS etc) running on this machine?
Are other applications, beside SQL server, running on the machine?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 7, 2008 at 11:18 am
Stopping and Restarting would be your best bet to mitigate this issue. Right click on my computer and go to Manage, check for the application logs maybe on of the applications running on it were taking high memory hence the errors..hope this helps.
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
October 8, 2008 at 8:50 am
Hi,
I had the same issue... I had same error in agent logs but in sql logs, it loots like differential backup was going on. The strange thing is that differential backup ran fine. all the jobs running ok but just these errors are appearing in agent logs.
Please advice.
Thx
October 9, 2008 at 2:59 am
A significant part of sql server process memory has been paged out.
This is the primary cause of your problems, and you need to stop SQL needing to produce this message. If you Google this message text, there is a lot of advise available on how to resolve this issue.
The Product Support Service (PSS) has a good post on what causes this problem and some resolutions http://blogs.msdn.com/psssql/archive/2007/05/31/the-sql-server-working-set-message.aspx
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply