• Home
  • Contact

John Sansom

SQL Server DBA Blog

  • About
    • The Blog
    • John Sansom
    • Contact
  • Ebook
  • Archives
    • Start Here
    • Popular Posts
    • All Posts
    • SFTW
  • Becoming a DBA
    • What it’s Really Like
    • Interview Tips
    • Certification
    • FAQ
  • Books
  • Resources
    • Blog Tools and Technology
    • UK Events Schedule
    • References & Resource Lists
  • Subscribe

Performance Dashboard Report – Datatype Overflow Error

January 14, 2009 by John Sansom Leave a Comment

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,




Filed Under: Reporting Services, SQL Server Tips

About John Sansom

I’m a Microsoft Certified Master(MCM) of SQL Server. I’ve been working with database technology in a variety of flavors for over fifteen years. I absolutely love what I do and genuinely feel privileged to be a part of our tremendous technology community. Got a question about SQL Server or being a DBA? Ask me!

Popular Articles

  • Top 10 Free SQL Server Tools
  • Performing fast SQL Server delete operations
  • How to identify the most costly SQL Server queries using DMV’s
  • Top 10 Junior DBA Interview Tips
  • The Database Administrator’s Primary Responsibility
  • Your Road to Becoming a DBA: Laying a Strong Foundation
  • Top 5 SQL Forums
  • SQL Server Memory Configuration, Determining MemToLeave Settings
  • Script SQL Server Agent Jobs Using PowerShell
  • Using sys.dm_os_ring_buffers to Troubleshoot Connectivity Issues

Categories

  • Administration (38)
  • Blogging (8)
  • Customer Service (5)
  • Disaster Recovery (5)
  • DMVs (4)
  • Index Optimisation (6)
  • Interviews (1)
  • Link Posts (243)
  • Memory (2)
  • Performance Tuning (15)
  • Professional Development (70)
  • Reporting Services (5)
  • Reviews (1)
  • SQL Server Community (144)
  • SQL Server Tips (11)
  • SQLServerCentral Syndication (112)
  • SQLServerPedia Syndication (116)
  • Tools (7)

Copyright © 2023 · Santech Solutions Limited · Powered by the Genesis Framework · Privacy Policy