SOLVED

how to identify what started checkdb

%3CLINGO-SUB%20id%3D%22lingo-sub-2187525%22%20slang%3D%22en-US%22%3Ehow%20to%20identify%20what%20started%20checkdb%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2187525%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20few%20days%20ago%2C%20ERP%20user%20complaining%20that%20it%20run%20slow%2C%20so%20I%20check%20the%20server%20and%20found%20on%20the%20event%20viewer%20it%20seems%20checkdb%20is%20run%20about%20every%2010%20minutes%20for%20the%20last%20several%20days.%3CBR%20%2F%3Eon%20the%20previous%20month%20it%20seems%20only%20run%20twice%20about%2011am%20and%20that's%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Esince%20it%20only%20run%20twice%20in%20a%20day%20on%20previous%20month%2C%20so%20I%20thought%20it%20could%20be%20schedule%20job%20by%20ERP%20application%2C%20so%20I%20contacted%20ERP%20vendor%20support%20and%20they%20told%20me%20the%20ERP%20system%20doesn't%20schedule%20job%20to%20run%20checkdb%20only%20for%20sqlserver%20daily%20backup.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20check%20the%20windows%20task%20scheduler%20as%20well%20as%20sqlserver%20agent%20scheduler%3B%20no%20schedule%20for%20checkdb.%3C%2FP%3E%3CP%3Ethe%20only%20schedule%20job%20for%20sqlserver%20agent%20is%20to%20backup%20database%20at%2011pm.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20just%20wondering%20what%20cause%20checkdb%20to%20run%20automatically%3F%3C%2FP%3E%3CP%3Efrom%20what%20I%20found%20on%20the%20internet%20that%20sqlserver%20doesn't%20run%20checkdb%20automatically%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20the%20server%20there%20are%205%20database%20in%20this%201%20sqlserver%20instance%2C%20it%20seems%20it%20effect%203%20test%20database%2C%20the%20main%202%20database%20seems%20to%20be%20ok.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eso%20I%20start%20sql%20management%20studio%2C%20I%20can%20access%20all%20the%205%20databases%20with%20no%20issue%2C%20I%20can%20queries%20different%20tables%20from%20different%20database%20without%20any%20issue.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20server%20is%20running%20windows%20server%202012R2%20and%20sqlserver%202014%20standard%20edition.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20some%20of%20the%20sqlserver%20log%20entry.%3C%2FP%3E%3CP%3EDate%2CSource%2CSeverity%2CMessage%3CBR%20%2F%3E03%2F05%2F2021%2014%3A54%3A07%2Cspid99%2CUnknown%2CCHECKDB%20for%20database%20'M1_M1'%20finished%20without%20errors%20on%202016-08-02%2020%3A41%3A02.097%20(local%20time).%20This%20is%20an%20informational%20message%20only%3B%20no%20user%20action%20is%20required.%3CBR%20%2F%3E03%2F05%2F2021%2014%3A54%3A07%2Cspid99%2CUnknown%2CStarting%20up%20database%20'M1_M1'.%3CBR%20%2F%3E03%2F05%2F2021%2014%3A54%3A07%2Cspid99%2CUnknown%2CCHECKDB%20for%20database%20'M1_ET'%20finished%20without%20errors%20on%202017-01-02%2021%3A09%3A31.907%20(local%20time).%20This%20is%20an%20informational%20message%20only%3B%20no%20user%20action%20is%20required.%3CBR%20%2F%3E03%2F05%2F2021%2014%3A54%3A06%2Cspid99%2CUnknown%2CStarting%20up%20database%20'M1_ET'.%3CBR%20%2F%3E03%2F05%2F2021%2014%3A54%3A05%2Cspid70%2CUnknown%2CCHECKDB%20for%20database%20'M1_AH'%20finished%20without%20errors%20on%202017-01-02%2021%3A08%3A24.963%20(local%20time).%20This%20is%20an%20informational%20message%20only%3B%20no%20user%20action%20is%20required.%3CBR%20%2F%3E03%2F05%2F2021%2014%3A54%3A04%2Cspid70%2CUnknown%2CStarting%20up%20database%20'M1_AH'.%3CBR%20%2F%3E03%2F05%2F2021%2014%3A48%3A39%2Cspid70%2CUnknown%2CCHECKDB%20for%20database%20'M1_M1'%20finished%20without%20errors%20on%202016-08-02%2020%3A41%3A02.097%20(local%20time).%20This%20is%20an%20informational%20message%20only%3B%20no%20user%20action%20is%20required.%3CBR%20%2F%3E03%2F05%2F2021%2014%3A48%3A39%2Cspid70%2CUnknown%2CStarting%20up%20database%20'M1_M1'.%3CBR%20%2F%3E03%2F05%2F2021%2014%3A48%3A39%2Cspid70%2CUnknown%2CCHECKDB%20for%20database%20'M1_ET'%20finished%20without%20errors%20on%202017-01-02%2021%3A09%3A31.907%20(local%20time).%20This%20is%20an%20informational%20message%20only%3B%20no%20user%20action%20is%20required.%3CBR%20%2F%3E03%2F05%2F2021%2014%3A48%3A38%2Cspid70%2CUnknown%2CStarting%20up%20database%20'M1_ET'.%3CBR%20%2F%3E03%2F05%2F2021%2014%3A48%3A31%2Cspid92%2CUnknown%2CCHECKDB%20for%20database%20'M1_AH'%20finished%20without%20errors%20on%202017-01-02%2021%3A08%3A24.963%20(local%20time).%20This%20is%20an%20informational%20message%20only%3B%20no%20user%20action%20is%20required.%3CBR%20%2F%3E03%2F05%2F2021%2014%3A48%3A31%2Cspid92%2CUnknown%2CStarting%20up%20database%20'M1_AH'.%3CBR%20%2F%3E03%2F05%2F2021%2014%3A48%3A28%2Cspid92%2CUnknown%2CCHECKDB%20for%20database%20'M1_AH'%20finished%20without%20errors%20on%202017-01-02%2021%3A08%3A24.963%20(local%20time).%20This%20is%20an%20informational%20message%20only%3B%20no%20user%20action%20is%20required.%3CBR%20%2F%3E03%2F05%2F2021%2014%3A48%3A27%2Cspid92%2CUnknown%2CStarting%20up%20database%20'M1_AH'.%3CBR%20%2F%3E03%2F05%2F2021%2014%3A48%3A27%2Cspid92%2CUnknown%2CCHECKDB%20for%20database%20'M1_AH'%20finished%20without%20errors%20on%202017-01-02%2021%3A08%3A24.963%20(local%20time).%20This%20is%20an%20informational%20message%20only%3B%20no%20user%20action%20is%20required.%3CBR%20%2F%3E03%2F05%2F2021%2014%3A48%3A26%2Cspid92%2CUnknown%2CStarting%20up%20database%20'M1_AH'.%3CBR%20%2F%3E03%2F05%2F2021%2014%3A48%3A25%2Cspid92%2CUnknown%2CCHECKDB%20for%20database%20'M1_AH'%20finished%20without%20errors%20on%202017-01-02%2021%3A08%3A24.963%20(local%20time).%20This%20is%20an%20informational%20message%20only%3B%20no%20user%20action%20is%20required.%3CBR%20%2F%3E03%2F05%2F2021%2014%3A48%3A25%2Cspid92%2CUnknown%2CStarting%20up%20database%20'M1_AH'.%3CBR%20%2F%3E03%2F05%2F2021%2014%3A48%3A21%2Cspid92%2CUnknown%2CCHECKDB%20for%20database%20'M1_M1'%20finished%20without%20errors%20on%202016-08-02%2020%3A41%3A02.097%20(local%20time).%20This%20is%20an%20informational%20message%20only%3B%20no%20user%20action%20is%20required.%3CBR%20%2F%3E03%2F05%2F2021%2014%3A48%3A20%2Cspid92%2CUnknown%2CStarting%20up%20database%20'M1_M1'.%3CBR%20%2F%3E03%2F05%2F2021%2014%3A48%3A20%2Cspid92%2CUnknown%2CCHECKDB%20for%20database%20'M1_ET'%20finished%20without%20errors%20on%202017-01-02%2021%3A09%3A31.907%20(local%20time).%20This%20is%20an%20informational%20message%20only%3B%20no%20user%20action%20is%20required.%3CBR%20%2F%3E03%2F05%2F2021%2014%3A48%3A20%2Cspid92%2CUnknown%2CStarting%20up%20database%20'M1_ET'.%3CBR%20%2F%3E03%2F05%2F2021%2014%3A48%3A20%2Cspid92%2CUnknown%2CCHECKDB%20for%20database%20'M1_AH'%20finished%20without%20errors%20on%202017-01-02%2021%3A08%3A24.963%20(local%20time).%20This%20is%20an%20informational%20message%20only%3B%20no%20user%20action%20is%20required.%3CBR%20%2F%3E03%2F05%2F2021%2014%3A48%3A19%2Cspid92%2CUnknown%2CStarting%20up%20database%20'M1_AH'.%3CBR%20%2F%3E03%2F05%2F2021%2014%3A47%3A20%2Cspid90%2CUnknown%2CCHECKDB%20for%20database%20'M1_M1'%20finished%20without%20errors%20on%202016-08-02%2020%3A41%3A02.097%20(local%20time).%20This%20is%20an%20informational%20message%20only%3B%20no%20user%20action%20is%20required.%3CBR%20%2F%3E03%2F05%2F2021%2014%3A47%3A20%2Cspid90%2CUnknown%2CStarting%20up%20database%20'M1_M1'.%3CBR%20%2F%3E03%2F05%2F2021%2014%3A47%3A19%2Cspid90%2CUnknown%2CCHECKDB%20for%20database%20'M1_ET'%20finished%20without%20errors%20on%202017-01-02%2021%3A09%3A31.907%20(local%20time).%20This%20is%20an%20informational%20message%20only%3B%20no%20user%20action%20is%20required.%3CBR%20%2F%3E03%2F05%2F2021%2014%3A47%3A19%2Cspid90%2CUnknown%2CStarting%20up%20database%20'M1_ET'.%3CBR%20%2F%3E03%2F05%2F2021%2014%3A47%3A18%2Cspid90%2CUnknown%2CCHECKDB%20for%20database%20'M1_AH'%20finished%20without%20errors%20on%202017-01-02%2021%3A08%3A24.963%20(local%20time).%20This%20is%20an%20informational%20message%20only%3B%20no%20user%20action%20is%20required.%3CBR%20%2F%3E03%2F05%2F2021%2014%3A47%3A18%2Cspid90%2CUnknown%2CStarting%20up%20database%20'M1_AH'.%3CBR%20%2F%3E03%2F05%2F2021%2014%3A47%3A18%2Cspid90%2CUnknown%2CCHECKDB%20for%20database%20'M1_M1'%20finished%20without%20errors%20on%202016-08-02%2020%3A41%3A02.097%20(local%20time).%20This%20is%20an%20informational%20message%20only%3B%20no%20user%20action%20is%20required.%3CBR%20%2F%3E03%2F05%2F2021%2014%3A47%3A18%2Cspid90%2CUnknown%2CStarting%20up%20database%20'M1_M1'.%3CBR%20%2F%3E03%2F05%2F2021%2014%3A47%3A17%2Cspid90%2CUnknown%2CCHECKDB%20for%20database%20'M1_ET'%20finished%20without%20errors%20on%202017-01-02%2021%3A09%3A31.907%20(local%20time).%20This%20is%20an%20informational%20message%20only%3B%20no%20user%20action%20is%20required.%3CBR%20%2F%3E03%2F05%2F2021%2014%3A47%3A17%2Cspid90%2CUnknown%2CStarting%20up%20database%20'M1_ET'.%3CBR%20%2F%3E03%2F05%2F2021%2014%3A47%3A17%2Cspid90%2CUnknown%2CCHECKDB%20for%20database%20'M1_AH'%20finished%20without%20errors%20on%202017-01-02%2021%3A08%3A24.963%20(local%20time).%20This%20is%20an%20informational%20message%20only%3B%20no%20user%20action%20is%20required.%3CBR%20%2F%3E03%2F05%2F2021%2014%3A47%3A16%2Cspid90%2CUnknown%2CStarting%20up%20database%20'M1_AH'.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20appreciated%20if%20anyone%20could%20point%20me%20in%20the%20right%20direction%20on%20how%20to%20find%20out%20what%20process%2Fprogram%2Fwho%20initiate%20checkdb.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3CP%3E-%20Haris%20-%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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 -

2 Replies
best response confirmed by hkusumo (New Contributor)
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

Hi Olaf,

Thank you,

For some reason the auto close is set to true, the ERP vendor also puzzle why it set to true, their explanation it could be from way back when they use vb 6 to develop their ERP system and get carried over.

I set auto_close to false now for all database.

Thanks again.

Regards
- Haris -