I'll start by saying: "I'm clutching at straws here".

The background: We've been getting an odd problem here that memory usage on our production server is creeping up until it fills the page file and brings the server to a grinding halt. Restarting the server is the only thing that seems to work at that point. So far we've been unable to ascertain why. Personally, I've never seen these symptoms before. We're running SQLServer 2005 on Windows Server 2003, both fully patched and up to date - nothing special there. We're currently testing all the hardware so maybe that'll throw up some answers but I can't imagine a hardware fault behaving in this sort of cumulative way. I'm wracking my brain for anything SQL based we might have done that would cause this and, because I've never seen the symptoms before, I'm starting with all the 'odd' practices that we do here which I haven't done previously.

The thing that immediately springs to mind is that we use hordes of temp tables and cursors here. My personal view of both of these structures has always been that they're spawned by Satan and shouldn't be used on pain of death so I've always avoided using them in the past (except when absolutely neccessary) so I'm not very up on the finer points. They're very much built into the culture here, though, so I'm stuck with them. I can't imagine temp tables being the problem, they'd get cleaned up naturally when they fall out of scope, but if someone's forgotton to deallocate a cursor I imagine that might cause the problem.

I've done some googling and can find lots of info that the SqlServer resources don't get freed nothing that specifies exactly what those resources might be. I'm guessing memory is one of them but, given the time it's going to take me to weed through the entire database, I don't want to waste alot of effort on a supposition. Can anyone confirm for me that, for example, a cursor not being deallocated in an autojob running every 10 minutes would build up memory usage in this way?

Thanks, Guys
Your increasingly bemused funkster.