I have a maintenance plan that runs on SQL Server agent as a the job and finishes with error. However the error is not visible in job history in SSMS, the text of Message is seemingly truncated. How can I see the whole log and which command actually failed?
Date 22.08.2020 23:00:00
Log Job History (MP - HeliosG Optimizations.Subplan_1)
Step ID 1
Server XXXX
Job Name MP - HeliosG Optimizations.Subplan_1
Step Name Subplan_1
Duration 02:26:43
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: XXXX\SYSTEM. ...ion 10.0.6000.29 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 23:00:00 Progress: 2020-08-22 23:00:01.35 Source: {4DF475B9-8D88-4BD3-9AFC-48FBBDAC9EB9} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp...".: 100% complete End Progress Progress: 2020-08-22 23:00:11.26 Source: Update Statistics Executing query "use [EkDB] ".: 0% complete End Progress Progress: 2020-08-22 23:00:14.47 Source: Update Statistics Executing query "UPDATE STATISTICS [dbo].[_AnalyzaDL] WITH FULLSC...".: 1% complete End Progress Progress: 2020-08-22 23:00:14.47 Source: Update Statistics Executing query "use [EkDB] ".: 1% complete End Progress Progress: 2020-08-22 23:00:16.34 Source: Update Statistics Executing query "UPDATE STATISTICS [dbo].[_AnalyzaDL_Historie] WI...".: 2% complete End Progress Progress: 2020-08-22 23:00:16.34 Source: Update Statistics Executing query "use [EkDB] ".: 2% complete End Progress Progress: 2020-08-22 23:00:16.68 Source: Update Statistics Executing query "UPDATE STATISTICS [dbo].[_CenPodm] WITH FULLSCAN...".: 3% complete End Progress Progress: 2020-08-22 23:00:16.68 Source: Update Statistics Executing query "use [EkDB] ".: 3% complete End Progress Progress: 2020-08-22 23:00:16.99 Source: Update Statistics Executing query "UPDATE STATISTICS [dbo].[_CenPodmSmlouvy] WITH F...".: 4% complete End Progress Progress: 2020-08-22 23:00:17.00 Source: Update Statistics Executing query "use [EkDB] ".: 4% complete End Progress Progress: 2020-08-22 23:00:17.01 Source: Update Statistics Executing query "UPDATE STATISTICS [dbo].[_Dodavatele] WITH FULLS...".: 5% complete End Progress Progress: 2020-08-22 23:00:17.01 Source: Update Statistics Executing query "use [EkDB] ".: 6% complete End Progress Progress: 2020-08-22 23:00:17.12 Source: Update Statistics Executing query "UPDATE STATISTICS [dbo].[_DopPodmSmlouvy] WITH F...".: 6% complete End Progress Progress: 2020-08-22 23:00:17.12 Source: Update Statistics Executing query "use [EkDB] ".: 7% complete End Progress Progress: 2020-08-22 23:00:17.17 Source: Update Statistics Executing query "UPDATE STATISTICS [dbo].[_DopravniPodm] WITH FUL...".: 7% complete End Progress Progress: 2020-08-22 23:00:17.17 Source: Update Statistics Executing query "use [EkDB] ".: 8% complete End Progress Progress: 2020-08-22 23:00:17.22 Source: Update Statistics Executing query "UPDATE STATISTICS [dbo].[_DoPTEST] WITH FULLSCAN...".: 8% complete End Progress Progress: 2020-08-22 23:00:17.22 Source: Update Statistics Executing query "use [EkDB] ".: 9% complete End Progress Progress: 2020-08-22 23:00:17.23 Source: Update Statistics Executing query "UPDATE STATISTICS [dbo].[_FBL3N] WITH FULLSCAN ".: 9% complete End Progress Progress: 2020-08-22 23:00:17.23 Source: Update Statistics Executing query "use [EkDB] ".: 10% complete End Progress Progress: 2020-08-22 23:00:17.29 Source: Update Statistics Executing query "UPDATE STATISTICS [dbo].[_IK17] WITH FULLSCAN ".: 10% complete End Progress Progress: 2020-08-22 23:00:17.29 Source: Update Statistics Executing query "use [EkDB] ".: 11% complete End Progress Progress: 2020-08-22 23:00:17.31 Source: Update Statistics Executing query "UPDATE STATISTICS [dbo].[_KatBeda] WITH FULLSCAN...".: 12% complete End Progress Progress: 2020-08-22 23:00:17.31 Source: Update Statistics Executing query "use [EkDB] ".: 12% complete End Progress Progress: 2020-08-22 23:00:17.40 Source: Update Statistics Executing query "UPDATE STATISTICS [dbo].[_MB51] WITH FULLSCAN ".: 13% complete End Progress Progress: 2020-08-22 23:00:17.40 Source: ... The package execution fa... The step failed.
Best Answer
You could try logging the out put in the advanced tab for the job step, there are couple of options 1) log to a table in msdb 2) log the output to a file