Querying CommandLog

If you have implemented Ola Hallenger scripts, http://ola.hallengren.com/, you will have a CommandLog table where you keep track of execution times.

The following query will help you get information from it, very useful for tracking backup failures:

USE DBA
SELECT
DatabaseName,
StartTime,
DATEDIFF(MINUTE,StartTime,EndTime) 'Duration in Min',
EndTime,
ErrorNumber,
ErrorMessage,
Command,
replace(
cast(SERVERPROPERTY('ErrorLogFileName') as nvarchar(1000))
,'errorlog','')
FROM DBO.COMMANDLOG
WHERE 1=1
--and StartTime > dateadd(dd,-1,getdate())
and CommandType in ('BACKUP_DATABASE','BACKUP_LOG','RESTORE_VERIFYONLY')
--and CommandType in ('xp_create_subdir','xp_delete_file')
--and CommandType in ('DBCC_CHECKDB')

--and ErrorNumber <> 0

order by ID desc

Below, we take advantage of window functions and we can see the last time a database had a DBCC checkdb done, along with its duration

 

    USE DBA
    
	;with Last_DBBC as
	(
	SELECT
    DatabaseName,
    StartTime,
    EndTime,
	ROW_NUMBER() over (PARTITION by databasename order by StartTime desc) 'rownumber'
    FROM DBO.COMMANDLOG
    WHERE 1=1
	and CommandType in ('DBCC_CHECKDB')
   	)
	select
	DatabaseName,
	StartTime, EndTime,
	DATEDIFF(MI,StartTime,EndTime) 'Duration in Min',
	DATEDIFF(DD,StartTime,GETDATE()) 'Age of DBCC in days'
	from Last_DBBC
	where 1=1
	and rownumber = 1

Leave a Reply

Your email address will not be published. Required fields are marked *