1

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.

1 Answer 1

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.