SQL2005 Enterprise 64bit - MEMORYCLERK_SQLBUFFERPOOL

[UpstateNYAdmin]

Does anyone know how to flush the buffer pool for this (aside from restarting SQL svc)? Right now the AWE allocated (KB) is showing ~60GB but AWE is not enabled and I am getting errors from MOM since 98% of memory is begin consumed by this.

 

[SQLWilts]

Start with DBCC MEMORYSTATUS and work from there...

 

[SQLWilts]

Forget my last - try this

DBCC DROPCLEANBUFFERS

 

[SQLWilts]

But methinks you have more serious issues going on here.

Could you run the following and post the output?

sp_configure 'show advanced options',  1
go
reconfigure
go
sp_configure
go

SQL 64bit should ignore AWE memory.....

 

[UpstateNYAdmin]

Thanks, I ran the dropcleanbuffers first but that didnt change the AWE allocated number.

name                                minimum     maximum     config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
Ad Hoc Distributed Queries          0           1           1            1
affinity I/O mask                   -2147483648 2147483647  0            0
affinity mask                       -2147483648 2147483647  0            0
affinity64 I/O mask                 -2147483648 2147483647  0            0
affinity64 mask                     -2147483648 2147483647  0            0
Agent XPs                           0           1           1            1
allow updates                       0           1           0            0
awe enabled                         0           1           0            0
blocked process threshold           0           86400       0            0
c2 audit mode                       0           1           0            0
clr enabled                         0           1           0            0
common criteria compliance enabled  0           1           0            0
cost threshold for parallelism      0           32767       5            5
cross db ownership chaining         0           1           0            0
cursor threshold                    -1          2147483647  -1           -1
Database Mail XPs                   0           1           1            1
default full-text language          0           2147483647  1033         1033
default language                    0           9999        0            0
default trace enabled               0           1           1            1
disallow results from triggers      0           1           0            0
fill factor (%)                     0           100         0            0
ft crawl bandwidth (max)            0           32767       100          100
ft crawl bandwidth (min)            0           32767       0            0
ft notify bandwidth (max)           0           32767       100          100
ft notify bandwidth (min)           0           32767       0            0
index create memory (KB)            704         2147483647  0            0
in-doubt xact resolution            0           2           0            0
lightweight pooling                 0           1           0            0
locks                               5000        2147483647  0            0
max degree of parallelism           0           64          2            2
max full-text crawl range           0           256         4            4
max server memory (MB)              16          2147483647  58368        58368
max text repl size (B)              0           2147483647  65536        65536
max worker threads                  128         32767       0            0
media retention                     0           365         0            0
min memory per query (KB)           512         2147483647  1024         1024
min server memory (MB)              0           2147483647  0            0
nested triggers                     0           1           1            1
network packet size (B)             512         32767       4096         4096
Ole Automation Procedures           0           1           1            1
open objects                        0           2147483647  0            0
PH timeout (s)                      1           3600        60           60
precompute rank                     0           1           0            0
priority boost                      0           1           0            0
query governor cost limit           0           2147483647  0            0
query wait (s)                      -1          2147483647  -1           -1
recovery interval (min)             0           32767       0            0
remote access                       0           1           1            1
remote admin connections            0           1           1            1
remote login timeout (s)            0           2147483647  20           20
remote proc trans                   0           1           0            0
remote query timeout (s)            0           2147483647  600          600
Replication XPs                     0           1           0            0
scan for startup procs              0           1           0            0
server trigger recursion            0           1           1            1
set working set size                0           1           0            0
show advanced options               0           1           1            1
SMO and DMO XPs                     0           1           1            1
SQL Mail XPs                        0           1           1            1
transform noise words               0           1           0            0
two digit year cutoff               1753        9999        2049         2049
user connections                    0           32767       0            0
user options                        0           32767       0            0
Web Assistant Procedures            0           1           0            0
xp_cmdshell                         0           1           1            1

 

[mrdenny]

Flushing the cache won't reduce the memory that SQL has allocated to it. It will only empty the pages in memory, not release them back to the OS.

There is no way to force SQL to release the data back to the OS.

Your Max Server Memory is set at 57 Gigs. I assume you have 64 installed, with a couple in use by Windows. That means that SQL is using 91% of your physical memory. If MOM is saying that 98% of your memory is in use, you need to see what else is using all that memory, or reduce that amount of memory that SQL can use at the MAX by a couple of Gigs. It'll take SQL some time but it will eventually release that memory back to the OS.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[SQLWilts]

I concur

 

[UpstateNYAdmin]

Thanks - I'll trim down the max server memory.