{"id":72,"date":"2008-12-04T14:51:17","date_gmt":"2008-12-04T19:51:17","guid":{"rendered":"https:\/\/jackdonnell.com\/?p=72"},"modified":"2008-12-04T14:51:17","modified_gmt":"2008-12-04T19:51:17","slug":"fix-sql-server-2005-performance-dashboard-reports","status":"publish","type":"post","link":"https:\/\/jackdonnell.com\/?p=72","title":{"rendered":"FIX – Sql Server 2005 Performance Dashboard Reports"},"content":{"rendered":"

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

PERMALINK: http:\/\/www.tsql.ca\/Default.aspx?tabid=633&EntryID=24<\/a><\/p>\n\n\n\n\n\n
Posted by:<\/span>\u00a0Essa Mughal<\/span><\/td>\n7\/19\/2007 10:05 AM<\/span><\/td>\n<\/tr>\n
\u00a0<\/p>\n

Those who are using Performance Dashboard Custom Reports, they might get that error if they have not updated there script.<\/p>\n

Difference of two datetime columns caused overflow at runtime.<\/strong><\/p>\n

Reason:
\n<\/strong>There is a function used in the script.sql,\u00a0since 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
\n<\/p>\n

Solution:
\n<\/strong>The fix for that error is to update script.sql file residing into Performance Dashboard folder<\/p>\n

C:\\Program Files\\Microsoft SQL Server\\90\\Tools\\PerformanceDashboard<\/p>\n

Update the following line<\/p>\n

sum(convert(bigint, datediff(ms, login_time, getdate()))) – sum(convert(bigint, s.total_elapsed_time)) as idle_connection_time,<\/strong><\/p>\n

with<\/p>\n

sum(convert(bigint, CAST ( DATEDIFF ( minute, login_time, getdate()) AS\u00a0
\n<\/strong>\u00a0\u00a0\u00a0BIGINT)*60000 + DATEDIFF ( millisecond, DATEADD ( minute,
\n<\/strong>\u00a0\u00a0\u00a0DATEDIFF ( minute, login_time, getdate() ), login_time ),getdate() ))) – sum(convert(bigint, s.total_elapsed_time)) as idle_connection_time,<\/strong><\/p>\n

Run the script again into MSDB database of SQL Server and after that it will run fine.<\/p>\n

Thanks<\/p>\n

<\/span><\/td>\n<\/tr>\n

Copyright \u00a92007 Essa Mughal<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n","protected":false},"excerpt":{"rendered":"

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.\u00a0 Below Essa Mughal has the fix! PERMALINK: http:\/\/www.tsql.ca\/Default.aspx?tabid=633&EntryID=24 Posted by:\u00a0Essa Mughal 7\/19\/2007 10:05 AM …<\/p>\n

Read more »<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"footnotes":""},"categories":[77],"tags":[],"class_list":["post-72","post","type-post","status-publish","format-standard","hentry","category-fromdigg"],"_links":{"self":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/72","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=72"}],"version-history":[{"count":1,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/72\/revisions"}],"predecessor-version":[{"id":73,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/72\/revisions\/73"}],"wp:attachment":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=72"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=72"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=72"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}