Is there any way we can generate report after running IndexOptimize – USER_DATABASES jobs.
I need a report which tells me which indexes have been rebuild/re-organised.
Select * from commandlog
I had a look in the above table, but I am unable to identify which indexes has this actually performed rebuild/re-organize. Even though there is a command logged in the table, there are always few tables which didn't get rebuild/reorganized for different reasons like pagecounts etc..
Does anyone had any requirement like this. Can anyone share any script with me please.
Best Answer
You can find answer for this question at Ola Hallengren website itself. He has put lot of effort to make DBA's life easier and provided lot of parameters in order to customize and run the script as per user's requirement.
SELECT DatabaseName, SchemaName, ObjectName, CASE WHEN ObjectType = 'U' THEN 'USER_TABLE' WHEN ObjectType = 'V' THEN 'VIEW' END AS ObjectType, IndexName, CASE WHEN IndexType = 1 THEN 'CLUSTERED' WHEN IndexType = 2 THEN 'NONCLUSTERED' WHEN IndexType = 3 THEN 'XML' WHEN IndexType = 4 THEN 'SPATIAL' END AS IndexType, PartitionNumber, ExtendedInfo.value('(ExtendedInfo/PageCount)[1]','int') AS [PageCount], ExtendedInfo.value('(ExtendedInfo/Fragmentation)[1]','float') AS Fragmentation, CommandType, Command, StartTime, EndTime, CASE WHEN DATEDIFF(ss,StartTime, EndTime)/(24*3600) > 0 THEN CAST(DATEDIFF(ss,StartTime, EndTime)/(24*3600) AS nvarchar) + '.' ELSE '' END + RIGHT(CONVERT(nvarchar,EndTime - StartTime,121),12) AS Duration, ErrorNumber, ErrorMessage FROM dbo.CommandLog WHERE CommandType = 'ALTER_INDEX' ORDER BY StartTime ASC