SQL Agent Job failing with Error 8152 (String or binary data would be truncated) during Replication/Distribution

I have noticed that you don't always get information about column and value, but you only get the generic 8152. I have not noticed the exact pattern, but I know that there other situations than just writing to a column where you can get a truncation error, and Microsoft has not improved the error message for these situation.

I would recommend that you set up an extended-events session that captures the event error_reported, and that you set up the event so that it captures the statement for the error. As it happens, I have an article for how to do this on my web site: Monitor Exceptions in Your Database. The article also includes a view to easily view the information. This should at least take you closer to a solution.

Use the replication troubleshooting tools and logging around the Distribution Agent to locate the failing transaction and then inspect it in the distribution database; the generic 8152 error alone is not enough.

1. Identify where replication is failing

Follow the transactional replication troubleshooting flow:

1. Use Replication Monitor to confirm that the error is occurring in the Distributor to Subscriber path (Distribution Agent).
2. From Replication Monitor or SQL Server Agent → Job Activity Monitor, open the failing Distribution Agent job and review the job history for the exact step and error text.
3. If the job history is not detailed enough, enable verbose logging for the Distribution Agent.

Reference steps for this are in the transactional replication troubleshooter under “Troubleshooting methodology” and “Enable verbose logging on any agent.”

2. Enable verbose logging for the Distribution Agent

Verbose logging can capture the exact batch and command that fails at the Subscriber:

1. Edit the Distribution Agent job step (the one that runs the agent executable).
2. Add an -Output parameter and a verbosity level to the command line, for example:
   • -Output <path_to_log_file>
   • Ensure there is a space before -Output (the documentation explicitly calls out that a missing space can prevent startup).
3. If the agent fails to start with a high verbosity level, decrease the verbosity by 1 as suggested in the troubleshooting guide.
4. Restart the Distribution Agent job and let it fail again.
5. Review the generated output file on disk; it will show the failed transaction and often the exact statement that caused the truncation.

This is the primary supported way to see the failing statement when the job history only shows the generic 8152 error.

3. Use the Distribution Agent error file

The Distribution Agent supports an -ErrorFile argument:

• -ErrorFile specifies a file that contains the failed replication transactions and associated error messages when failures occur at the Subscriber.
• If not specified, an .err file with the agent name is generated in the current directory.

Ensure the Distribution Agent is configured with -ErrorFile (or locate the default .err file) and inspect it for:

• The failed batch (INSERT/UPDATE) being applied at the Subscriber.
• The error message tied to that batch.

This will give the exact T‑SQL statement and values that caused the truncation.

4. Query MSrepl_errors in the distribution database

The distribution database keeps extended error information in MSrepl_errors:

SELECT TOP (100)
       time,
       source_name,
       error_code,
       error_text,
       xact_seqno,
       command_id
FROM   distribution.dbo.MSrepl_errors
ORDER BY time DESC;

Key columns:

• error_text – contains the error message (including 8152 text).
• xact_seqno and command_id – identify the first transaction and command in the failed batch.

Once the failing xact_seqno/command_id are known, use the standard replication metadata tables (e.g., commands in the distribution database) to locate the exact command and then map it back to the table and columns at the Subscriber. The documentation notes that MSrepl_errors is specifically for Distribution Agent and Merge Agent failure information.

5. About VERBOSE_TRUNCATION_WARNINGS / Trace Flag 460

The provided context does not document VERBOSE_TRUNCATION_WARNINGS or Trace Flag 460 behavior with SQL Agent job history, so no statement can be made from this context about whether they will surface column names in job history. The supported and documented way in this context to get more detail is:

• Verbose agent logging (-Output with higher verbosity).
• Distribution Agent -ErrorFile.
• MSrepl_errors in the distribution database.

6. Check for replication‑specific causes

From the replication troubleshooting guidance:

• Determine whether the error is in the Publisher to Distributor or Distributor to Subscriber path.
• If it is in the Distribution Agent, the problem is with applying transactions at the Subscriber.

Schema mismatches (for example, smaller column length at the Subscriber than at the Publisher) are a common cause of 8152 in replication scenarios, but the context here does not describe a specific “Detect nonlogged agent shutdown” bug or schema mismatch issue for SQL Server 2022. Use the methods above to identify the failing statement and then compare the table schema between Publisher and Subscriber for that table.

7. General replication error‑handling steps

If the agent continues to fail:

1. Restart the agent and see if it runs without failures.
2. Check agent history and job history for other errors around the same time.
3. Verify connectivity between Publisher, Distributor, and Subscriber using the same accounts as the agents.
4. If the error persists, increase logging and review the output file as described above.

These steps are documented as the standard user actions for replication agent failures.

References:

• Troubleshooter: Find errors with SQL Server transactional replication
• Replication Distribution Agent
• MSrepl_errors (Transact-SQL)
• MSSQL_ENG020557
• SQL Server Replication (Troubleshooting)
• KB3081580 - FIX: "String or binary data would be truncated" error when you use peer-to-peer replication in SQL Server 2012 or SQL Server 2014
• Not able to find the proper issue field - Microsoft Q&A