The other day someone on Twitter asked how to find out when or who last ran a DBCC FREEPROCCACHE command on their SQL Server instance. Since this is not an unusual request I figured I would post a few examples of how to find DBCC commands or even many similar commands or events. If you had a regular or Extended Event trace going you could easily look for these types of commands assuming you had the correct events. However there are two ways that I know of to find these using built in default features that I will outline here. The first is actually a trace but I am talking about the black box trace that runs on every SQL Server by default unless you explicitly change that. A little disclaimer here in that BOL states this feature will be removed in a future version of SQL Server but it is in all the current versions so I won’t lose any sleep over that right now. This trace which is also known as the default trace captures a number of events and writes them to a series of log files in the log folder underneath where the SQL binaries normally reside. It uses a set of 5 (if memory serves me right) files that start with one named log.trc. Once that file gets full or you restart SQL Server it will open a 2nd file with the name log_1.trc and increment the _n each time this rollover process occurs. When you get the 5th file it will go back and overwrite the 1st one giving it a new name and thus there will only be 5 total. As you can guess this means the data is transient and can and usually will be overwritten at some point in the future depending on how much activity or restarts you may have.
If you don’t know where the files reside or what the current names are don’t worry. The query below will tell you which is the current log file for the default trace. I encourage you to look at BOL for the definitions of each of the columns as there are some other useful information in there as well.
SELECT t.[id], t.[path] FROM sys.traces AS t WHERE t.[is_default] = 1 ;
Normally the default trace ID is 1 but check to be sure. To see which events the default trace captures you can run the query below. Note that I used 1 in the query. I have listed the ones for SQL Server 2016 below that are included with the default trace.
SELECT DISTINCT [eventid], [name]
FROM fn_trace_geteventinfo(1) AS e
INNER JOIN sys.trace_events AS t ON e.[eventid] = t.[trace_event_id] ;
As you can see one of the events is Audit DBCC Event and for this example that is exactly what we were looking for. So to see if anyone has run this command in the timeframe the trace files cover you can run the script listed below.
DECLARE @Path VARCHAR(500) ;
SELECT @Path = path FROM sys.traces WHERE [is_default] = 1 ;
SET @Path = SUBSTRING(@Path,1,LEN(@Path) – CHARINDEX(‘\’,REVERSE(@Path))) + ‘\Log.trc’ ;
SELECT * FROM ::fn_trace_gettable(@Path,default)
WHERE TextData LIKE ‘dbcc free%’
Let me explain a few things about the script. I am getting the path of the current trace file and placing it into a variable. The current file name will almost certainly have a suffix of _nn just before the .trc extension. If I were to run the script as is I would only be reading the current log file and not the other 4 that preceded it. If all you care about is the current log file then fine but most will want to search all the existing log files. One way to do this is to simply replace the current file name with just log.trc and use default as the 2nd parameter as I did above in the fn_trace_gettable function. The default parameter value tells the function to read all files from that one onward. even though log.trc doesn’t actually exist it knows how to handle it and reads all of the existing trace files in order.
So if the string that we search on (here we use ‘dbcc free%’) is in any of the files it will return the matching rows. You may have to adjust the wildcards and such but I think you get the idea. Again remember that the data is transient so always look at the StartTime column in the logs to ensure you know which Date and Time range you are looking at. You can do something like this but I will leave that up to you.
SELECT MIN(StartTime) AS [Begin], MAX(StartTime) AS [End] FROM ::fn_trace_gettable(@Path,default)
A word of caution in that I never bothered to see just how resource intensive this function is. while I don’t expect any issues with normal use it is not something you want to be searching on every second. Be sensible and you should have no problems.
The 2nd way to look for things like this is to read each of the SQL Server error logs and search for your particular string. Keep in mind that the text in the error logs may not match the text in the trace files. Actually it probably won’t match so you may have to adjust your search string accordingly. The idea here is similar to searching the trace files only we will use some built in stored procedures called sp_readerrorlog and sp_enumerrorlogs instead of the trace function since we are looking in a completely different set of files. Let me give you a brief walk thru of the script below. First we declare some regular and table variables and assign our search string. We then use sp_enumerrorlogs to populate one of the table variables with a row for each SQL Server error log file. Since the number of files used is easily configurable we won’t know how many there are without this step. We get the max number (the first log is always 0) and start looping thru each one and call the sp_readerrorlog procedure passing 3 parameters. The 1st parameter defines the log number we want to read. The 2nd parameter is a 1 which denotes to read the SQL Server error log vs. say the SQL Agent log. And the 3rd parameter is the search string we want to search for. You can also pass an option 4th parameter to further refine the search if needed. We place any results into the other table variable and read them all at the end.
DECLARE @MaxID INT, @LogNum INT = 0, @SearchFor VARCHAR(256) ;
DECLARE @Logs TABLE ([Archive #] INT, [Date] DATETIME, [Log File Size (Byte)] BIGINT) ;
DECLARE @LogData TABLE ([LogDate] DATETIME, [ProcessIngfo] VARCHAR(64) , [Text] VARCHAR(MAX)) ;
SET @SearchFor = ‘DBCC FREE’ ;
INSERT INTO @Logs ([Archive #], [Date], [Log File Size (Byte)] ) EXEC sp_enumerrorlogs ;
SET @MaxID = (SELECT MAX([Archive #]) FROM @Logs) ;
WHILE @LogNum <= @MaxID
INSERT INTO @LogData ([LogDate], [ProcessIngfo], [Text]) EXEC sp_readerrorlog @LogNum, 1, @SearchFor ;
SET @LogNum = @LogNum + 1 ;
SELECT * FROM @LogData ORDER BY LogDate ;
You may find events in the error log that are not in the trace and visa versa. For instance you can see data or file autogrowth in the default trace but not in the error logs. And the error logs will show failed login attempts or backups and the trace will not.
So use this as you see fit but each can be a vital tool to see what is happening in your SQL Server instance on a daily basis.