Unable to find Job SQL Server agent


  • Hi Guys,

    New to SQL Administration..

    Our SQL server eventviewer and logs shows a DBCC CHECKDB('database name') With physical only running on all the databases, but i am not able to find where it was scheduled..

    Looked under the jobs node in the SQL Server Agent.

    Any pointers appreciated..



    Wednesday, July 06, 2011 4:17 PM

All replies

  • Hello,

    In the log, have you the DBCC CHECKDB entries after the SQL Server start / at the beginning of the log? Then it's quite normal, after the restart SQL Server check's all DBs.

    It's also possible the a monitoring software validates the state of the databases. 

    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing
    Wednesday, July 06, 2011 4:23 PM
  • Do you use any BACKUP tool? like litespeed or so? I think litespeed does a DBCC CHECKDB... just a guess
    Wednesday, July 06, 2011 4:39 PM
  • No, We don't. Some more info

     It has a SPID and log shows it was executed by domain admin account..

    Wednesday, July 06, 2011 4:59 PM
  • Windows scheduler?
    Tibor Karaszi, SQL Server MVP | web | blog
    Wednesday, July 06, 2011 5:02 PM
  • I went through the task scheduler but could not find anything there..
    Wednesday, July 06, 2011 5:28 PM
  • try this

    select * from msdb..sysjobsteps where command like '%dbcc%'

    Wednesday, July 06, 2011 7:12 PM
  • look at the sql server maintenance plan.

    Thursday, July 07, 2011 1:27 AM
  • Hi BHJK,


    Please run below query to find out when and who is running DBCC CheckDB. Below query will work only on SQL 2005 and above. 

    if (select convert(int,value_in_use) from sys.configurations where name = 'default trace enabled' ) = 1 
    declare @curr_tracefilename varchar(500); 
    declare @base_tracefilename varchar(500); 
    declare @indx int ; 
    declare @temp_trace table (   dbname sysname,
    command nvarchar(MAX) collate database_default
    ,    LoginName varchar(MAX) collate database_default
    ,    StartTime datetime
    ,    errors int
    ,    repaired int
    ,    time nvarchar(10) collate database_default
    select @curr_tracefilename = path from sys.traces where is_default = 1 ; 
    set @curr_tracefilename = reverse(@curr_tracefilename); 
    select @indx = PATINDEX('%\%', @curr_tracefilename) ; 
    set @curr_tracefilename = reverse(@curr_tracefilename); 
    set @base_tracefilename = left( @curr_tracefilename,len(@curr_tracefilename) - @indx) + '\log.trc' ; 
    insert into @temp_trace 
    select databasename, substring(convert(nvarchar(MAX),TextData),36, patindex('%executed%',TextData)-36) as command
    ,    LoginName
    ,    StartTime
    ,    convert(int,substring(convert(nvarchar(MAX),TextData),patindex('%found%',TextData)+6,patindex('%errors %',TextData)-patindex('%found%',TextData)-6)) as errors
    ,    convert(int,substring(convert(nvarchar(MAX),TextData),patindex('%repaired%',TextData)+9,patindex('%errors.%',TextData)-patindex('%repaired%',TextData)-9)) repaired
    ,    substring(convert(nvarchar(MAX),TextData),patindex('%time:%',TextData)+6,patindex('%hours%',TextData)-patindex('%time:%',TextData)-6)+':'+substring(convert(nvarchar(MAX),TextData),patindex('%hours%',TextData)+6,patindex('%minutes%',TextData)-patindex('%hours%',TextData)-6)+':'+substring(convert(nvarchar(MAX),TextData),patindex('%minutes%',TextData)+8,patindex('%seconds.%',TextData)-patindex('%minutes%',TextData)-8) as time 
    from ::fn_trace_gettable( @base_tracefilename, default ) 
    where EventClass = 22 and substring(TextData,36,12) = 'DBCC CHECKDB';    
    declare @dbcount int
    set @dbcount = (select count(*) from sys.databases where state_desc = 'online')
    select dbname,@DBCount, command
    ,    LoginName
    ,    StartTime
    ,    errors
    ,    repaired
    ,    time 
    from @temp_trace a 
    where starttime > dateadd(dd,-7,getdate())
    order by StartTime desc ;    




    Thursday, July 07, 2011 2:19 AM
  • I would first make absolutely certain that this isn't an Agent job. If you have no jobs, for instance, you can stop the Agent service and see if this stops. If it isn't Agent, then it is something else. Perhaps run a Profiler/SQL Trace while it is happening and catch things (columns) such as application name etc
    Tibor Karaszi, SQL Server MVP | web | blog
    Thursday, July 07, 2011 3:17 AM