smchhaniyara
commented
3 years ago Here is the job failed logs if it can help to review. I think Created landing table is not cleaning up if any error or job failed during SQLWATCH import.
Agent Job Failed Log details
Date 11/16/2021 10:01:00 PM Log Job History (SQLWATCH_Import_Remote_Data)
Step ID 1
Server WSSQLEABITRN01T\TSTINST01
Job Name SQLWATCH_Import_Remote_Data
Step Name Import Remote Data
Duration 00:00:10
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message Executed as user: HEALTHY\xxxxxx ...er Imports remote SQLWATCH data into the Central Repository Marcin Gminski 2020, SQLWATCH.IO Version: 1.2.7872.23725 (2021-07-21 13:10:50) 2021-11-16 22:01:00.784 DEBUG Application Configuration CentralRepositorySqlInstance : WSSQLEABITRN01T\TSTINST01 CentralRepositorySqlDatabase : SQLWATCH CentralRepositorySqlUser : CentralRepositorySqlSecret : SqlBulkCopy.EnableStreaming : true SqlBulkCopy.BatchSize : 4000 SqlBulkCopy.BulkCopyTimeout : 300 EnvironmentToProcess : ALL FullLoad : false LogFile : SqlWatchImport.log MaxLogSizeMB : 10 MaxLogFiles : 10 PrintToLogFile : true PrintToConsole : true MinThreads : -1 MaxThreads : 0 MinPoolSize : 0 MaxPoolSize : 0 DumpDataOnError : true ClientSettingsProvider.ServiceUri : (Thread: 1) 2021-11-16 22:01:00.784 DEBUG Checking if Central Repository is online (Thread: 1)2021-11-16 22:01:00.830 DEBUG Central Repository SQLWATCH Version: "4.2.0.28234" (Thread: 1) 2021-11-16 22:01:00.846 Got 3 instances to import 2021-11-16 22:01:00.846 Got 57 tables to import from each instance 2021-11-16 22:01:00.846 DEBUG Automatically setting MinThreads to 171 (Thread: 1) 2021-11-16 22:01:00.862 DEBUG "WSSQLARESTRN01T\TSTINST01" SQLWATCH Version: "4.2.0.28234" (Thread: 8) 2021-11-16 22:01:00.862 DEBUG "WSSQLC25N03\RPTINST04" SQLWATCH Version: "4.2.0.28234" (Thread: 9) 2021-11-16 22:01:00.862 Importing: "WSSQLARESTRN01T\TSTINST01" 2021-11-16 22:01:00.862 Importing: "WSSQLC25N03\RPTINST04" 2021-11-16 22:01:00.862 DEBUG Checking if Central Repository is online (Thread: 8) 2021-11-16 22:01:00.862 DEBUG Checking if Central Repository is online (Thread: 9) 2021-11-16 22:01:00.862 DEBUG "WSSQLC25N02\RPTINST04" SQLWATCH Version: "4.2.0.28234" (Thread: 6) 2021-11-16 22:01:00.862 Importing: "WSSQLC25N02\RPTINST04" 2021-11-16 22:01:00.862 DEBUG Checking if Central Repository is online (Thread: 6)2021-11-16 22:01:00.987 DEBUG Fetched "Last Updated" ("2021-09-27 22:52:49.703") from "dbo.sqlwatch_meta_server" for "[WSSQLC25N03\RPTINST04]" in 0.4946ms. (Thread: 13) 2021-11-16 22:01:00.987 DEBUG Nothing to import from "[WSSQLC25N03\RPTINST04].dbo.sqlwatch_meta_server" (Thread: 14) 2021-11-16 22:01:00.987 DEBUG Fetched "Last Updated" ("2021-09-27 22:34:24.107") from "dbo.sqlwatch_meta_server" for "[WSSQLC25N02\RPTINST04]" in 0.5633ms. (Thread: 13) 2021-11-16 22:01:00.987 DEBUG Nothing to import from "[WSSQLC25N02\RPTINST04].dbo.sqlwatch_meta_server" (Thread: 5) 2021-11-16 22:01:01.018 DEBUG Fetched "Last Updated" ("2021-09-14 21:33:40.960") from "dbo.sqlwatch_meta_server" for "[WSSQLARESTRN01T\TSTINST01]" in 0.4879ms. (Thread: 13) 2021-11-16 22:01:01.018 DEBUG Nothing to import from "[WSSQLARESTRN01T\TSTINST01].dbo.sqlwatch_meta_server" (Thread: 18)2021-11-16 22:01:02.925 DEBUG Created landing table "#dbo.sqlwatch_logger_snapshot_header" for "WSSQLC25N02\RPTINST04" in 394.1877ms. (Thread: 18) 2021-11-16 22:01:02.925 DEBUG Created landing table "#dbo.sqlwatch_logger_snapshot_header" for "WSSQLC25N03\RPTINST04" in 349.1661ms. (Thread: 9) 2021-11-16 22:01:02.956 DEBUG Created landing table "#dbo.sqlwatch_logger_snapshot_header" for "WSSQLARESTRN01T\TSTINST01" in 431.079ms. (Thread: 11) 2021-11-16 22:01:02.971 DEBUG Copied 27 rows from "[WSSQLC25N03\RPTINST04].dbo.sqlwatch_logger_snapshot_header" to "dbo.s... Process Exit Code 1. The step failed.
marcingminski
I can see many table name like '_DUMP_20211115172405038_WSSQLARESTRN01T\TSTINST01.dbo.sqlwatch_meta_query_plan' generated in Central Repository database.
Can you please provide any suggestion ?
use SQLWATCH go select * from sys.all_objects where type='U' and name like '_DUMP%' order by create_date desc
(42125 rows affected)
_DUMP_20211115172405038_WSSQLARESTRN01T\TSTINST01.dbo.sqlwatch_meta_query_plan _DUMP_20211115172322787_WSSQLARESTRN01T\TSTINST01.dbo.sqlwatch_logger_perf_procedure_stats _DUMP_20211115172304209_WSSQLARESTRN01T\TSTINST01.dbo.sqlwatch_meta_query_plan _DUMP_20211115172213317_WSSQLARESTRN01T\TSTINST01.dbo.sqlwatch_logger_perf_procedure_stats _DUMP_20211115172205395_WSSQLARESTRN01T\TSTINST01.dbo.sqlwatch_meta_query_plan _DUMP_20211115172124114_WSSQLARESTRN01T\TSTINST01.dbo.sqlwatch_logger_perf_procedure_stats _DUMP_20211115172110285_WSSQLARESTRN01T\TSTINST01.dbo.sqlwatch_meta_query_plan _DUMP_20211115172016754_WSSQLARESTRN01T\TSTINST01.dbo.sqlwatch_logger_perf_procedure_stats _DUMP_20211115172006020_WSSQLARESTRN01T\TSTINST01.dbo.sqlwatch_meta_query_plan _DUMP_20211115171912954_WSSQLARESTRN01T\TSTINST01.dbo.sqlwatch_logger_perf_procedure_stats _DUMP_20211115171904642_WSSQLARESTRN01T\TSTINST01.dbo.sqlwatch_meta_query_plan _DUMP_20211115171511884_WSSQLARESTRN01T\TSTINST01.dbo.sqlwatch_logger_perf_procedure_stats _DUMP_20211115171503837_WSSQLARESTRN01T\TSTINST01.dbo.sqlwatch_meta_query_plan _DUMP_20211115171424431_WSSQLARESTRN01T\TSTINST01.dbo.sqlwatch_logger_perf_procedure_stats _DUMP_20211115171405261_WSSQLARESTRN01T\TSTINST01.dbo.sqlwatch_meta_query_plan _DUMP_20211115171312917_WSSQLARESTRN01T\TSTINST01.dbo.sqlwatch_logger_perf_procedure_stats _DUMP_20211115171304885_WSSQLARESTRN01T\TSTINST01.dbo.sqlwatch_meta_query_plan _DUMP_20211115171211789_WSSQLARESTRN01T\TSTINST01.dbo.sqlwatch_logger_perf_procedure_stats _DUMP_20211115171203835_WSSQLARESTRN01T\TSTINST01.dbo.sqlwatch_meta_query_plan _DUMP_20211115171112268_WSSQLARESTRN01T\TSTINST01.dbo.sqlwatch_logger_perf_procedure_stats