If SQL Server is running slow and operations are throwing errors due to lack of memory, it is necessary to look into memory issue. If SQL Server is restarted all the cache memory is automatically cleaned up. In production server it is not possible to restart the server. In this scenario following three commands can be very useful.
When executed following three commands will free up memory for SQL Server by cleaning up its cache.
DBCC FREESYSTEMCACHE
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE
Reference : Pinal Dave (https://blog.sqlauthority.com)
31 Comments. Leave new
In My Production server My RAM memory shows 73 GB is used. In Proccess sql is taking around 66 GB. Does it always takes 66 gb, how to free memory when it is not using.
I have query regarding buffer catch,after restarting server cache get cleans,It means that dirty page and clean page count gets zero value and DBCC DROPCLEANBUFFERS also does the same process.
But if dirty Pages Count is less than Clean Pages then What is the way to remove only clean pages from memory? and dirty pages should remain in buffer cache so that reading from disk can be minimize.Please suggest suitable solution.
I read about DBCC DROPCLEANBUFFERS. When run, this will remove all clean pages from memory for an entire database.I Just need to know how often this command should be run??as Pages Read/sec and pages write/sec also have high value. Please Suggest.
What exactly you are trying to achieve?
Sorry for confusion,i wanted to ask that if memory in buffercache increases per day then how often Drop clean buffer command should be run??I want to ask one more question regarding formula for memory utilization.the value of total physical available memory gets from sys.dm-os-sys-memory and buffer cache memory from sys.dm-os-memory-clerks(sum(pages-kb)).what else should be considered to get exact memory utilization so that we get exact figure what is available in sys.dm-os-memory(available-physical-memory-kb)