Mar 05 2021 01:14 AM
Hi,
A few days ago, ERP user complaining that it run slow, so I check the server and found on the event viewer it seems checkdb is run about every 10 minutes for the last several days.
on the previous month it seems only run twice about 11am and that's it.
since it only run twice in a day on previous month, so I thought it could be schedule job by ERP application, so I contacted ERP vendor support and they told me the ERP system doesn't schedule job to run checkdb only for sqlserver daily backup.
I check the windows task scheduler as well as sqlserver agent scheduler; no schedule for checkdb.
the only schedule job for sqlserver agent is to backup database at 11pm.
I just wondering what cause checkdb to run automatically?
from what I found on the internet that sqlserver doesn't run checkdb automatically
On the server there are 5 database in this 1 sqlserver instance, it seems it effect 3 test database, the main 2 database seems to be ok.
so I start sql management studio, I can access all the 5 databases with no issue, I can queries different tables from different database without any issue.
The server is running windows server 2012R2 and sqlserver 2014 standard edition.
Here is some of the sqlserver log entry.
Date,Source,Severity,Message
03/05/2021 14:54:07,spid99,Unknown,CHECKDB for database 'M1_M1' finished without errors on 2016-08-02 20:41:02.097 (local time). This is an informational message only; no user action is required.
03/05/2021 14:54:07,spid99,Unknown,Starting up database 'M1_M1'.
03/05/2021 14:54:07,spid99,Unknown,CHECKDB for database 'M1_ET' finished without errors on 2017-01-02 21:09:31.907 (local time). This is an informational message only; no user action is required.
03/05/2021 14:54:06,spid99,Unknown,Starting up database 'M1_ET'.
03/05/2021 14:54:05,spid70,Unknown,CHECKDB for database 'M1_AH' finished without errors on 2017-01-02 21:08:24.963 (local time). This is an informational message only; no user action is required.
03/05/2021 14:54:04,spid70,Unknown,Starting up database 'M1_AH'.
03/05/2021 14:48:39,spid70,Unknown,CHECKDB for database 'M1_M1' finished without errors on 2016-08-02 20:41:02.097 (local time). This is an informational message only; no user action is required.
03/05/2021 14:48:39,spid70,Unknown,Starting up database 'M1_M1'.
03/05/2021 14:48:39,spid70,Unknown,CHECKDB for database 'M1_ET' finished without errors on 2017-01-02 21:09:31.907 (local time). This is an informational message only; no user action is required.
03/05/2021 14:48:38,spid70,Unknown,Starting up database 'M1_ET'.
03/05/2021 14:48:31,spid92,Unknown,CHECKDB for database 'M1_AH' finished without errors on 2017-01-02 21:08:24.963 (local time). This is an informational message only; no user action is required.
03/05/2021 14:48:31,spid92,Unknown,Starting up database 'M1_AH'.
03/05/2021 14:48:28,spid92,Unknown,CHECKDB for database 'M1_AH' finished without errors on 2017-01-02 21:08:24.963 (local time). This is an informational message only; no user action is required.
03/05/2021 14:48:27,spid92,Unknown,Starting up database 'M1_AH'.
03/05/2021 14:48:27,spid92,Unknown,CHECKDB for database 'M1_AH' finished without errors on 2017-01-02 21:08:24.963 (local time). This is an informational message only; no user action is required.
03/05/2021 14:48:26,spid92,Unknown,Starting up database 'M1_AH'.
03/05/2021 14:48:25,spid92,Unknown,CHECKDB for database 'M1_AH' finished without errors on 2017-01-02 21:08:24.963 (local time). This is an informational message only; no user action is required.
03/05/2021 14:48:25,spid92,Unknown,Starting up database 'M1_AH'.
03/05/2021 14:48:21,spid92,Unknown,CHECKDB for database 'M1_M1' finished without errors on 2016-08-02 20:41:02.097 (local time). This is an informational message only; no user action is required.
03/05/2021 14:48:20,spid92,Unknown,Starting up database 'M1_M1'.
03/05/2021 14:48:20,spid92,Unknown,CHECKDB for database 'M1_ET' finished without errors on 2017-01-02 21:09:31.907 (local time). This is an informational message only; no user action is required.
03/05/2021 14:48:20,spid92,Unknown,Starting up database 'M1_ET'.
03/05/2021 14:48:20,spid92,Unknown,CHECKDB for database 'M1_AH' finished without errors on 2017-01-02 21:08:24.963 (local time). This is an informational message only; no user action is required.
03/05/2021 14:48:19,spid92,Unknown,Starting up database 'M1_AH'.
03/05/2021 14:47:20,spid90,Unknown,CHECKDB for database 'M1_M1' finished without errors on 2016-08-02 20:41:02.097 (local time). This is an informational message only; no user action is required.
03/05/2021 14:47:20,spid90,Unknown,Starting up database 'M1_M1'.
03/05/2021 14:47:19,spid90,Unknown,CHECKDB for database 'M1_ET' finished without errors on 2017-01-02 21:09:31.907 (local time). This is an informational message only; no user action is required.
03/05/2021 14:47:19,spid90,Unknown,Starting up database 'M1_ET'.
03/05/2021 14:47:18,spid90,Unknown,CHECKDB for database 'M1_AH' finished without errors on 2017-01-02 21:08:24.963 (local time). This is an informational message only; no user action is required.
03/05/2021 14:47:18,spid90,Unknown,Starting up database 'M1_AH'.
03/05/2021 14:47:18,spid90,Unknown,CHECKDB for database 'M1_M1' finished without errors on 2016-08-02 20:41:02.097 (local time). This is an informational message only; no user action is required.
03/05/2021 14:47:18,spid90,Unknown,Starting up database 'M1_M1'.
03/05/2021 14:47:17,spid90,Unknown,CHECKDB for database 'M1_ET' finished without errors on 2017-01-02 21:09:31.907 (local time). This is an informational message only; no user action is required.
03/05/2021 14:47:17,spid90,Unknown,Starting up database 'M1_ET'.
03/05/2021 14:47:17,spid90,Unknown,CHECKDB for database 'M1_AH' finished without errors on 2017-01-02 21:08:24.963 (local time). This is an informational message only; no user action is required.
03/05/2021 14:47:16,spid90,Unknown,Starting up database 'M1_AH'.
I appreciated if anyone could point me in the right direction on how to find out what process/program/who initiate checkdb.
Thank you.
Regards
- Haris -
Mar 07 2021 11:43 PM
Solution
03/05/2021 14:54:04,spid70,Unknown,Starting up database 'M1_AH'.
Your databases are set to "Auto close"? Not good in terms of performance.
Use SQL Server Profiler to trace the commands/accesses or setup Audit
Create a server audit and database audit specification
Olaf
Mar 09 2021 02:00 PM
Mar 07 2021 11:43 PM
Solution
03/05/2021 14:54:04,spid70,Unknown,Starting up database 'M1_AH'.
Your databases are set to "Auto close"? Not good in terms of performance.
Use SQL Server Profiler to trace the commands/accesses or setup Audit
Create a server audit and database audit specification
Olaf