THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Andrew Kelly

Finding When Someone Last Ran a DBCC


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.

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 ;



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.


Andy Kelly

Published Wednesday, June 28, 2017 9:30 PM by Andrew Kelly

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



meadc said:    burberry bags online,burberry online store    Nike Air Max    coach factory outlet    prada handbags    school bags    pandora rings jewelry    ray ban sale    timberland shoes online    michael kors outlet store    kate spade shopping store    payless shoes for sale    coach outlet,coach handbags    puma outlet    prada outlet online store    prada online    kate spade outlet store    longchamp outlet online    Nike Schuhe Unisex    prada bags    am thuc    birkenstock sandals online    prada bags outlet    sunglasses online store    diesel mens watches    Louis Vuitton Handbags Outlet    Nike Air Presto    Louis Vuitton belts    ray ban clubmaster    yeezy shoes 350    yeezy 350    balenciaga outlet sale    nike huarache shoes    black friday michael kors online store    kate spade uk store    coach shoes for women    Promise Rings - Official    michael kors bags sale    louis vuitton online    michael kors bags outlet    coach outlet    pandora charms    Air Jordan Running    michael kors outlet sale    chrome hearts jewelry    kate spade handbags online store    school bags    ugg    burberry outlet online store    birkenstock outlet

me adc10.16

October 15, 2017 9:04 PM

wef said:    Chanel Outlet    payless shoes    80's Fashion    Nike Air Jordan Enfants    Michael Kors Outlet    Air Jordan Retro 3    ray ban shop    Promise Rings - Official    populaire Nike Lunarmax    puma outlet    coach factory outlet    katespade outlet    Nike Air Force    Coach Bags Outlet    NIKE Schuhe Shop    casio watches    michele watch    oakley australia    Air Jordan 10    qiqi fashion    birkenstock sandals    burberry bags outlet    michael kors shoes    Sunglasses outlet sale    Nike Air Jordan Enfants    ray ban clubmaster sunglasses    Cartier Watches    puma outlet online    hermes bag    louis vuitton outlet    coach factory outlet    clearance    prada outlet woodbury


February 10, 2018 7:27 PM

chenyingying said:


May 6, 2018 10:41 PM

loemrntdherid1 said:

May 20, 2018 8:21 PM

shenyuhang said:


June 1, 2018 7:00 PM

dongdong8 said:



June 29, 2018 2:45 AM

linying123 said:



July 16, 2018 8:27 PM

dongdong8 said:



July 23, 2018 11:28 PM

shenyuhang said:


July 23, 2018 11:34 PM

qqq said:

August 16, 2018 12:05 AM

chenjinyan said:


August 22, 2018 11:06 PM

shenyuhang said:


August 23, 2018 10:15 PM

chenlixiang said:



September 18, 2018 9:56 PM

kakakaoo said:

October 8, 2018 2:29 AM

chenjinyan said:



October 9, 2018 6:48 PM

chenqiuying said:


October 10, 2018 5:57 PM

qqq said:


March 27, 2019 12:44 AM

yanmaneee said:">">

June 29, 2020 11:02 PM

Leave a Comment


This Blog


Privacy Statement