SQLWatchImporter timeouts when merging sqlwatch_logger_* tables

[dennisa72295]

Did you check DOCS to make sure there is no workaround? https://sqlwatch.io/docs/ Yes Describe the bug Executing SQLWatchImporter produces 2 timeout errors. One for each of these tables: sqlwatch_logger_perf_os_performance_counters and sqlwatch_logger_agent_job_history

To Reproduce Steps to reproduce the behavior: Execute the SQLWatchImporter job where there are 2 remote instances both on the same server with different ports

Expected behavior Expect the job to complete without errors

Screenshots If applicable, add screenshots to help explain your problem.

Windows Server (please complete the following information):

• OS Version: Windows 2016

SQL Server (please complete the following information):

• SQL Version: SQL Server 2016 SP12
• SQL Edition: Enterprise

SQL Server Management Studio (SSMS -> about -> copy info): SQL Server Management Studio 15.0.18390.0 SQL Server Management Objects (SMO) 16.100.46521.71 Microsoft Analysis Services Client Tools 15.0.19714.0 Microsoft Data Access Components (MDAC) 10.0.18362.1 Microsoft MSXML 3.0 6.0 Microsoft .NET Framework 4.0.30319.42000 Operating System 10.0.18363

SQLWATCH version (from DACPAC or from sysinstances)

    4.2.0.28234

Additional context 2021-11-04 09:30:02.735 ERROR Failed to merge table "[######dbt01\STAGE].dbo.sqlwatch_logger_perf_os_performance_counters"

                    at Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

                    ;merge dbo.sqlwatch_logger_perf_os_performance_counters as target

                            using (

                            select s.* from [#dbo.sqlwatch_logger_perf_os_performance_counters] s

                            inner join dbo.sqlwatch_logger_snapshot_header h

                                on s.[snapshot_time] = h.[snapshot_time]

                                and s.[snapshot_type_id] = h.[snapshot_type_id]

                                and s.[sql_instance] = h.[sql_instance]) as source

                        on (source.[snapshot_time] = target.[snapshot_time] and source.[snapshot_type_id] = target.[snapshot_type_id] and source.[sql_instance] = target.[sql_instance] and source.

[performance_counter_id] = target.[performance_counter_id] and source.[instance_name] = target.[instance_name])

                        when not matched

                        then insert ([performance_counter_id],[instance_name],[cntr_value],[base_cntr_value],[snapshot_time],[snapshot_type_id],[sql_instance],[cntr_value_calculated])

                        values (source.[performance_counter_id],source.[instance_name],source.[cntr_value],source.[base_cntr_value],source.[snapshot_time],source.[snapshot_type_id],source.

[sql_instance],source.[cntr_value_calculated]); (Thread: 16) 2021-11-04 09:31:31.404 DEBUG Copied 5389064 rows from "[auslyncdbt01].dbo.sqlwatch_logger_perf_file_stats" to "#dbo.sqlwatch_logger_perf_file_stats" in 154578.7466ms. (Thread: 12) 2021-11-04 09:31:33.789 DEBUG Merged 2481149 rows from "#dbo.sqlwatch_logger_disk_utilisation_table" for "######dbt01" in 106823.2962ms (Thread: 128) 2021-11-04 09:31:33.789 INFO Imported "dbo.sqlwatch_logger_disk_utilisation_table" from "######dbt01" in 156981.627ms (Thread: 128) 2021-11-04 09:34:18.859 ERROR Failed to merge table "[######dbt01].dbo.sqlwatch_logger_agent_job_history"

                    at Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

                    ;merge dbo.sqlwatch_logger_agent_job_history as target

                            using (

                            select s.* from [#dbo.sqlwatch_logger_agent_job_history] s

                            inner join dbo.sqlwatch_logger_snapshot_header h

                                on s.[snapshot_time] = h.[snapshot_time]

                                and s.[snapshot_type_id] = h.[snapshot_type_id]

                                and s.[sql_instance] = h.[sql_instance]) as source

                        on (source.[sql_instance] = target.[sql_instance] and source.[snapshot_time] = target.[snapshot_time] and source.[sqlwatch_job_id] = target.[sqlwatch_job_id] and source.

[sqlwatch_job_step_id] = target.[sqlwatch_job_step_id] and source.[sysjobhistory_instance_id] = target.[sysjobhistory_instance_id] and source.[snapshot_type_id] = target.[snapshot_type_id])

                        when not matched

                        then insert ([sql_instance],[sqlwatch_job_id],[sqlwatch_job_step_id],[sysjobhistory_instance_id],[sysjobhistory_step_id],[run_duration_s],[run_date],[run_status],

[snapshot_time],[snapshot_type_id],[run_date_utc])

                        values (source.[sql_instance],source.[sqlwatch_job_id],source.[sqlwatch_job_step_id],source.[sysjobhistory_instance_id],source.[sysjobhistory_step_id],source.

[run_duration_s],source.[run_date],source.[run_status],source.[snapshot_time],source.[snapshot_type_id],source.[run_date_utc]); (Thread: 22)

[marcingminski]

Is the issue that there is too much data and the initial pull is taking too long? You can increase the timeout in the config.

[dennisa72295]

I increased the BulkCopyTimeout to 600 but I’m still getting errors. It seems to be having some issues with only one of my instances. I have 2 instances running on the same server using different ports. @@SERVERNAME for the first instance that seems to be working is the same as the hostname. It uses port 1434. The second instance is a named instance (i.e. hostname\STAGE) using port 1435. This is the one that seems to be having issues with the timeouts. I’ve attached the log file for you to see the errors Thanks Dennis

From: Marcin Gminski @.> Sent: Thursday, November 4, 2021 10:13 AM To: marcingminski/sqlwatch @.> Cc: ANDERSON Dennis (FRA-DPIT) @.>; Author @.> Subject: Re: [marcingminski/sqlwatch] SQLWatchImporter timeouts when merging sqlwatchlogger* tables (Issue #424)

Security Notice: Please be aware that this email was sent by an external sender.

Is the issue that there is too much data and the initial pull is taking too long? You can increase the timeout in the config.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHubhttps://github.com/marcingminski/sqlwatch/issues/424#issuecomment-961019827, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AUOKAOJ6OCHNSWN5QXFRBADUKKIGJANCNFSM5HLREJPQ. Triage notifications on the go with GitHub Mobile for iOShttps://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Androidhttps://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub.

[dennisa72295]

I had to increase the timeout to at least 900 to get it to complete successfully. There should be something in the documentation that explains this. Also there should be a way to remove instances from the repository using the SQLWatchImporter executable.

[marcingminski]

Thanks. Documentation is open source so you are free to add a note to it.

Also there should be a way to remove instances from the repository using the SQLWatchImporter executable.

This is more complex as removing instance will also remove its data and with large volumes, this may take a long time. This has been discussed here: https://meta.sqlwatch.io/t/sqlwatchimport-remove-remote-instance/96