SQL Server 2005 Performance Dashboard Report Error ‘Difference of two datetime columns caused overflow at runtime’


Recently I was deploying SQL Server 2005 performance dashboard for a SQL Server 2005 Instance and after I ran Setup.SQL I tried to pull the report using performance_dashboard_man.rdl and instantly got an error ‘Difference of two date time columns caused overflow at runtime’.

It was clear that there is issue with DATE conversion and after some research found that a workaround was already in place for the issue. I would like to share this to all my friends -

Workaround -

Open the Setup.SQL script (which is installed along with the performance dashboard – you’ll find it at C:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard\). Find line 276, which says:

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

and replace this line 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,

This workaround will work for you.

SQL Writer Error – ‘sysdatabases in SQL server instance is empty.’


You might have noticed an error in the application log for some server with in your environment which says sysdatabases in SQL server instance <Instance Name> is empty.

Cause

 

This is a SQLWriter error when we have a VSS based backup solution implemented.The backup solution is using SQL Server VSS Writer Service to connect to SQL and this account doesn’t have sysadmin rights or not part of logins.In most of the cases SQL Server VSS Writer Service will be running on Local System account.

Solution to clear the error message

 

We have to add back NT AUTHORITY\SYSTEM if it was removed and grant sysadmin rights incase Writer Service is using Local System account.

I am curious to understand if any one of you are facing this issue.

System Center Advisor(Earlier Microsoft code name Atlanta)


System center advisor is a cloud based service offered by Microsoft.This service helps to understand if our SQL 2008 or 2008 R2 servers are configured correctly.

Servers can be registered and data will be processed in cloud and retrived for review.Potential errors and warnings are generated and this report will help DBA to proactively resolve issues.

This closely matches BPA,however we can expect more advanced features with SCA.

Automatic Page Repair,works behind the scene and a wonderful friend of DBA


In a SQL Server 2008 mirroring environment when the primary server detected a corrupt page on disk,it asks the mirror to provide the copy of the page and then correct the damage.

This works behind the scene and no intervention is required.

DMV sys.dm_db_mirroring_auto_page_repair can be used to detect any repair attempts which have been made.