May 14, 2003 at 11:12 am
We have a W2K server running SQL 2K sp2 that has been getting this error intermittently for several weeks. The symptoms include the SQL exe buildup of memory usage until the error occurs. The system then begins refusing connections until we have to restart SQL. This server is supporting our website.
I've looked at KB 811887 and it describes our situation almost exactly but I see no calls to the sp_OACreate stored proc and so can't see where to insert a call to sp_OADestroy. KB 282229 says apply the next service pack but also says the problem was corrected in sp1.
We started a blackbox trace after the last SQL restart but have gleaned no new information from it. [Just waiting for the next meltdown!]
I see several references to this problem on the MS public.sqlserver newsgroups but the only consistent response was to apply the next service pack and that was suggested even prior to sp1. Due to operational considerations I'm not ready to apply sp3 yet.
Comments and suggestions appreciated.
Bob
May 14, 2003 at 12:12 pm
Mmeory leak may also be caused by otehr components such as SQL mail, Full-text search, Extend Stored Procedures and OLE DB provider etc.
Do you see any error messages like "Clearing procedure cache to free contiguous memory" in SQL Server errorlogs?
May 14, 2003 at 12:25 pm
Allen is right. Similar situation happened to our main sql server. Once we run a new application all the time on the server. The memory leaks. I must reboot the server to release the memory. Then removed the application to the web server.
Robert
May 14, 2003 at 12:57 pm
Thanks Allen & Robert
Here are the other errors and interesting lines in the SQL Server Logs:
Operating system error 1: Incorrect function...
Error: 17059, Severity: 18, State: 0
Error 17882 ... [subject error]
Error: 17882, Severity: 18, State: 1
Stored function 'xp_fixeddrives' in the library 'xpstar.dll' generated an access violation. SQL Server is terminating process 55..
Error: 0, Severity: 20, State: 0
Stored function 'xp_fixeddrives' in the library 'xpstar.dll' generated an access violation. SQL Server is terminating process 58..
Error: 0, Severity: 20, State: 0
Using 'xpsqlbot.dll' version '2000.80.194' to execute extended stored procedure 'xp_qv'.
Using 'xpstar.dll' version '2000.80.534' to execute extended stored procedure 'xp_instance_regread'.
Bob
May 14, 2003 at 1:39 pm
Run xp_fixeddrives from QA to see whether same error messages appearing in your SQL Server Errorlog.
Do you have second SQL Server? If you have, Run same command too in the second server and compare the version and date of xpstar.dll on both of servers.
May 14, 2003 at 2:31 pm
I ran xp_fixeddrives on 3 servers; the problem box and another production server. No error on either and the xpstar.dll version is the same. Also no error on my development server but the xpstar.dll version was older.
This is an infrequent error, in fact the errors I listed are all that have occurred in the last month. The server or SQL Server has been restarted 5 times in 30 days; usually just after the subject error has occurred.
Thanks
Bob
May 14, 2003 at 5:02 pm
Are there any other applications also running in this server? Keep watching SQL Server errorlog and machine application/system logs until server meltdown. Hopefully you could catch up some interesting messages.
Meanwhile, you may use performance monitor to monitor those processes that could lead the memory leak on counters Process : PoolNonpaged Bytes, Pool Paged Bytes, Private bytes, Working Set.
¡¤ Memory Usage and Virtual Memory increasing (even during inactivity) ¡¤ Paged Pool Memory being consumed and not returned ¡¤ Non-paged Pool Memory being consumed and not returned ¡¤ Private bytes increasing.
May 15, 2003 at 9:04 am
No other applications. I will set up the performance monitor.
Thanks
Bob
quote:
Are there any other applications also running in this server? Keep watching SQL Server errorlog and machine application/system logs until server meltdown. Hopefully you could catch up some interesting messages.Meanwhile, you may use performance monitor to monitor those processes that could lead the memory leak on counters Process : PoolNonpaged Bytes, Pool Paged Bytes, Private bytes, Working Set.
¡¤ Memory Usage and Virtual Memory increasing (even during inactivity) ¡¤ Paged Pool Memory being consumed and not returned ¡¤ Non-paged Pool Memory being consumed and not returned ¡¤ Private bytes increasing.
May 15, 2003 at 11:41 am
Allen
I'm not much of a Performance Monitor user but when I set up the four counters you recommended they all read out at 100% [or 0 but I only see one horizontal line at 100%].
Does this give me any useful information?
Bob
May 15, 2003 at 12:23 pm
In order to see the trend of memory usage for processes in your SQL Server box, you have to monitor these counters for quite period of time (days or weeks). You can log these counters into performance log files for later review. You may ask your NT administrators how to do it.
You also can monitor them from the processes tab of Task Manager, select menu item "View/Select Columns", From this dialog, select "Memory Usage", "VM Size", "Paged Pool", "Nonpaged Pool".
May 20, 2003 at 2:34 pm
For what it's worth, we discovered an error in Cold Fusion coding that caused our memory leak. We hadn't seen the error til the slammer patch was installed.
Fixed the CF code (and upgraded to SP3); all's well.
Al
May 20, 2003 at 3:24 pm
Appreciate the response.
We don't use Cold Fusion. Intend to install SP3 when operational commitments allow.
The Blackbox trace we're running hasn't given any useful data; it actually is reporting "Failed to read file data. File may be corrupt or it may have a wrong data format." and does not show the portion of the file that would have the time that the process failed. That's opening the trace in SQL Profiler.
Does anyone know of any other program that will read these trace files?
Bob
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply