SQL2K not releasing memory
-
Had to restarted the server the morning after it decided to have one of its funny turns, but the time, it stopped in the middle of a trace which I set up before.
I noticed that the "Audit Logout" was the last event the server ran before it stopped, the results are as followed starting from earlier when it was working, note the time for these events.
9:25:00
CPU=0; Reads=209; Duration=0
9:25:10
CPU=0; Reads=42; Duration=0
9:23:25
CPU 593; Reads=62577; Duration=106203
9:22:11
CPU=890; Reads=93859; Duration=190500
Below is the last event before the server stopped.
9:22:16
CPU=718; Reads=77877; Duration = 215670
Anyone got any idea why this is taking so long?
TIA
Ken
-
dude, what's running there?
have you checked if you have any recursive cursors that you aren't closing AND deallocating?
have you perhaps set SET CURSOR_CLOSE_ON_COMMIT OFF, remembering that your server properties may have SET IMPLICIT_TRANSACTIONS OFF which implies that your connections are not being closed
you might want to check locks in the performance monitor, perhaps these are chewing up your memory
use ther performance monitor to check this out
presuming you're running at least win 2k...
Max
-
just had another idea: DBCC FREEPROCCACHE
looking at your original post, it seems that the only thing out of the ordinary is the number of stolen pages.
maybe schedule this to run on a regular basis until you find the leak
p.s. what's the worst vegetable to have on a boat...
a leak!
p.p.s what the worst vegetable to be stuck up max's nose...
a leak
I'll stop now
Max
-
Guys,
After much of testing and reconfiguring the server and database, it is now showing signs of stability. I think the problem was caused by screwed up indexing.
Anyway, I have reindexed the database and now the problem seems to have gone away, it has now been working for over a week with minor glitches.
Thanks for all your advise.
Best Regards
Ken
-
instead of restarting the SQL Server for realeasing the memory try the following command
DBCC DROPCLEANBUFFERS
You can use the DBCC DROPCLEANBUFFERS console command to release all data pages from memory. It will Remove the clean buffers from the buffer pool
and Clean up the display
Before using this command do not forget to execute the CHECKPOINT Command.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply