Questions about DBCC FREEPROCCACHE

Posted on

Question :

I used spAskBrent for troubleshooting a SQL Server 2005 performance issue.

I found the problem was someone had ran DBCC FREEPROCCACHE. It caused low performance (very low PLE) on my database and queries to time out.

How can I find out who ran the query to free the plan cache?

Answer :

You should be able to locate this within the default trace for SQL Server, if it is still enabled on the instance. One of the events captured is the Security Audit: Audit DBCC Event, can read more on the event here.

If you find the default trace is enabled the following query from MSSQLTips.com article to find when DBCC were executed:

DECLARE @path NVARCHAR(260)

SELECT @path=path FROM sys.traces WHERE is_default = 1

--Security Audit: Audit DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKCATALOG,
--DBCC CHECKALLOC, DBCC CHECKFILEGROUP Events, and more.
SELECT TextData, Duration, StartTime, EndTime, SPID, ApplicationName, LoginName  
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass IN (116) AND TextData like 'DBCC%CHECK%'
ORDER BY StartTime DESC

Leave a Reply

Your email address will not be published. Required fields are marked *