Skip to main content
Delphix

Detecting Memory Leak in RemoteServices for SQL Server Target Environments (KBA8070)

 

KBA

KBA# 8070

 

Issue

In the 6.0.2.0 release of the Delphix Virtualization Engine a new feature was added allowing the use of Windows Authentication to access source environments when checking for available backups among some environmental queries:

  • Windows Authentication for SQL Server:
    You will now be able to use Windows Authentication to link SQL Server databases. Rather than providing both a database user and a Windows user to ingest data, you can leverage one set of credentials (a Windows OS user) to perform all source operations. This capability will simplify SQL Server deployments and reduce Delphix’s security requirements on source databases.

This new capability would use a JDBC connection from the source environment's "Connector Host", that which has DelphixConnector installed, to issue SQL queries to obtain the backup set history of the source backups used for the SQL Server dSource ingestions during sync operation (maunal sync, validated sync). An example of the Connector Host is seen here from a Windows source environment:

ConnectorHost.png

This SQL Server JDBC driver is controlled by java remote services from the engine - communicating to the Connector Host to provide the SQL commands the JDBC driver will execute, which in turn collects results and presents back to the Delphix engine.  This information is used to determine next available backups and some environmental aspects of the source database (size and properties such as recovery model).

It was discovered in some environments the dSources started to throw connection faults even though there were no known network or other connectivity issues. This would happen to any dSource configured to the Connector Host, which usually provided the staging databases for the source databases and the result was stalls in the validated syncs, a common configuration for dSources.

 

 

The Fault

An example of such a fault looked like this:


    Object ID                       FAULT-7316
    Fault Id                        7316
    Target                          MSSQL_LINKED_SOURCE-56
    Target Name                     TEST_DSRC
    Bundle Id                       fault.mssql.dSource.validated_sync.failed
    Params                          TEST_DSRC
                                    Failed to query source database "master" over JDBC.
                                    Command output: SQLState: 08S01 SQLErrorCode: 0 ErrorMessage: The TCP/IP connection to the host sourcehost.acme.com, port 1433 has failed. Error: "null. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".
                                    Make sure the database is accepting remote connections over TCP, the database is not using a dynamic TCP port, and the SQL login "WINDOMAIN\OS_USER" has privileges to connect to the source database and has required permissions. Then refresh the environment and try again.
    Title                           Validated sync for the dSource failed
    Description                     Validated sync for dSource "TEST_DSRC" failed with the error: Failed to query source database "master" over JDBC.Command output: SQLState: 08S01 SQLErrorCode: 0 ErrorMessage: The TCP/IP connection to the host sourcehost.acme.com, port 1433 has failed. Error: "null. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".
    Response
    Action                          Review the error. Additional recommendation(s): Make sure the database is accepting remote connections over TCP, the database is not using a dynamic TCP port, and the SQL login "WINDOMAIN\OS_USER" has privileges to connect to the source database and has required permissions. Then refresh the environment and try again..
    Severity                        CRITICAL
    Status                          ACTIVE
    Date Diagnosed                  2021-08-26 16:03:18 UTC

After going through the recommended actions, the actions were checked and yet the faults remained. This was critical as it stalled the advance of ingestions necessary to keep the synchronization of the source database with the staging database intact, preventing the ability to provision VDBs with timely data from the source environment.

The issue is caused by a memory leak in the non-DelphixConnector java.exe process running on the Connector Host that manages the JDBC connections to the source environment, being controlled by the Delphix engine. In some cases, in the DelphixConnector installation (default is C:\Program Files\Delphix\DelphixConnector) a very large, on the order of GBs, HPROF file is found. This is the heap dump of the memory of the java-based process and usually has a name like "<PID>.hprof" or "connector.hprof"where PID is the process id of the java process.

You can also check java process (See Troubleshooting section) with task manager to check how much memory it is using - it in the GB range you are likely experiencing this memory leak issue.

