SQL 2K Sp2 Memory Leak

[moonshadow]

This is a tough problem, so I could do with some help from you SQL Gurus (Terry - I hope you're reading this). We have 115 SQL Servers in branches throughout the UK. They are running Windows NT 4 SP6, SQL Server 2000 SP2. Over half of them are having Blue Screen errors and automatically rebooting on a regular basis (Some occassionally 4 times a day), but usually once a week.
Now there are probably several things causing this, but one support person has claimed that SQL Server uses more and more memory. I know it's designed to do this, but we're also getting a lot of 'virtual memory is low' messages on these machines too, and remote controlling them slows to a crawl. I think this implies some sort of memory leak in SQL Server. But how do I prove it, and find out what's causing it? Curiously, those servers where we have restricted the maximum amount of memory available to SQL Server have not blue screened since - presumably the OS is now protected from having all it's memory taken.
To make matters worse, we have a server at head office, configured to adminster these branch servers (using SQL Server's multi-server administration. which is great). It's running Windows 2K and SQL 2k SP2, with SQL restricted to 700MB of RAM. SQL Server on this runs out of memory about every 3 weeks - lots of messages about it in the SQL error log. When it does this it crashes SQL Agent on most of our branch servers - and some need a hard reboot. I'm monitoring it right now, using performance monitor, but can't see anything using excessive SQL memory - cache hit ratio of 99.989%. I plan to reboot it every 2 weeks to see if that helps. If not, I'm going to conclude that it's some query running which suddenly gobbles all it's memory.
Obviously, I'd like to upgrade to SP3a, but that's gonna take some considerable planning - even if I could get approval from management.
Apologies for the long post, but I wanted to give as much information as possible. ANY replies, no matter how far out, would be most welcome

 

[johnv20]

This may be off the point but sql server sp2 can be affected by the slammer virus amongst others, are you fully patched against all of these ?

 

[moonshadow]

I have warned management about this on several occasions, but as we're 'nice and safe' behind our firewall, with draconian restrictions on e-mail attachments and use of floppy disks and cd-roms, they've decided it's not a worry. Of course, if I was an external consultant, and they'd paid me £2000 for the advice, then that would be a different story.

 

[JayKusch]

May want to run this tool to see just how safe from the Slammer you really, or your execs feel they are - lol

http://www.microsoft.com/technet/treeview/default.asp?url=/technet/security/tools/SVAtool.asp

Thanks

J. Kusch

 

[johnv20]

Can you take 1 of the boxes that blue screens quite regulary
& run a trace on all events that are occurring.

I know there'll be an awful lot of data to sort through but it may help (especially if someone forgot to deallocate a cursor with an sp etc.)

 

[moonshadow]

Thanks for people looking at this post. I've resolved 2 out of 3 of the problems as follows:
1) SQL Server Memory link on our branches was caused by an in-house NT Service written using Dot Net. I now stop this service in the evening, and restart it in the morning - no more memory problems and blue screens.
2) Rebooting problems were caused by a dodgy batch of capacitors on all the motherboards. IBM have kindly replaced all of these motherboards now.
3) The memory leak on the server at head office has not been resolved. I'm now looking at rebooting this every week to prevent it occurring. There must be some way of seeing what's using the memory inside SQL Server (I've struggled with Performance monitor, but it's not really helping).
Anyone got any ideas?