Can't clean buffer cache

Hi,

My problem is that I cannot completely clean buffer cache on SQL Server 2005 version 9.00.2047.00 (probably SP1).

Right after I run DBCC DROPCLEANBUFFERS in the context of my database (this is development server, and so far I am only the one who is working with a particular database), I run a script that quetries sys.dm_os_buffer_descriptors view also from the context of my database to make sure that the buffer cache is really clean. However it shows large number of entries totalling 42 MB.

I ran both DBCC an the script in the past too, and it always showed nothing in the results, that means that buffers were really clean. The reason why I am running this is for benchmarking of existing and new application.

Does anybody have any idea, suggestions, how to troubleshoot this issue ? I already closed all connections to this database, but rebooting the server is not an option since other people are also working on it.

Thanks

BoL (link) does suggest issuing a CHECKPOINT before the DBCC DROPCLEANBUFFERS, have you tried that?|||

Hi Mark

Moffs suggestion is correct, you will need to run the CHECKPOINT command.

Checkpoint all the databases that you want to have buffers to cold and then run the dbcc command.

I don't think you can get rid of all the buffers from the sysdatabases.

Could you please post the output of the following command once you have completed the checkpoint and dbcc.

Code Snippet

select distinct db_name(database_id) from sys.dm_os_buffer_descriptors

Jag