Mar 4, 2008

Fix: "Difference of two datetime columns caused overflow at runtime" in Performance Dashboard.

Error: Difference of two datetime columns caused overflow at runtime
Solution: to modify msdb.MS_PerfDashboard.usp_Main_GetSessionInfo

sum(convert(bigint, datediff(ms, login_time, getdate()))) - sum(convert(bigint, s.total_elapsed_time)) as idle_connection_time,

to:

sum(convert(bigint, CAST ( DATEDIFF ( minute, login_time, getdate()) AS BIGINT)*60000 + DATEDIFF ( millisecond, DATEADD ( minute, DATEDIFF ( minute, login_time, getdate() ), login_time ),getdate() ))) - sum(convert(bigint, s.total_elapsed_time)) as idle_connection_time,