Post reply

Memory Issue

  • March 2, 2009 at 6:45 am

    #207066

    Hi All,

    I have a small memory issue on my SQL 2000 Sp3A DB that runs on a Windows 2003 box. It has total of 8 Gig RAM. (Set to use 6 GIG of RAM for SQL Server)

    It all happened after a Stored procedure with no SET NOCOUNT ON was run in a loop. All queries are still running except that the Profiler Trace is not starting. There it gives the error insufficient memory. On the SQL Server error log, I see

    WARNING: Failed to reserve contiguous memory of Size= 45613056.

    Buffer Distribution: Stolen=53491 Free=5014 Procedures=5611

    Inram=0 Dirty=1411 Kept=0

    I/O=0, Latched=169, Other=720736

    Buffer Counts: Commited=786432 Target=786432 Hashed=722316

    InternalReservation=649 ExternalReservation=1151 Min Free=1024

    Procedure Cache: TotalProcs=2239 TotalPages=5611 InUsePages=2659

    Dynamic Memory Manager: Stolen=59034 OS Reserved=8088

    OS Committed=8044

    OS In Use=7962

    Query Plan=5781 Optimizer=0

    General=11090

    Utilities=755 Connection=2799

    Global Memory Objects: Resource=2668 Locks=47377

    SQLCache=603 Replication=180

    LockBytes=2 ServerGlobal=49

    Xact=1750

    Query Memory Manager: Grants=5 Waiting=0 Maximum=109104 Available=107887

    Does anyone know why this is happening? Anything that can be done without restarting the SQL Server?

    Thanks

    Roy

    -Roy

  • March 2, 2009 at 10:16 am

    #951960

    Isnt there anybody who can help me here?? :crying:

    -Roy

  • March 2, 2009 at 2:02 pm

    #952104

    Can you include SET NOCOUNT ON in your SP(if its not going to brk anything) and see if tht helps.

    Also, do u see too many unparameterized queries hitting ur sql server at tht time?

    MJ

  • March 2, 2009 at 2:05 pm

    #952105

    That has already been added. Thats the first thing I id. But by that time, the Damage had already been done. Now I need to get out of that position.

    -Roy

  • March 2, 2009 at 2:09 pm

    #952106

    If you don't want to restart then clear the procedure cache, I know it affects the performance but ..... If possible try tht.

    Also, do u see too many unparameterized queries hitting ur server?

    MJ

  • March 2, 2009 at 2:15 pm

    #952111

    try this.....

    DBCC FREEPROCCACHE

    DBCC DROPCLEANBUFFERS

    MJ

  • March 2, 2009 at 2:16 pm

    #952112

    DBBC FREEPROCCACHE will clear the Query plans. But not the Buffer cache. I am thinking more like DBCC DROPCLEANBUFFERS. This will clean out the buffers. I think it is the Buffer that is the problem. I can compile without any issues.

    I dont see any more non parameterized queries hitting the DB

    -Roy

  • March 2, 2009 at 2:20 pm

    #952115

    Try that and let us know the outcome. I posted tht a minute back after posting my previous reply.

    MJ

  • March 2, 2009 at 2:25 pm

    #952119

    I was planning to do it tomorrow morning. I usually dont do anything on the server in the evening time. Always in the morning. I will post how it went tomorrow

    -Roy

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply