Skip to main content
Delphix

Collecting diagnostics from SQL Server hosts for Delphix support cases

Goal

This article provides steps for collecting diagnostic information from Windows Server hosts to assist with Delphix support cases, including:

  • General server information
  • The SQL Server ERRORLOG
  • Windows Event Logs
  • Delphix Connector Logs

General server information

The following commands can be run from a Windows command prompt (cmd.exe) to:

  • Create a folder "casenumber" in C:\Temp
  • Collect system information
  • List installed Windows updates, using a Windows Management Instrumentation (WMI) query
  • Export registry entries relating to SQL Server, including startup parameters, installed instances and software versions
  • Export registry entries relating to network and iSCSI configuration, for comparison with Delphix's Recommendations for Windows iSCSI Configuration

Subsequent steps also refer to this example directory C:\Temp\casenumber. Please substitute your own directory as appropriate.

cd /D C:\Temp\
mkdir casenumber
cd casenumber

systeminfo > dlpx_systeminfo.txt
wmic qfe > dlpx_wmic_qfe.txt

regedit.exe /e dlpx_reg_sql.txt "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server"
regedit.exe /e dlpx_reg_iscsi.txt "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\iSCSI"
regedit.exe /e dlpx_reg_disk.txt "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Disk"
regedit.exe /e dlpx_reg_tcp.txt "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\Interfaces"
regedit.exe /e dlpx_reg_scsi.txt "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Class\{4D36E97B-E325-11CE-BFC1-08002BE10318}"

SQL Server ERRORLOG

The SQL Server ERRORLOG is used by Delphix Support to correlating the timing of database activities between the Delphix Engine and the involved SQL Server instance.

ERRORLOG files can be copied directly from your SQL Server's Log directory to C:\Temp\casenumber using Windows Explorer. The ERRORLOG is typically located in:

C:\Program Files\Microsoft SQL Server\MSSQL[VERSION].[INSTANCENAME]\MSSQL\Log

The current location can be determined by querying the affected SQL Server instance with:

SELECT SERVERPROPERTY('ErrorLogFileName');

Where possible, please copy all ERRORLOG files relevant to the issue, showing:

  • Typical behavior before the issue started, if applicable
  • All logs covering the period in which the issue or failed operation occurred
  • Typical behavior after the issue was resolved, if applicable

If unsure, please provide ERRORLOG and ERRORLOG.1.

Windows Event Logs

Windows Event logs are a valuable source of information regarding Service and Disk activity.

The Event Viewer is used to export the event logs. It can be located using the Start Menu, or launched directly as eventvwr.msc

Please export both the Application and System logs:

1_event_logs.png

Each Event Log can be saved using the Save All Events As... option in the Actions sidebar, on the right hand side of the Event Viewer window.

2_save_events.png

Use this dialog to save each event log in both Event Log (.evtx) and Comma Separated (.csv) format, producing a total of four files:

  • Application Log (EVTX): C:\Temp\casenumber\application_HOSTNAME.evtx
  • Application Log (CSV): C:\Temp\casenumber\application_HOSTNAME.csv
  • System Log (EVTX): C:\Temp\casenumber\system_HOSTNAME.evtx
  • System Log (CSV): C:\Temp\casenumber\system_HOSTNAME.csv

The binary .evtx and text-based .csv formats are complementary, and we may be unable to fully analyze some errors if only one format is provided.

If the produced files are too large for transfer and upload, you can use the Event Viewer to filter by event severity, or by time. Unfiltered event logs are preferred because they allow the opportunity to look for patterns of behavior and to use low-severity messages for tracking provisioning activity.

Delphix Connector Logs

The Delphix Connector logs are available in the DelphixConnector\logs folder of your Delphix Connector installation directory, in the following location by default:

C:\Program Files\Delphix\DelphixConnector\logs

Please copy the following files directly to C:\Temp\casenumber:

  • debug.log
  • error.log
  • Compressed debug logs for the date you experienced the issue, e.g. debug.log2017-01-01.1.zip for January 1, 2017
  • Compressed error logs for the date you experienced the issue, e.g. error.log2017-01-01.1.zip for January 1, 2017

If the affected server is an SQL Server Source Environment and not a Delphix Target Environment, then the relevant Delphix Connector logs will be present on the configured "Connector" or "Proxy" server.

Checklist

If you have collected all information in the previous sections, the following information should now be present in your case directory:

  • dlpx_systeminfo.txt
  • dlpx_wmic_qfe.txt
  • dlpx_reg_sql.txt
  • dlpx_reg_iscsi.txt
  • dlpx_reg_disk.txt
  • dlpx_reg_tcp.txt
  • dlpx_reg_scsi.txt
  • ERRORLOG
  • [OPTIONAL] ERRORLOG.1 [,...]
  • application_HOSTNAME.evtx
  • application_HOSTNAME.csv
  • system_HOSTNAME.evtx
  • system_HOSTNAME.csv
  • debug.log
  • error.log
  • [OPTIONAL] debug.logYYYY-MM-DD.1.zip [,...]
  • [OPTIONAL] error.logYYYY-MM-DD.1.zip [,...]

Uploading logs to your support case

Depending on your preference, you can either upload the collected files individually or compress them into a single file (zip or tar.gz are preferred, for compatibility).

To compress a folder in Windows, use the Windows Explorer to right-click on the folder and select Send toCompressed (zipped) folder.

 

Files of less than 50MB in size can be attached directly to the case, using the link contained in emails regarding your open case. Files larger than this size can be attached using https://upload.delphix.com, referencing your case number when prompted.

Additional information

In some cases, Delphix Support may ask for the collection of additional logs or data not mentioned in this document, such as:

  • Disk performance test results using diskspd
  • Output from diagnostic tools such as Process Explorer
  • Version information of system DLLs or other files
  • SQL Server Profiler traces
  • Detailed profiling data collected by DiagManager
  • Performance data from Performance Monitor Data Collector Sets

Some of this additional information may require the use of tools which are provided by Microsoft, but not built into the Windows Server operating system.

Delphix Support will provide explicit instructions when requesting this additional information, or will offer assistance via WebEx.