> Hi all,
> I'm having a recurring problem with my ASP/SQL Server 7
> website. The website will be running smoothly, with a
> fairly consistent level of (externally visible)
> performance, until, after about a week, I'll suddenly
> start to get the following error...
> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
> [Microsoft][ODBC SQL Server Driver][SQL Server]There is
> insufficient system memory to run this query.
> ...wherever a SQL query is made. Rebooting the SQL Server
> machine clears up the problem, until it reappears about a
> week later.
> Examining the SQL error log, everything seems fine for
> days, until the following messages start appearing each
> time a stored proc attempts execution:
> WARNING: Clearing procedure cache to free contiguous
> Buffer Distribution: Stolen=3562 Free=128944 Procedures=20
> Inram=0 Dirty=69219 Kept=27
> I/O=0, Latched=0, Other=21420
> Buffer Counts: Commited=223192 Target=223192 Hashed=90666
> ExternalReservation=0 Min Free=81
> Procedure Cache: TotalProcs=2 TotalPages=20 InUsePages=19
> Dynamic Memory Manager: Stolen=3582 OS=439 General=3864
> Query Plan=77 Optimizer=6
> Utilities=5 Connection=69
> Global Memory Objects: Resource=443 Locks=1639 XDES=1
> SQLCache=94 tion=2
> LockBytes=2 ServerGlobal=20
> Query Memory Manager: Grants=0 Waiting=0 Maximum=161478
> Does anyone speak the language of SQL Server internals,
> who can tell me what this means?
> Just from the behavior I'm guessing there's a loop or
> somesuch in my code that's eating procedure cache memory,
> but what's the best way to track and pinpoint the stored
> proc where it's occurring? I was thinking I could use
> Profiler to track the start and completion of procs, then
> get the longest running from that. But is there any way
> to see at a granular level what's impacting the memory?
> I've been going through KB articles as fast as I can read
> them (currently trying things suggested in "HOW TO:
> Troubleshoot Application Performance with SQL Server"
> and "HOW TO: Troubleshoot the Performance of Ad-Hoc
> Queries") but I'm hoping someone out there may have run
> into something similar before and be able to offer
> This is SQL Server 7, running on a dedicated Windows 2000
> Server with 2GB RAM and dual 1.8 GHz processors. I'm
> using the Full Text Search add-in, and as per Microsoft's
> recommendation set the server's total virtual memory to 6
> GB and the SQL server max memory setting to 3 GB.
> Thanks for any and all advice,