I am trying to view the details of a SQL Server agent job history log entry. What I am seeing is the following:
Date 2/1/2021 8:30:01 AM
Log Job History (Every 10 Minutes Transaction Log Backup.Subplan_1)
Step ID 1
Server SQL-SERVER
Job Name Every 10 Minutes Transaction Log Backup.Subplan_1
Step Name Subplan_1
Duration 00:00:06
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: CC\sqlserveragent. ... 14.0.2037.2 for 64-bit Copyright (C) 2017 Microsoft. All rights reserved. Started: 8:30:01 AM Progress: 2021-02-01 08:30:01.39 Source: {4720AEAA-15ED-4DCA-A83D-079899E0FD78} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp...".: 100% complete End Progress Progress: 2021-02-01 08:30:03.03 Source: Back Up Database (Transaction Log) Executing query "EXECUTE master.dbo.xp_create_subdir N'\\FILE-SERVE...".: 4% complete End Progress Progress: 2021-02-01 08:30:03.03 Source: Back Up Database (Transaction Log) Executing query "EXECUTE master.dbo.xp_create_subdir N'\\FILE-SERVE...".: 9% complete End Progress Progress: 2021-02-01 08:30:03.04 Source: Back Up Database (Transaction Log) Executing query "EXECUTE master.dbo.xp_create_subdir N'\\FILE-SERVE...".: 13% complete End Progress Progress: 2021-02-01 08:30:03.04 Source: Back Up Database (Transaction Log) Executing query "EXECUTE master.dbo.xp_create_subdir N'\\FILE-SERVE...".: 18% complete End Progress Progress: 2021-02-01 08:30:03.04 Source: Back Up Database (Transaction Log) Executing query "EXECUTE master.dbo.xp_create_subdir N'\\FILE-SERVE...".: 22% complete End Progress Progress: 2021-02-01 08:30:03.04 Source: Back Up Database (Transaction Log) Executing query "EXECUTE master.dbo.xp_create_subdir N'\\FILE-SERVE...".: 27% complete End Progress Progress: 2021-02-01 08:30:03.04 Source: Back Up Database (Transaction Log) Executing query "EXECUTE master.dbo.xp_create_subdir N'\\FILE-SERVE...".: 31% complete End Progress Progress: 2021-02-01 08:30:03.04 Source: Back Up Database (Transaction Log) Executing query "EXECUTE master.dbo.xp_create_subdir N'\\FILE-SERVE...".: 36% complete End Progress Progress: 2021-02-01 08:30:03.04 Source: Back Up Database (Transaction Log) Executing query "EXECUTE master.dbo.xp_create_subdir N'\\FILE-SERVE...".: 40% complete End Progress Progress: 2021-02-01 08:30:03.04 Source: Back Up Database (Transaction Log) Executing query "EXECUTE master.dbo.xp_create_subdir N'\\FILE-SERVE...".: 45% complete End Progress Progress: 2021-02-01 08:30:03.06 Source: Back Up Database (Transaction Log) Executing query "EXECUTE master.dbo.xp_create_subdir N'\\FILE-SERVE...".: 50% complete End Progress Progress: 2021-02-01 08:30:03.06 Source: Back Up Database (Transaction Log) Executing query "EXECUTE master.dbo.xp_create_subdir N'\\FILE-SERVE...".: 54% complete End Progress Progress: 2021-02-01 08:30:03.06 Source: Back Up Database (Transaction Log) Executing query "EXECUTE master.dbo.xp_create_subdir N'\\FILE-SERVE...".: 59% complete End Progress Progress: 2021-02-01 08:30:03.06 Source: Back Up Database (Transaction Log) Executing query "EXECUTE master.dbo.xp_create_subdir N'\\FILE-SERVE...".: 63% complete End Progress Progress: 2021-02-01 08:30:03.06 Source: Back Up Database (Transaction Log) Executing query "EXECUTE master.dbo.xp_create_subdir N'\\FILE-SERVE...".: 68% complete End Progress Progress: 2021-02-01 08:30:03.06 Source: Back Up Database (Transaction Log) Executing query "EXECUTE master.dbo.xp_create_subdir N'\\FILE-SERVE...".: 72% complete End Progress Progress: 2021-02-01 08:30:03.06 Source: Back Up Database (Transaction Log) Executing query "EXECUTE master.dbo.xp_create_subdir N'\\FILE-SERVE...".: 77% complete End Progress Progress: 2021-02-01 08:30:03.06 Source: Back Up Database (Transaction Log) Executing query "EXECUTE master.dbo.xp_create_subdir N'\\FILE-SERVE...".: 81% complete End Progress Progress: 2021-02-01 08:30:03.06 Source: Back Up Database (Transaction Log) Executing query "EXECUTE master.dbo.xp_create_subdir N'\\FILE-SERVE...".: 86% complete End Progress Progress: 2021-02-01 08:30:03.06 Source: Back Up Database ... The package execution fa... The step failed.
I have read multiple posts about limiting the size of the log and removing log files older than X days/weeks etc. but I cannot seem to find anything related to showing or configuring logging to show the complete details of each line item in the log. Here is a more specific example from the log I posted above:
Progress: 2021-02-01 08:30:03.06 Source: Back Up Database (Transaction Log) Executing query "EXECUTE master.dbo.xp_create_subdir N'\\FILE-SERVE...".: 86% complete End Progress Progress: 2021-02-01 08:30:03.06 Source: Back Up Database ... The package execution fa... The step failed.
Within that example, you can see the ellipses (...) that are used to abbreviate the log entry. You can also see that the information in the entry is not enough to determine the problem. So...
How do I configure SQL Server or SQL Server Management Studio to log the entire query being executed and the reason for the failure?