Prerequisites

  • Delphix Virtualization Engine 6.0.2.0 or higher up to 6.0.8.1 (inclusive)
  • Fault indicating connection issue with SQL Server message starting: SQLState: 08S01
    • This is the SQLState seen in all known issues
  • Validated Sync configured for dSources
    • It is conceivable that manual syncs could cause this but it would require a lot of them in repeated fashion to cause the issue.

 

Applicable Delphix Versions

Click here to view the versions of the Delphix engine to which this article applies
Major Release All Sub Releases
6.0 6.0.2.0, 6.0.2.1, 6.0.3.0, 6.0.3.1, 6.0.4.0, 6.0.4.1, 6.0.4.2, 6.0.5.0, 6.0.6.0, 6.0.6.1, 6.0.7.0, 6.0.8.0, 6.0.8.1, 6.0.9.0

Resolution

There is a fix in the 6.0.9.0 Delphix Virtualization Engine release.

Please be aware that you follow this resolution path IF you have already ruled out and actual and real connectivity issue (port blocks, incorrect address, DNS, etc.). 

For workarounds there are two that are known to provide temporary relief:

  • Restart the Management Service on the Delphix engine. This is not desired but will provide relief.  It will not affect VDBs or dSource staging databases. It will cancel running jobs, and interrupt tasks such as validated sync and policies. To restart the Management Service, from GUI you login as the sysadmin in the "Setup" application.
    • Click on the "three dots", the ellipsis in the upper right, select "Restart", read the "Do you really want to do this" popup and agree to it.

restart.png

  • Look for the java process running the JDBC connections (refer to Troubleshooting section). You can kill this process, but you may need to check for restore activity on the staging instance first so as to not interrupt the ingestions.  You can use a query like this to check:
SELECT sqltext.TEXT,WAIT_TYPE,
req.session_id,
req.status,
req.command,
req.cpu_time,
percent_complete,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
WHERE req.command in ('BACKUP DATABASE','RESTORE DATABASE', 'RESTORE LOG')

When you don't see RESTORE activity you can kill the java process (you can locate java process as described in the Troubleshooting section).  After it is killed, the Delphix engine will start a new process to proceed with validated sync activities.

 

Troubleshooting

There is a diagnostic marker as indicated in the beginning of this article:

SQLState: 08S01 SQLErrorCode: 0 ErrorMessage: The TCP/IP connection to the host Windows_Source_host.acme.com, port 1433 has failed. Error: "null. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.". 

There is another process used to create and manage the JDBC connections from staging to the source to determine backupset history. For example, this information is used to properly determine next available backups for a validated sync scenario.

Locating the java process for JDBC connections

This other java process originates from a toolkit directory similar to this one as seen in the Windows task manager:

C:\Program Files\Delphix\DelphixConnector\Delphix_COMMON_b6ecbbc43c25_host\java 

You can inspect this java process from the Windows task manager on the staging host:

commandLine.png

In order to see the command line you need to add it to the "details" section of the task manager.

If you right click in the column header for the Task Manager Details (where you see column names such as "Name", "PID", "Status", etc) you will access a popup windows.  Scroll through it and choose "Command line" to add to the headers.

columnHeader.png

If you notice the "Memory (active private working set)" is displaying usage in the tens or more GBs range (like 12,000,000 K) for example, you might be approaching a threshold and may need to kill that java process. You can does this once you can confirm no activities for a few minutes (See Resolution section for example of Restore activity query to run to check).

You can also check for java HPROF  (heap memory profile) files as well in the event an out of memory condition occurs, under the Delphix\DelphixConnector directory. If this occurs you may experience the connectivity issue and message indicated at the beginning of this article (fault example).

Also, restarting DelphixConnector will do nothing to resolve since it isn't handling these JDBC connections.

 

 


Related Articles

The following articles may provide more information or related information to this article:

  • link
  • link
  • link