2

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?

0

1 Answer 1

2

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
1
  • I tested this and it works!
    – SQLPRODDBA
    Nov 23, 2015 at 9:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.