How to Find out the Time Points When Someone Checks Database Consistency in SQL Server

There are several database consistency checker commands. It is recommended that we should rarely execute FREEPROCCACHE in production environments. In this article, we will learn how to identify when database consistency checker commands were executed in your production environment.

Using SSMS

Messages available in the SQL Server Error logs can be an information or an error. At the time of an issue, this error log will help you to investigate quickly.

To identify when was the DBCC FREEPROCCACHE was executed on your SQL server, follow the steps mentioned below:

Step 1: Connect to your SQL Server database engine using SQL Server management studio.

Step 2: From the object explorer, expand the folder “Management” and then navigate to the subfolder “SQL Server Logs”. The number of archive log files available per SQL instance can vary from 6 to 99. By default, you can see 7 log files under “SQL Server logs” and it would include 1 current log file and 6 archive log files.

Step 3: To read data from a log file, double click and open it. A new window will open and in this window, you will see data on the left and checkboxes on the right which allows the selection of other archive log files.

 Step 4: You can now search for “DBCC FREEPROCCACHE” to reach the row that has Date, Source, and Message that was logged at the time of execution of DBCC FREEPROCCACHE. As an alternative, you could filter multiple logs. The entry would look like the following message:

SQL Server has encountered 1 occurrence(s) of cache store flush for the ‘Bound Trees’ cache store (part of plan cache) due to ‘DBCC FREEPROCCACHE’ or ‘DBCC FREESYSTEMCACHE’ operations.

Using T-SQL

After connecting to your SQL Server database engine using SQL Server management studio, open a new query window and execute the query below:

 EXEC sp_readerrorlog 0, 1, 'DBCC FREEPROCCACHE' 

0 in the above query indicates that the current log file would be scanned for entries during the execution of DBCC FREEPROCCACHE

1 indicates that the log file used is SQL Server Error log file

Let us test it.

Before testing this, let us first recycle the log file so it is easy for us to view the content. To recycle the SQL Server Error log file, execute this command from a new query window.

sp_cycle_errorlog

And then execute this command:

DBCC FREEPROCCACHE

Now, if you check the SQL Server error log file, you will see entries as shown in the screenshot:

This script will recycle your SQL Server error log every day at 12:00 AM.

 USE [msdb]
 GO
 BEGIN TRANSACTION
 DECLARE @ReturnCode INT
 SELECT @ReturnCode = 0
 IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
 BEGIN
 EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  
 END
  
 DECLARE @jobId BINARY(16)
 EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Daily_Cycle_log', 
         @enabled=1, 
         @notify_level_eventlog=0, 
         @notify_level_email=0, 
         @notify_level_netsend=0, 
         @notify_level_page=0, 
         @delete_level=0, 
         @description=N'No description available.', 
         @category_name=N'[Uncategorized (Local)]', 
         @owner_login_name=N'ECPL\naveenv', @job_id = @jobId OUTPUT
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'S1', 
         @step_id=1, 
         @cmdexec_success_code=0, 
         @on_success_action=1, 
         @on_success_step_id=0, 
         @on_fail_action=2, 
         @on_fail_step_id=0, 
         @retry_attempts=0, 
         @retry_interval=0, 
         @os_run_priority=0, @subsystem=N'TSQL', 
         @command=N'Exec sp_cycle_errorlog', 
         @database_name=N'master', 
         @flags=0
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'S1', 
         @enabled=1, 
         @freq_type=4, 
         @freq_interval=1, 
         @freq_subday_type=1, 
         @freq_subday_interval=0, 
         @freq_relative_interval=0, 
         @freq_recurrence_factor=0, 
         @active_start_date=20171113, 
         @active_end_date=99991231, 
         @active_start_time=0, 
         @active_end_time=235959
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 COMMIT TRANSACTION
 GOTO EndSave
 QuitWithRollback:
     IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
 EndSave:
  
 GO

A regular usage of DBCC commands would help you to avoid SQL Server corruption. So make sure you have a maintenance plan to check the integrity of your databases.

Author Introduction:

Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including Outlook repair and excel recovery software products. For more information visit www.datanumen.com