none
Exception 'SqlException': A network-related or instance-specific error occurred while establishing a connection to SQL Server

    Question

  • Hi All,

    Am having these errors appearing since the database was full.After enabling auto grow.We SCOM 2007 R2 installed with SQL Server 2008 R2.These errors are still present.Searched the web and never found this specific error

    Exception 'SqlException': A network-related or instance-specific error occurred while establishing a connection to SQL Server

    These are the event IDs that appear in the log.DBA has checked connectivity to the server and can view an active connection by the SCOM Account

    Event ID 31551

    Failed to store data in the Data Warehouse. The operation will be retried.

    Exception 'SqlException': A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

    One or more workflows were affected by this.

    Workflow name: Microsoft.SystemCenter.DataWarehouse.CollectPerformanceData

    Instance name:

    Instance ID: {D89A46AC-7B20-0C41-B76B-51866300E414}

    Management group:

    Event ID 8000

    followed by these as well

    A subscriber data source in management group Australia Post has posted items to the workflow, but has not received a response in 122 minutes. Data will be queued to disk until a response has been received. This indicates a performance or functional problem with the workflow.

    Workflow Id : Microsoft.SystemCenter.DataWarehouse.CollectEventData

    Instance :

    Event ID 2115

    Instance Id : {D89A46AC-7B20-0C41-B76B-51866300E414}

    A Bind Data Source in Management Group Australia Post has posted items to the workflow, but has not received a response in 7140 seconds. This indicates a performance or functional problem with the workflow.

    Workflow Id : Microsoft.SystemCenter.DataWarehouse.CollectEventData

    Instance :

    Instance Id : {D89A46AC-7B20-0C41-B76B-51866300E414}

    Thanks in advance

    Friday, September 02, 2011 6:17 AM

Answers

  • Hi Nicholas,

    As it turns out,once the Windows firewall was switched off.All the errors disappeared.And am receiving alerts into the Database.

    I have another issue,i get these alerts

    Data was written to the Data Warehouse staging area but processing failed on one of the subsequent operations.

    Exception 'SqlNullValueException': Data is Null. This method or property cannot be called on Null values.

    One or more workflows were affected by this.

    Workflow name: Microsoft.SystemCenter.DataWarehouse.CollectPerformanceData

    Instance name: Instance ID: {D89A46AC-7B20-0C41-B76B-51866300E414}

    Management group:

    Should i create a new thread for this ?

    James

    • Marked as answer by James Theseira Friday, September 16, 2011 2:02 AM
    Thursday, September 08, 2011 4:42 AM

