Performance Dashboard Report – Datatype Overflow Error

When you try to execute the Performance Dashboard Reports for the first time, you may be presented with an error of the form:

“the difference of two datetime columns caused overflow at runtime”

This is because the DATEDIFF function returns an int value and once you have a connection that is more than 24 days or so old it will overflow the data type.

In order to work around this issue simply substitute the code from the stored procedure MS_PerfDashboard.usp_Main_GetSessionInfo (most likely located in the MSDB database) below:

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

And replace it with:

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,