How to find dirty buffer pages and clear them - SQL Server
Dirty buffer pages are nothing but consuming more memory for even smaller queries on the SQL Server. By default the DBA shouldn't worry about these dirty pages in the buffer pool as they are taken care by the system using Write Ahead Logging (WAL)method. Only in case of point-in-time recovery you might need to worry about these dirty buffers as the server comes online it might find mismatch of pages that will keep the database in suspect mode.
Also you can take help of CHECKPOINT statement on a database to write out all the dirty pages in buffer to the disk. The memory management of Server will still hold them on the buffer although they are written to disk, to force clean them you must use DBCC DROPCLEANBUFFERS() statement, fyi SSP weblog informationon benchmarking.
Coming the usefulness in SQL Server 2005 you could take help of DMVs (again), before that in order to avoid any sort of confusion you need to disable the automatic cleanup by the system. You could use DBCC TRACEON for flags 3505 & 661.
To show the dirty buffer pages use:
select * from sys.dm_os_buffer_descriptors
where database_id = db_id() and is_modified = 1
order by page_id desc
go
To clear up then you should use
checkpoint
go
Then you would assume there will not be any dirty pages in the buffer pool, wrong. As explained above you need to run:
dbcc dropcleanbuffers()
go
Now it is confirmed that you will not see them:
select * from sys.dm_os_buffer_descriptors
where database_id = db_id()
order by page_id desc
go
**__________________________________**
SQL Server MVP, Sr. DBA & industry expert.
-
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it. It is also a power and you will gain by sharing it.