January 6, 2009 at 12:40 am
Hello All,
This is my first post. Hope you can help me fix the problem. MSSQL service is going down often(every 8/10 hours or so) and we had to reboot the server to bring back online. Here's the error log-
_____________
Event Type:Error
Event Source:Srv
Event Category:None
Event ID:2019
Date:1/5/2009
Time:6:11:39 PM
User:N/A
Computer:XXXX-Servername
Description:
The server was unable to allocate from the system nonpaged pool because the pool was empty.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 00 00 04 00 01 00 54 00 ......T.
0008: 00 00 00 00 e3 07 00 c0 ....ã..À
0010: 00 00 00 00 9a 00 00 c0 ......À
0018: 00 00 00 00 00 00 00 00 ........
0020: 00 00 00 00 00 00 00 00 ........
0028: 02 00 00 00 ....
_____________
Earlier we had Symantec AV installed in this server and I'm sure this has been completely removed from the server and registry. But still this problem is happening. This server has enough RAM and other resources.
Here's the details about the server-
OS - Microsoft Windows Server 2003 Standard Edition
Processor - Intel Pentium 4 CPU 3.06 GHz
RAM - 2 GB
SQL Version - Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: )
Process info taken during service down time-
sqlservr.exe-
Memory Usage - 929,192 K
NP Pool - 241 K
Handles - 3099
FYI- A month back everything was fine. We even added extra RAM to this server but no luck. Any help is appreciated. Please let me know if you need any other details.
Thanks,
Arun
January 7, 2009 at 2:44 am
I had this on a SQL 2000 box some years back. The ultimate fix for my problem was to add a -g startup parameter to leave more memory unallocated when SQL started.
We had a number of DTS routines that run on a scheduled basis, and sometimes when run times unavoidably overlapped this caused a shortage of memory.
Be aware that another common cause of this problem is a memory leak. Use Google if you are not sure what a memory leak is or how to troubleshoot the problem.
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
January 7, 2009 at 10:04 pm
EdVassie (1/7/2009)
I had this on a SQL 2000 box some years back. The ultimate fix for my problem was to add a -g startup parameter to leave more memory unallocated when SQL started.We had a number of DTS routines that run on a scheduled basis, and sometimes when run times unavoidably overlapped this caused a shortage of memory.
Be aware that another common cause of this problem is a memory leak. Use Google if you are not sure what a memory leak is or how to troubleshoot the problem.
Hi EdVassie,
Would you mind letting me know the exact steps to accomplish this task since I'm not sure? Will this cause any other problems in near future? I'm asking because this is a live server with more than 700 databases so I don't want to mess up something. Please provide me any other info I'd be thankful.
Thanks,
Arun
January 8, 2009 at 2:41 am
The default for -g in SQL Server 2000 is 256 MB (See Using Startup Options in BOL). What I did was increase this by 64 MB until the system became stable. I restarted SQL with the new -g value and watched it for a few days. It was still unstable so I did this again. I think for me I only needed another 128 MB before it all worked, but other workloads will be different.
You also say you have only 2 GB on the server. Maybe part of your problem is that workloads have increased and SQL needs more memory. Certainly if you increase the -g parameters you will be restricting what memory SQL Server can use.
Consider increasing server memory to 4GB. If your SQL edition and OS edition supports it, you can go above 4GB if you can take advantage of AWE and PAE (See posts ad infinitum about these options).
Ultimately you need to plan to move to SQL Server 2008 on a 64-bit box - this is now the only sensible upgrade option from SQL Server 2000 or SQL Server 2005.
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
January 8, 2009 at 12:57 pm
Ed, why use the -g switch as opposed to simply setting a lower fixed amount for SQL within EM? Are there advantages to using the switch?
Arun, how can you function with 700 databases on a server with only 2Gb of RAM? Even if they were tiny, with any kind of user load and leaving enough for the OS, I don't see how it's possible - even for 8-10 hours. Hopefully this server is solely dedicated to SQL. I agree with Ed, it's probably way past time to add more memory.
-- You can't be late until you show up.
January 9, 2009 at 2:28 am
tosscrosby (1/8/2009)
why use the -g switch as opposed to simply setting a lower fixed amount for SQL within EM?
Having put my brain into gear, reducing SQL max memory is the best approach on a server with only 2GB. If SQL was using AWE, then the only way would be to use the -g switch.
The OP seems between a rock and a hard place. The server memory looks way too small to cope with the workload, but the only way to avoid the immediate problem is to reduce the memory taken by SQL Server.
Even my desktop at home has 12GB memory (which helps Vista run well), so 2GB for a production server is very low spec.
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
January 9, 2009 at 1:48 pm
EdVassie (1/7/2009)
I had this on a SQL 2000 box some years back. The ultimate fix for my problem was to add a -g startup parameter to leave more memory unallocated when SQL started.We had a number of DTS routines that run on a scheduled basis, and sometimes when run times unavoidably overlapped this caused a shortage of memory.
Be aware that another common cause of this problem is a memory leak. Use Google if you are not sure what a memory leak is or how to troubleshoot the problem.
I agree with Ed... this sounds a lot like memory leaks.
Just a couple of things that are mostly just opinion on my part... I've never seen DTS take down a server unless someone wrote some "code" like ActiveX and it had a memory leak in it. I've been running Norton/Symantec AV on my box for 4 years. It's never been a problem.
And, some memory leaks are sneaky... anyone write an extended stored procedure or some wonderful OLE automation routines with sp_OA and forgot to drop objects? Of course, no one writes apps that have memory leaks, do they? 😛 Do you have anything else running on the SQL Server other than Windows Server and SQL Server?
--Jeff Moden
Change is inevitable... Change for the better is not.
January 9, 2009 at 2:01 pm
Jeff Moden (1/9/2009)
or some wonderful OLE automation routines with sp_OA and forgot to drop objects?
Did they ever fix the memory leak with sp_OA? It seems even if you were extremely careful coding to drop your objects with error handling it would still leak...
January 9, 2009 at 4:50 pm
That's a very good question and you're absolutely correct... sp_OA had some pretty nasty memory leaks. I don't actually use sp_OA for that and other reasons, so I've not kept track of that particular problem.
Does anybody out there know if they fixed memory leaks in sp_OA? In SQL Server 2000?
--Jeff Moden
Change is inevitable... Change for the better is not.
January 9, 2009 at 8:05 pm
Hi all,
Thanks for the updates regarding this. This server is shared SQL server and no other services installed(even Symantec AV has been uninstalled and no Firewalls). Recently we got upgraded to 2 GB RAM after this problem. Previously we had only 512 MB of RAM and the only problem we faced was slow response when I work inside this server but never faced service down alert like now nor we did reboot for over 3 weeks, everything was fine. We stopped customers creating databases in this server until this problem is resolved completely. I'm not DBA. Is there anyway to find sp_OA and disable it? Please walk me through the steps and help me zero down the problem. It's been very critical now 🙁 :(. Any help would be greatly appreciated.. please help:)
Thanks,
Arun
January 9, 2009 at 9:53 pm
Search the sysComments table(s) for any occurance of sp_OA. But, now that you've told us more, I don't believe that's the problem... 2GB of RAM is woefully inadequate for most servers that have customers hitting on them
--Jeff Moden
Change is inevitable... Change for the better is not.
January 10, 2009 at 7:50 am
Hi Arun,
For the SP_OA procedure you first need to see if that even applies to you. Chances are you don't even have any in your code, at least let's hope for that!
Find a script to search in all your code, something like:
http://www.sqlservercentral.com/scripts/Miscellaneous/30184/
I have not tried this particular script but it has a good rating, run a few tests on a dev box to make sure before trying it on your prod machine.
There are a few others on this site and on the web. Use 'sp_oa%' as your search pattern since we are looking for sp_OACreate, etc.
If you find any code using sp_OA we'll work from there...
I don't know your level of comfort with Windows and SQL Server in general so please don't take this the wrong way but if I was in your position I think I would try to find an experienced sysadmin to help me troubleshoot this memory leak.
A quick search on google using "how to troubleshoot memory leak" returns many link but I don't think I can walk you through this. I have seen servers with memory leaks but there always was someone more knowledgeable in the hot seat so my experience is limited.
If it's critical to your business it should not be too hard to convince your boss to get some external help and in my experience this could prove invaluable.
Hope this helps...
I really hope
January 10, 2009 at 8:48 am
Maxim Picard (1/10/2009)
There are a few others on this site and on the web. Use 'sp_oa%' as your search pattern since we are looking for sp_OACreate, etc.
Oh, be careful... it should be '[font="Arial Black"]%[/font]sp_oa%' if you're looking in sysComments for usage.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 10, 2009 at 6:59 pm
Hi All,
Thanks for the updates so far. BTW, if I upgrade to SP4 will this problem get resolved? Current version is SQL 2000 SP3. Please advise.
Thanks,
Arun
January 10, 2009 at 7:50 pm
Arun (1/10/2009)
BTW, if I upgrade to SP4 will this problem get resolved?
What problem? You've not yet identified what the problem is. And, upgrading to SP4 may be exactly NOT the thing to do. SP4 was a pretty steep upgrade with many, many changes in security and a couple of other doozies. You could break a lot of code by upgrading to SP4... you have to do some pretty serious regression testing before such an upgrade.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply