FIX – Sql Server 2005 Performance Dashboard Reports

I Love the Sql Server 2005 Performance Dashboard Reports, but there is a slight bug that gets me from time to on systems with long uptimes.  Below Essa Mughal has the fix!

PERMALINK: http://www.tsql.ca/Default.aspx?tabid=633&EntryID=24

Posted by: Essa Mughal 7/19/2007 10:05 AM
 

Those who are using Performance Dashboard Custom Reports, they might get that error if they have not updated there script.

Difference of two datetime columns caused overflow at runtime.

Reason:
There is a function used in the script.sql, since DATEDIFF returns and int once you have connection that is more than 24 days or so old it will overflow the dattype if you modify the procedure so caluclates the differnce in minutes first converts this to milliseconds then add the number of minutes diffrence onto the start time and then calculate the remianing number of milli seconds

Solution:
The fix for that error is to update script.sql file residing into Performance Dashboard folder

C:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard

Update the following line

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

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,

Run the script again into MSDB database of SQL Server and after that it will run fine.

Thanks

Copyright ©2007 Essa Mughal

Tiny URL for this post:
 

Share the joy

Comments are closed.