Check report are rendering more than 8 sec

WITH RankedReports




AS


(


SELECT

ReportID,

TimeStart,

TimeEnd,

UserName,

RANK() OVER(PARTITION BY ReportID ORDER BY TimeStart DESC) AS iRank

FROM dbo.ExecutionLog t1

JOIN dbo.Catalog t2

ON t1.ReportID = t2.ItemID




)



SELECT

t2.Name AS ReportName,

t1.TimeStart,

t1.TimeEnd,

DATEDIFF(ss, t1.TimeStart,t1.TimeEnd) as [time],

t1.UserName,

t2.Path,

t1.ReportID

FROM RankedReports t1

JOIN dbo.Catalog t2

ON t1.ReportID = t2.ItemID

WHERE t1.iRank = 1

AND DATEDIFF(ss, t1.TimeStart,t1.TimeEnd)>=8

ORDER BY t1.TimeStart

Delete duplicate records

;WITH NEW

AS




(


SELECT *,ROW_NUMBER() OVER(PARTITION BY column1 ORDER BY column2 DESC) AS [Rank] FROM tablename




)


DELETE FROM NEW WHERE [Rank]=2

Reduce the log file size

Set DB recovery mode to simple and apply below query

DBCC SHRINKFILE (Rtest_log, 1)