available memory is decresed from 17GB to 7GB....

[DLu]

Hi:
production server SQL2005 Ent. sp2.
1. We increased our memory from 28GB to 64GB on 8/8, and reset the min=20GB, max=60GB. Howevery, found that yesterday, from task manger, the available memory was reduced from 8/10 40GB to 17GB, and today, it is reduced from 17GB to 7GB.

2. select spid, kpid, blocked, waittype, waittime, lastwaittype, dbid, login_time, last_batch, status, cmd from master..sysprocesses where waittime > 100000000

BROKER_TRANSMITTER 1 logintime: 2009-08-08 00:51:15.340 lastBatch:2009-08-08 00:51:15.340 cmd:background BRKR TASK
waittime is the highest (521,724,718). Is it normal?

3. SQLServer:Memory Manager Target Server Memory (KB) 59381896
SQLServer:Memory Manager Total Server Memory (KB) 55368448

It looks like that the SQL server is consuming many memory.
a. how could we know exactly what is going on about 'leaking' of the memory?
b. what are the possbile solutions? Should we enable AWE?

Thanks
David

[rmiao]

a. sql will use all available memory up to max memory setting, it's by design.
b. set max memory lower, AWE doesn't do much in this case and it's required on 32-bit machine to use memory above 4gb.

If you set max memory to 60gb then why worry?

[DLu]

Hi Ramio:

thanks for your memory tip.
the next issue the the master db's huge waittime? don't see this from other 64 bit sql 2005 servers.

spid waittime lastwaittype dbid login_time status cmd
71 559763375 BROKER_TRANSMITTER 1 2009-08-08 00:51 background BRKR TASK
72 559763375 BROKER_TRANSMITTER 1 2009-08-08 00:51 background BRKR TASK

We don't have any appl developed for the service broker, all appl are running at web server. but we do have database mirrowing on the prod server.

Thanks for your help
-D

[rmiao]

It's system service, expected high wait time usually.