We have a system running SQL Server 2008 R2 on a Hyper-V VM (64GB RAM and 16 virtual cores).
Another VM (the application server) within the system accesses the db server quite intensively for both querying and data updates.
We have been experiencing two main exceptions in the application server at the time were a particular maintenance sub plan stored procedure is executed.
The two exceptions are:
System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding (.......)
System.Data.SqlClient.SqlException (0x80131904): The app domain with specified version id (1859) was unloaded due to memory pressure and could not be found. (.....)
The maintenance plan stored procedure executed at this time has the following statements:
DBCC FREESYSTEMCACHE ('ALL') DBCC FREESESSIONCACHE DBCC FREEPROCCACHE CHECKPOINT DBCC DROPCLEANBUFFERS
This is obviously causing the issues after some testing. Also I have found out that:
Timeout exception is caused by this:
CHECKPOINT DBCC DROPCLEANBUFFERS
Memory pressure exception is caused by this:
DBCC FREESYSTEMCACHE ('ALL') DBCC FREESESSIONCACHE DBCC FREEPROCCACHE
Unfortunately we don't know the reason for having this maintenance task happening. My first thought is remove this task, but I don't know if there would be more problems by not having these running.
Are these DBCC commands really necessary?