All replies

  • Hi James

     

    Does this help?

    http://blogs.technet.com/b/sudheesn/archive/2009/07/28/getting-31552-exception-sqlexception-timeout-expired-very-frequently-in-scom-server.aspx

     

    Greetz Roelkn


    www.win4e.nl @Roelkn
    Friday, September 02, 2011 6:32 AM
  • Hi Greetz,

    Looked up that page and it does not apply in my situation.there isn't even a high count of Alerts (For Example).The alert count is 0.Which is a bit strange.

    James

    Monday, September 05, 2011 4:44 AM
  •  

    Hi,

     

    Regarding the issue, please also try the methods in the following posts:

     

    Some Server 2008 Windows Firewall rules needed to install OpsMgr R2

    http://blogs.technet.com/b/kevinholman/archive/2009/06/02/some-server-2008-windows-firewall-rules-needed-to-install-opsmgr-r2.aspx

     

    EventID 31552: Failed to store data in the Data Warehouse. Exception 'SqlException': A network-related or instance-specific error occurred while establishing a connection to SQL Server

    http://thoughtsonopsmgr.blogspot.com/2011/03/eventid-31552-failed-to-store-data-in.html

    Please Note: Since the website is not hosted by Microsoft, the link may change without notice. Microsoft does not guarantee the accuracy of this information.

     

    Repost: EventID 31552

    http://thoughtsonopsmgr.blogspot.com/2009/08/eventid-31552.html

    Please Note: Since the website is not hosted by Microsoft, the link may change without notice. Microsoft does not guarantee the accuracy of this information.

     

    Event ID 2115 is logged, and a management server generates an "unable to write data to the Data Warehouse" alert in System Center Operations Manager 2007

    http://support.microsoft.com/default.aspx?scid=kb;EN-US;945946

     

    Hope this helps.

     

    Thanks.


    Nicholas Li - MSFT
    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Monday, September 05, 2011 8:55 AM
  • Hi Nicholas,

    Have tried all the above but still no joy.

    Have executed this article by Kevin Holman in regards to the event id 2115.

    http://blogs.technet.com/b/kevinholman/archive/2008/04/21/event-id-2115-a-bind-data-source-in-management-group.aspx

    I somehow think the management pack is not able to communicate with the SCOM database.

    Failed to store data in the Data Warehouse.

    Exception 'SqlException': A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

    One or more workflows were affected by this.

    Workflow name: Microsoft.SystemCenter.DataWarehouse.StandardDataSetMaintenance

    Instance name: Performance data set

    Instance ID: {4AB646A7-7C1B-000B-5EB0-14E82A6603FF}

    Notice how it says it can't find the server or instance.My SCOM UI is up and running well.Only thing here is that the default port 1433 was changed to 4102.Not sure if there would be any impact for Management pack defined objects.

    Also reviewed the SCOM Firewall requirements.

    http://technet.microsoft.com/en-us/library/cc540431.aspx

    Just also wondering if changing the port from 1433 to 4102 is supported by Microsoft.

    Regards

    James

    Tuesday, September 06, 2011 6:25 AM
  •  

    Hi,

     

    I have checked the document and found the some ports are configurable. I think you can configure the port according to it; and also ensure the configurations are correct.

     

    Thanks.
    Nicholas Li - MSFT
    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Tuesday, September 06, 2011 8:17 AM
  • Hi Nicholas,

    As it turns out,once the Windows firewall was switched off.All the errors disappeared.And am receiving alerts into the Database.

    I have another issue,i get these alerts

    Data was written to the Data Warehouse staging area but processing failed on one of the subsequent operations.

    Exception 'SqlNullValueException': Data is Null. This method or property cannot be called on Null values.

    One or more workflows were affected by this.

    Workflow name: Microsoft.SystemCenter.DataWarehouse.CollectPerformanceData

    Instance name: Instance ID: {D89A46AC-7B20-0C41-B76B-51866300E414}

    Management group:

    Should i create a new thread for this ?

    James

    • Marked as answer by James Theseira Friday, September 16, 2011 2:02 AM
    Thursday, September 08, 2011 4:42 AM
  •  

    Hi,

     

    Since the issue disappeared after disabling the Windows Firewall, please adjust the firewall settings and then enable the firewall to protect the system.

     

    At this time, please check if you can get the Instance name by executing the following query against the OperationsManager database:

     

    SELECT * from BaseManagedEntity where BaseManagedEntityId like‘% D89A46AC-7B20-0C41-B76B-51866300E414%’

     

    Thanks.
    Nicholas Li - MSFT
    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Friday, September 09, 2011 10:10 AM
  • Hi Nicholas,

    I can get the Instance Name.However,what i am seeing is the

    State.StateStage table is growing and growing.

    Am seeing this in the Alerts as well

    Data Warehouse event data writer process failed to perform maintenance operation. Data was written to the Data Warehouse staging area but processing failed on one of the subsequent operations.
    Exception 'SqlNullValueException': Data is Null. This method or property cannot be called on Null values.

    Workflow name: Microsoft.SystemCenter.DataWarehouse.CollectEventData

    Instance name: SCOMServer

    Instance ID: {D89A46AC-7B20-0C41-B76B-51866300E414}

    Management group:

    The only Null value that i see in the StateStage table is ManagedEntityMonitorRowId which is NUll

    Thanks

    James

    Wednesday, September 14, 2011 1:24 AM
  • Hi nicholas,

    With the 31553 error.after disabling the affected Rules.Clearing out the State.StateStage table and enabling the affected rules.It appears that the workflow is now flowing through.this may have been the result of enabling the rules when the firewall was blocked.Setting this to resolved.

    Thanks everyone for input and suggestions.

    James

    Friday, September 16, 2011 2:01 AM