none
SQL Management Pack 6.6.2.0 broken Visualizations RRS feed

  • Question

  • Hi all,

    We updated our SQL Management packs last night to version 6.6.2.0 please see all SQL MPs we have installed along with versions below. the issue we are having is all the "newer" dashboard Visualizations aren't functioning. The only one that seems to throw an error is "Microsoft SQL Server > Summary" which throws:

    An error occurred executing the command: [Microsoft.SQLServer.Visualization.Library.DataProviders!DatacenterDashboardDataProvider/GetDatacenterDashboardData] in provider: [Microsoft.SQLServer.Visualization.Library.DataProviders.DatacenterDashboardDataProvider, Microsoft.SQLServer.Visualization.Library.DataProviders, Version=6.6.2.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35]. : The client has been disconnected from the server. Please call ManagementGroup.Reconnect() to reestablish the connection.
       at Microsoft.EnterpriseManagement.Presentation.DataAccess.DataProviderCommandMethod.Invoke(CoreDataGateway gateWay, DataCommand command)
       at Microsoft.EnterpriseManagement.Presentation.DataAccess.CoreDataGateway.ExecuteScalarInternal[TResult](DataCommand command)
       at Microsoft.EnterpriseManagement.Presentation.DataAccess.CoreDataGateway.<ExecuteScalarAsync>b__2[TResult](<>f__AnonymousType0`1 data)

    I'm assuming we are hitting some timeout returning the data from the DW because this message appears after 3 minutes exactly.

    MPs installed:

    Pretty sure i'm not missing any dependant packs...

    We are running SCOM 2012 R2 with UR7 (upped to UR7 to see if it fixed the issue, previously running UR5). Any help greatly appreciated.


    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember, if you see a post that helped you please click "Vote as Helpful", and if it answered your question, please click "Mark as Answer". I do not work for Microsoft, I manage a large estate in the public sector, my views are generally first hand production experiences.

    Friday, October 16, 2015 4:29 PM

Answers

  • if you run

    USE master
    GO
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    SELECT
    er.session_Id AS [Spid]
    , sp.ecid
    , er.start_time
    , DATEDIFF(SS,er.start_time,GETDATE()) as [Age Seconds]
    , sp.nt_username
    , er.status
    , er.wait_type
    , SUBSTRING (qt.text, (er.statement_start_offset/2) + 1,
    ((CASE WHEN er.statement_end_offset = -1
    THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
    ELSE er.statement_end_offset
    END - er.statement_start_offset)/2) + 1) AS [Individual Query]
    , qt.text AS [Parent Query]
    , sp.program_name
    , sp.Hostname
    , sp.nt_domain
    
    FROM sys.dm_exec_requests er
    INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
    CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
    WHERE session_Id > 50
    AND session_Id NOT IN (@@SPID)
    ORDER BY session_Id, ecid
    

    against your DW SQL Instance you will see a running transaction locking "Microsoft_SQLServer_Visualization_Library_Relationship_Hierarchy". As a bonus, this transaction will never commit and fill your DW DB log file until full, then rollback.

    SQL MPs 6.6.0.0 and 6.6.2.0 are broken.

    • Proposed as answer by stemo76 Tuesday, October 20, 2015 2:27 PM
    • Marked as answer by Microsoft Jim Wednesday, October 21, 2015 10:51 AM
    Tuesday, October 20, 2015 9:35 AM

All replies

  • Anyone? Think we will have to raise a PSS case for this...

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember, if you see a post that helped you please click "Vote as Helpful", and if it answered your question, please click "Mark as Answer". I do not work for Microsoft, I manage a large estate in the public sector, my views are generally first hand production experiences.

    Monday, October 19, 2015 8:12 AM
  • Anyone? Think we will have to raise a PSS case for this...

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember, if you see a post that helped you please click "Vote as Helpful", and if it answered your question, please click "Mark as Answer". I do not work for Microsoft, I manage a large estate in the public sector, my views are generally first hand production experiences.


    Just wait for new MP release.

    Vladimir Zelenov | http://systemcenter4all.wordpress.com

    Tuesday, October 20, 2015 8:24 AM
  • if you run

    USE master
    GO
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    SELECT
    er.session_Id AS [Spid]
    , sp.ecid
    , er.start_time
    , DATEDIFF(SS,er.start_time,GETDATE()) as [Age Seconds]
    , sp.nt_username
    , er.status
    , er.wait_type
    , SUBSTRING (qt.text, (er.statement_start_offset/2) + 1,
    ((CASE WHEN er.statement_end_offset = -1
    THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
    ELSE er.statement_end_offset
    END - er.statement_start_offset)/2) + 1) AS [Individual Query]
    , qt.text AS [Parent Query]
    , sp.program_name
    , sp.Hostname
    , sp.nt_domain
    
    FROM sys.dm_exec_requests er
    INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
    CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
    WHERE session_Id > 50
    AND session_Id NOT IN (@@SPID)
    ORDER BY session_Id, ecid
    

    against your DW SQL Instance you will see a running transaction locking "Microsoft_SQLServer_Visualization_Library_Relationship_Hierarchy". As a bonus, this transaction will never commit and fill your DW DB log file until full, then rollback.

    SQL MPs 6.6.0.0 and 6.6.2.0 are broken.

    • Proposed as answer by stemo76 Tuesday, October 20, 2015 2:27 PM
    • Marked as answer by Microsoft Jim Wednesday, October 21, 2015 10:51 AM
    Tuesday, October 20, 2015 9:35 AM
  • We saw the same or similar thing.  Our DW Log grew to 215gb and the views still don't work.

    BTW we are on 2012 R2 UR5, I heard some people say that they had better luck with 6.6.0 on UR7

    • Edited by stemo76 Tuesday, October 20, 2015 2:28 PM
    Tuesday, October 20, 2015 2:27 PM
  • add us to the 'me too' list.  The "SQL Summary" dashboard would sit there saying "loading" and not do anything.   The OperationsmanagerDW log then grew as fast as the disk backend could write to disk.. nice.

    "funny" thing is that this MP works perfectly in our test environment.

    SCOM 2012 SP1 UR5.

    Tuesday, October 20, 2015 3:58 PM
  • Looks like in your particular case you are hitting SCOM DW query limit 3 minutes.

    Can you try the following:

    On the management server create key “HKLM\SOFTWARE\Microsoft\Microsoft Operations Manager\3.0\Data Warehouse” and add REG_DWORD type value with name “Search Command Timeout Seconds”. The server will use your value instead of default 180 seconds.

    Tuesday, October 20, 2015 5:53 PM
  • There are also a big amount of "MSSQL xxxx: Discovery failed" alerts which look like false positive to me.

    Management Group: xxxx. Script: DiscoverSQL2008MirroredDB.vbs. Instance: xxxx: Mirrored DB discovery script 'DiscoverSQL2008MirroredDB.vbs' for instance 'xxxx' failed.

    ^ This is not a MirroredDB to start with and all the instance databases are properly monitored.

    Also DW logs filled up - environment is the latest UR7 / 2012 R2.

    Wednesday, October 21, 2015 6:28 AM
  • as the table "Microsoft_SQLServer_Visualization_Library_Relationship_Hierarchy" is locked by a never ending transaction, the wait time would have to be as long as it takes to fill your transaction log file and rollback.

    180 seconds should be enough under normal circumstances

    Wednesday, October 21, 2015 7:27 AM
  • Unfortunately the 3 minute timeout fix would not have been much use.

    given that it was filling the log at 100MB/s, it would have filled the log by 17.5GB in 3 minutes.  It would have done this every time someone clicks on the summary dashboard.  And we all know what people do if it doesn't work first time don't we.  Our DW translog would have filled in no time.

    I'm seriously glad I watch the translogs closely after adding MP's, as they could have autogrown and filled the translog LUN entirely.

    My recovery so far has been to delete as many of he SQL 6.6.2.0 MP's as possible before I try and get 6.6.0.0 back in.  However - looking around, people have had these same problems on 6.6.0.0 too.

    I'm a bit stuck now.

    There is a bitter irony that a SQL MP can trash the SQL DW.



    • Edited by Robuea Wednesday, October 21, 2015 8:42 AM
    Wednesday, October 21, 2015 8:36 AM
  • Tricky situation this one...

    We have a PSS case open which has gone over to the SQL team, lets see what they come back with. This seemed to work fine in our test environment but has destroyed production. The problem I now have is we have *LOTS* of monitoring which relies on these management packs, making removing them to install an older version next to impossible without taking out a lot of our monitoring.

    I'm a little bit tempted to see if PSS will increment the version number on one of the old MPs (5.5.1.0) and reseal it for us. at least until a newer pack is released.


    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember, if you see a post that helped you please click "Vote as Helpful", and if it answered your question, please click "Mark as Answer". I do not work for Microsoft, I manage a large estate in the public sector, my views are generally first hand production experiences.

    Wednesday, October 21, 2015 10:57 AM
  • we've re-added all the 6.6.2.0 SQL MP's, excluding the visualization and presentation MP's, because of dependencies elsewhere.

    We're also seeing lots of "Discovery Failed" critical alerts in SCOM, together with some DB's reporting 0% free space.  I've checked them manually and they're not full at all.

    Rather more worryingly, after a couple of hours post installation, the transactions logs on the OperationsManager database are now filling faster than before.  Nothing like the DW did, and I should be able to keep on top of it with translogs backups hopefully.

    Wednesday, October 21, 2015 12:41 PM
  • Please vote for this issue on Connect.
    Thursday, October 22, 2015 9:56 AM
  • http://blogs.msdn.com/b/sqlreleaseservices/archive/2015/10/16/released-system-center-management-pack-for-sql-server-and-replication-6-6-2-0.aspx?CommentPosted=true&PageIndex=2#comments

    UPDATE (10/22/2015): We have removed this release from Download Center for the time being. Large number of customers reported seeing an issue with tempdb and DW log growing above normal levels. We are working on a fix and will release an update as soon as we have it ready. If you have already installed 6.6.2.0 and seeing this issue in your environment, please run the script below to mitigate the problem. You should still update your environment when we have the new release available even if you run the workaround. We apologize for the trouble this has caused you.

    alter procedure sdk.Microsoft_SQLServer_Visualization_Library_UpdateHierarchy as RETURN 0;

    GO

    alter procedure sdk.Microsoft_SQLServer_Visualization_Library_UpdateLastValues as RETURN 0;

    GO

    update sdk.Microsoft_SQLServer_Visualization_Library_Tables set LastProcessedId = 9223372036854775807;

    Thursday, October 22, 2015 10:28 PM
  • New version released (6.6.3.0), _should_ fix this problem:

    http://www.microsoft.com/en-us/download/details.aspx?id=10631

    Cheers,
    Patrick


    Please remember to click 'Mark as Answer' on the post that helped you.

    Patrick Seidl (System Center and Private Cloud)
    s2 - seidl solutions
    Blog: http://www.systemcenterrocks.com

    Thursday, November 12, 2015 1:55 PM
  • I got excited for a moment, but the link you reference goes to version 6.6.0.

    *edit*, must have been something in cache, I see 6.6.3 now.  {crossing fingers}.  I may wait for more to blow up there test environments before attempting.

    • Edited by B. Wright Thursday, November 12, 2015 2:09 PM correction
    Thursday, November 12, 2015 2:06 PM
  • Installed 6.6.3 into our test environment and we are still seeing the failed discovery issue. The dashboards work and we are monitoring the t-logs for any problems but this MP is still faulty.
    Thursday, November 12, 2015 3:03 PM
  • Installed 6.6.3 into our test environment and we are still seeing the failed discovery issue. The dashboards work and we are monitoring the t-logs for any problems but this MP is still faulty.
    Brian, could you please open a connect issue https://connect.microsoft.com/WindowsServer/SC_Public with that issue so you can be contacted. In 6.6.2 there was an improvement in the error handling. There were situations when the script was failing but keep silence, so <g class="gr_ gr_60 gr-alert gr_gramm undefined Grammar only-ins replaceWithoutSep" data-gr-id="60" id="60">problem</g> was not reported to SCOM. With the new error handling, it reports about a script problem in such case. The recommendation is to check the Run AS configuration for the failing workflows.
    Friday, November 13, 2015 1:20 AM
  • Hahaha, Aleksandr... that  was a good one. Is anybody listening on connect or are they 'just' not responding? Tired of posting there...

    /patrick


    Please remember to click 'Mark as Answer' on the post that helped you.

    Patrick Seidl (System Center and Private Cloud)
    s2 - seidl solutions
    Blog: http://www.systemcenterrocks.com

    Friday, November 13, 2015 7:32 AM
  • I have 6.6.4 running and have same loading errors.

    Anybody a solution?

    Friday, January 15, 2016 2:50 PM
  • An error occurred executing the command: [Microsoft.SQLServer.Visualization.Library.DataProviders!DatacenterDashboardDataProvider/GetDatacenterDashboardData] in provider: [Microsoft.SQLServer.Visualization.Library.DataProviders.DatacenterDashboardDataProvider, Microsoft.SQLServer.Visualization.Library.DataProviders, Version=6.6.4.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35]. : The client has been disconnected from the server. Please call ManagementGroup.Reconnect() to reestablish the connection.
       at Microsoft.EnterpriseManagement.Presentation.DataAccess.DataProviderCommandMethod.Invoke(CoreDataGateway gateWay, DataCommand command)
       at Microsoft.EnterpriseManagement.Presentation.DataAccess.CoreDataGateway.ExecuteScalarInternal[TResult](DataCommand command)
    Friday, January 15, 2016 2:57 PM
  • Other parts of the MP give similar error

    Please provide the following information to the support engineer if you have to contact Microsoft Help and Support :

    Microsoft.EnterpriseManagement.Presentation.DataAccess.DataAccessDataNotFoundException: Exception reading objects ---> Microsoft.EnterpriseManagement.Common.ServerDisconnectedException: The client has been disconnected from the server. Please call ManagementGroup.Reconnect() to reestablish the connection. ---> System.ServiceModel.CommunicationObjectFaultedException: The communication object, System.ServiceModel.Channels.ServiceChannel, cannot be used for communication because it is in the Faulted state.

    Server stack trace:
       at System.ServiceModel.Channels.CommunicationObject.ThrowIfDisposedOrNotOpen()
       at System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs, TimeSpan timeout)
       at System.ServiceModel.Channels.ServiceChannelProxy.InvokeService(IMethodCallMessage methodCall, ProxyOperationRuntime operation)
       at System.ServiceModel.Channels.ServiceChannelProxy.Invoke(IMessage message)

    Exception rethrown at [0]:
       at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
       at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
       at Microsoft.EnterpriseManagement.Common.Internal.IDispatcherService.DispatchUnknownMessage(Message message)
       at Microsoft.EnterpriseManagement.Common.Internal.EntityObjectsServiceProxy.GetManagedEntitiesByGenericCriteriaAndClassWithInstanceQueryOptions(String criteria, Nullable`1 classId, String languageCode, InstanceQueryOptions instanceQueryOptions)
       --- End of inner exception stack trace ---
       at Microsoft.EnterpriseManagement.Common.Internal.ExceptionHandlers.HandleChannelExceptions(Exception ex)
       at Microsoft.EnterpriseManagement.Common.Internal.EntityObjectsServiceProxy.HandleChannelExceptions(String methodName, Exception ex)
       at Microsoft.EnterpriseManagement.Common.Internal.EntityObjectsServiceProxy.GetManagedEntitiesByGenericCriteriaAndClassWithInstanceQueryOptions(String criteria, Nullable`1 classId, String languageCode, InstanceQueryOptions instanceQueryOptions)
       at Microsoft.EnterpriseManagement.InstancesManagement.GetObjectReader[T](EnterpriseManagementObjectGenericCriteria criteria, ManagementPackClass mpClass, ObjectQueryOptions queryOptions)
       at Microsoft.EnterpriseManagement.MonitoringInstancesManagement.GetObjectReader[T](ManagementPackClass managementPackClass, ObjectQueryOptions objectQueryOptions)
       at Microsoft.EnterpriseManagement.Management.DataProviders.ManagedEntityProvider.GetManagedEntitiesByClass(ICollection`1 baseTypeNames, String criteriaString, List`1 valueDefinitions, List`1 sortValueDefinitions, Int32 maxEntities, String typePropertyName, String typeWithIconPropertyName, Boolean propertyCollectionRequested)
       --- End of inner exception stack trace ---
       at Microsoft.EnterpriseManagement.Presentation.DataAccess.DataProviderCommandMethodInvoker.Invoke()
       at Microsoft.EnterpriseManagement.Monitoring.DataProviders.RetryCommandExecutionStrategy.Invoke(IDataProviderCommandMethodInvoker invoker)
       at Microsoft.EnterpriseManagement.Presentation.DataAccess.DataProviderCommandMethod.Invoke(CoreDataGateway gateWay, DataCommand command)
       at Microsoft.EnterpriseManagement.Presentation.DataAccess.CoreDataGateway.ExecuteInternal[TResult](DataCommand command)
       at Microsoft.EnterpriseManagement.Presentation.DataAccess.CoreDataGateway.<ExecuteAsync>b__0[TResult](<>f__AnonymousType0`1 data)

    Friday, January 15, 2016 3:02 PM