Custom with a large environment (7000+) agents was having issues with the SQL Dashboards not populating. We checked the event log and came across this error.
Log Name: Operations Manager
Source: OpsMgr SDK Service
Event ID: 26319
Task Category: None
Level: Error
Keywords: Classic
User: N/A
Description:
An exception was thrown while processing GetDataWarehouseStoredProcedureResult for session ID
Exception message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Timed out stored procedure: sdk.Microsoft_SQLServer_Visualization_Library_GetDataCenterDashboardData
Full Exception: Microsoft.EnterpriseManagement.DataWarehouse.DataAccess.SqlTimeoutException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Timed out stored procedure: sdk.Microsoft_SQLServer_Visualization_Library_GetDataCenterDashboardData —> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. —> System.ComponentModel.Win32Exception: The wait operation timed out
The fix:
When used rarely or after a long break, the dashboards may work rather slowly due to large amounts of the collected data to be processed; especially, it is related to large environments (2000+ objects).
Run this script in SQL against your DataWarehouse
USE [OperationsManagerDW] EXECUTE [sdk].[Microsoft_SQLServer_Visualization_Library_UpdateLastValues] EXECUTE [sdk].[Microsoft_SQLServer_Visualization_Library_UpdateHierarchy]
It’s a good post; thank you, Tim.
You’re absolutely right about the cause of such errors. I just want to add that there is a rule in SQL Server Dashboards MP which can help to avoid the timeout errors—“DW data early aggregation”. In the last blog post on releasing SQL MP 6.7.20.0, we described this rule and how to configure it (see “A Note About Dashboard Performance”): https://blogs.msdn.microsoft.com/sqlreleaseservices/released-system-center-management-pack-for-sql-server-and-dashboards-6-7-20-0/.