View for Ola Hallengren CommandLog

,

Runs on top of OlaHallengren's Logging table.

Create this view, and change the path to the commandlog table if needed.

A common query for me is

  select * from vcommandlog where Fragmentation > 25 and cast(starttime as date) = cast(getdate() as date)

  go

  select * from vcommandlog where DurationInSeconds > 60 and cast(starttime as date) = cast(getdate() as date)

  go

create view [dbo].[VCommandLog] as
SELECT [ID],[DatabaseName],[SchemaName],[ObjectName]
      ,[ObjectType],[IndexName],[IndexType],[StatisticsName]
      ,[PartitionNumber],[ExtendedInfo],[Command]
      ,[CommandType],[StartTime],[EndTime]
      ,[ErrorNumber],[ErrorMessage]
	  ,datediff(second,startTime,EndTime)  [DurationInSeconds]
	  ,ExtendedInfo.value('(/ExtendedInfo/PageCount)[1]','bigint') as [pagecount]
	  ,ExtendedInfo.value('(/ExtendedInfo/Fragmentation)[1]','numeric(7,5)') as [Fragmentation]
  FROM [dbo].[CommandLog] 
GO
--Then use like so
  select * from vcommandlog where Fragmentation > 25 and cast(starttime as date) = cast(getdate() as date)
  go
  select * from vcommandlog where DurationInSeconds > 60 and cast(starttime as date) = cast(getdate() as date)
  go

Rate

2.83 (6)

Share

Share

Rate

2.83 (6)