Post reply

SQL Server 2005 Performance Dashboard Failure - How To Resolve Without Restarting SQL Server?

  • January 28, 2010 at 3:22 pm

    #137623

    I've received this error before, when I attempted to start SQL Server 2005 Performance Dashboard:

    "Error:

    Difference of two datetime columns caused overflow at runtime."

    Today, the error has occurred on a 7x24 server that is not due for maintenance for another month so I can't shutdown and restart the SQL Server service to fix the problem.

    Please offer suggestions if you can, or think you can, solve the problem.

    Thanks,

    LC

  • January 28, 2010 at 3:31 pm

    #1110389

    In the comments section of

    http://blogs.msdn.com/sqlrem/archive/2007/03/07/Performance-Dashboard-Reports-Now-Available.aspx

    There is an explanation of how to resolve this. The cause is due to a stored proc and the date calculation.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • January 28, 2010 at 6:50 pm

    #1110413

    I searched in the Performance Dashboard folder on our server, found the file "Setup.sql", opened it in Management Studio, then searched for "DATEDIFF", found it at line 276, replaced it with the recommended code:

    "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,"

    Then (and this is important), I executed the entire script in the file before attempting to reopen Performance Dashboard. Worked perfectly.

    Thanks, Jason, and thanks to the person who went to the trouble to fix this problem.

    LC

    P.S. Be sure to save the modifications to the "Setup.sql" file. You'll need them in the future.

  • January 29, 2010 at 1:37 am

    #1110536

    You're welcome.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply