SQL Snapshot Replication Fails with BCP EOF / NULL Errors (Works in Transactional Replication)

Question

SQL Snapshot Replication Fails with BCP EOF / NULL Errors (Works in Transactional Replication)

Parag Nimbhore 5

Issue:
We have Snapshot Replication on a production server that was working fine but recently started failing with the below error:

Agent message code 20037. The process could not bulk copy into table "XXX".

Message: End of file reached, terminator missing or field data incomplete
Message:Batch send failed

Message: Failed to send batch after max errors

More details:

Publisher & Distributor: Microsoft SQL Server 2017 (RTM-CU31-GDR) (KB5084818) - 14.0.3525.1 (X64) Standard Edition on Windows Server 2016 Datacenter

Subscriber:

Microsoft SQL Server 2022 (RTM-CU24-GDR) (KB5083252) - 16.0.4250.1 (X64) Standard Edition on Windows Server 2022 Datacenter
Microsoft SQL Server 2017 (RTM-CU31-GDR) - 14.0.3525.1 (X64) Standard Edition on Windows Server 2016 Datacenter

What we tried:

Dropped table on subscriber + deleted snapshot folder + reinitialized → worked temporarily
Issue reoccurred; same steps no longer fix it
Created new Snapshot Replication → same failure for this table
Created Transactional Replication with same table → works fine

Issue occurs only in Snapshot Replication

Findings:

• Suspected issue with datetime2(7) and SNAC 11.0 (BCP native mode)
• Snapshot Agent appears to generate multiple .bcp files (parallel processing)

Quick Solution Workaround (working):

To avoid generating multiple .bcp files per table (parallel processing), we added a row filter (WHERE 1 = 1) to the affected articles.

Questions:

Is this a known issue with Snapshot Replication multi-threaded BCP?
Does SNAC 11.0 have limitations with datetime2(7)?
As we cannot add the same filter to every table (due to scale), will upgrading to OLE DB / ODBC Driver 17 or 18 resolve this?
Is there a way to disable BCP partitioning globally (without using row filters)?

Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2026-04-29T15:34:52.3+00:00

I'm afraid that to troubleshoot this, we would need a repro. That is, a scripts that creates a database, a table in the database and populates it with data. Before sharing the script with us, you should verify that you get the issues you mention. Since this started to happen recently, there is probably something in your data that has crept in recently.
Parag Nimbhore 5 Reputation points

2026-04-30T11:25:38.31+00:00
Hi Erland,

Thanks for your response.

We understand the need for a reproducible script. However, we would like to share a few additional observations which may help narrow this down:

The same table replicates successfully using Transactional Replication, but consistently fails under Snapshot Replication.

The issue is resolved when we force Snapshot Agent to generate a single BCP file by adding a row filter (WHERE 1 = 1), which disables parallel BCP generation.

This suggests the issue may be related to multi-threaded BCP file generation rather than purely data content.

The error pattern (EOF / terminator missing / NULL insert) appears consistent with incomplete or misaligned BCP data, possibly due to parallel file generation.

Given this behavior, we are trying to understand:

Whether there are known issues with parallel BCP generation in Snapshot Replication

Or if this could be related to SNAC 11.0 limitations (especially with datetime2(7))

Any guidance on Snapshot Agent behavior, BCP partitioning, or known limitations would be very helpful.

Thanks again for your time
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2026-04-30T13:55:55.76+00:00

This suggests the issue may be related to multi-threaded BCP file generation rather than purely data content.

Then again, you say that "was working fine but recently started failing", which suggests that there is something special in your environment/data that triggers this behaviour. I'm not ruling out that there is a bug of some sort, but I'm not going to set up something out of thin air to troubleshoot. To that end, I need to have something which is known to fail.

Rather than relying on volunteer support in peer-to-peer forum, it may be better to open a support case with Microsoft. When you work with a support engineer you can work directly with the case at hand, and you don't have to spend to time to produce a repro. On the other hand, it can certainly be more costly. Then again, if Microsoft agrees that this is a bug in the product, the case should be of no charge to you. (Although a pesky detail here is that the publisher is a version that is in extended support.)
Akhil Gajavelly 1,830 Reputation points Microsoft External Staff Moderator

2026-05-04T12:03:14.98+00:00

Hi @Parag Nimbhore ,

Just checking in Erland has provided the best possible guidance here given the scenario.

Since this behavior started recently and isn’t reproducible in a controlled script, it does point to an environment/data-specific edge case. At this stage, a Microsoft support case would be the most effective way to investigate further.
https://support.serviceshub.microsoft.com/supportforbusiness/create

If you do identify a minimal repro later, feel free to share the community would be happy to take another look.

Thanks,
Akhil.
Akhil Gajavelly 1,830 Reputation points Microsoft External Staff Moderator

2026-05-12T10:32:52.02+00:00

Hi @Parag Nimbhore ,

Following up to see if you had a chance to review the earlier guidance.

At this point, without a reproducible script or additional diagnostic details, there may not be much more the community can isolate from this side. As suggested earlier, opening a Microsoft support case would likely be the best path for deeper investigation into the environment-specific behavior.

If you later manage to capture a minimal repro or additional error details, please feel free to share them here and the community can revisit the issue.

Thanks,
Akhil.
Matthew Williamson 0 Reputation points

2026-05-14T15:34:54.2+00:00

We've had this same issue with nightly snapshots since 2026-03-30 when our first snapshot replication failed. All of the errors have been consistently unexpected EOF/duplicate PK/field length, suggesting that either a field or line terminator is not being respected. After troubleshooting for about a week, we moved to backup/restore for that situation. In the last month we have had 9 other occurrences of this during transactional replication setups, all coming from different environments, publishers, subscribers, and databases. Our publishers are all fully patched SQL 2019 and the subscribers are varied since they are under the control of other parties, but are SQL Server 2017-2022 as well as Azure SQL Database. The only solution we've found is to move to character encoding, but that seems to be causing massive slowdowns in the transfers. I will try the WHERE 1=1 filter on my next snapshot using native encoding, which I anticipate creating in the next 1-2 days, to see if it is successful and if things speed up. This has been a massive headache for our team as we manage around 100 transactional replication setups with pull subscriptions, most with 5 or more publications. These regularly have to be taken down and set back up as part of normal operations, so we hope to find a more performant solution and thank the community here for this additional suggestion as we have been searching for something specific in our environment to explain the sudden change.
Matthew Williamson 0 Reputation points

2026-05-16T22:13:27.2066667+00:00

As a follow-up to my comment, we've tried the 1=1 filter on all articles in 4 different subscriptions across two environments and it worked for 3 of those in one environment but not for 1 in the other environment. In the failing environment we still received the missing field separator or line terminator error so we went back to character encoding to successfully transfer the initial snapshot for transactional replication. Unfortunately we cannot just upgrade SQL Server for support purposes, so we'll continue to hunt additional workarounds or a common root cause and lower our threshold for backup initialization candidates.
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2026-05-17T09:59:00.5733333+00:00
It's interesting that you can give a specific date for when this issue started to appear. What may have happened on that day?

A update to SQL Server? Unlikely, since you are on SQL 2019 which is out of mainstream support.

A Windows patch that causes the snapshot files to be garbled?

A change in your data which triggers a bug in replication?

The error message seems somewhat inaccurate to me. For native-format bulk copy I would not expect any line or field terminators, but I don't know how their format files look like.

As I said earlier, to investigate this, I would need a repro that demonstrates the issue.

While you may not be able to upgrade in production, I think that you would still be served by trying to repro this in a test environment on SQL 2019 and then upgrade that environment to SQL 2025. If the issue persists in SQL 2025, there is all reason to open a support case with Microsoft.
Matthew Williamson 0 Reputation points

2026-05-18T14:26:36.14+00:00

Hello Erland,

To my knowledge, there were no changes made by our organization on that date, which has made this difficult to track down. We had last patched over a week prior during our monthly maintenance window. We were only able to pinpoint to the date because this particular setup ingested it's snapshot daily and it worked the night before but never worked again afterwards, after multiple attempts. We removed the first table that threw an error and it moved to another table. I think that by the time we got to 7 tables we stopped trying to remove them because it seemed evident that this was likely not a data issue if it is happening in 7 unrelated tables. The following weekend we had two other environments that were seeing the same issues, which does indicate a sweeping change of some sort. After a month of looking at it, we were questioning whether it may be due to a platform change by our cloud provider affecting the network transfer or the storage target.

I hadn't considered trying to get a 2025 instance set up just to reproduce and potentially getting support, but will see if we can pursue that avenue regardless of whether our application is 2025 ready or not. No expectations of investigation on your part, just relieved that we're not the only ones seeing this and wanted to add our experience for anyone in the community that may stumble upon this.
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2026-05-18T20:48:34.8733333+00:00

Had it been a single table, I would lean towards that there is some data issue that took the table over the brink. But seven? Well, if the same funny data has crept into all tables, but I believe you when you say that the tables are unrelated. I'm afraid that I am out of ideas for now.
Akhil Gajavelly 1,830 Reputation points Microsoft External Staff Moderator

2026-06-18T05:41:59.27+00:00

Hi @David Yard ,

Just checking whether you were able to identify any specific table, data pattern, or reproducible scenario that consistently triggers the Snapshot Replication failure. As requested earlier, any additional details that help reproduce the issue would be useful for further investigation.

Thanks,
Akhil.

1 answer

Your answer

Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2026-04-29T15:34:52.3+00:00

I'm afraid that to troubleshoot this, we would need a repro. That is, a scripts that creates a database, a table in the database and populates it with data. Before sharing the script with us, you should verify that you get the issues you mention. Since this started to happen recently, there is probably something in your data that has crept in recently.
Parag Nimbhore 5 Reputation points

2026-04-30T11:25:38.31+00:00

Hi Erland,

Thanks for your response.

We understand the need for a reproducible script. However, we would like to share a few additional observations which may help narrow this down:

The same table replicates successfully using Transactional Replication, but consistently fails under Snapshot Replication.

The issue is resolved when we force Snapshot Agent to generate a single BCP file by adding a row filter (WHERE 1 = 1), which disables parallel BCP generation.

This suggests the issue may be related to multi-threaded BCP file generation rather than purely data content.

The error pattern (EOF / terminator missing / NULL insert) appears consistent with incomplete or misaligned BCP data, possibly due to parallel file generation.

Given this behavior, we are trying to understand:

Whether there are known issues with parallel BCP generation in Snapshot Replication

Or if this could be related to SNAC 11.0 limitations (especially with datetime2(7))

Any guidance on Snapshot Agent behavior, BCP partitioning, or known limitations would be very helpful.

Thanks again for your time
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2026-04-30T13:55:55.76+00:00

This suggests the issue may be related to multi-threaded BCP file generation rather than purely data content.

Then again, you say that "was working fine but recently started failing", which suggests that there is something special in your environment/data that triggers this behaviour. I'm not ruling out that there is a bug of some sort, but I'm not going to set up something out of thin air to troubleshoot. To that end, I need to have something which is known to fail.

Rather than relying on volunteer support in peer-to-peer forum, it may be better to open a support case with Microsoft. When you work with a support engineer you can work directly with the case at hand, and you don't have to spend to time to produce a repro. On the other hand, it can certainly be more costly. Then again, if Microsoft agrees that this is a bug in the product, the case should be of no charge to you. (Although a pesky detail here is that the publisher is a version that is in extended support.)
Akhil Gajavelly 1,830 Reputation points Microsoft External Staff Moderator

2026-05-04T12:03:14.98+00:00

Hi @Parag Nimbhore ,

Just checking in Erland has provided the best possible guidance here given the scenario.

Since this behavior started recently and isn’t reproducible in a controlled script, it does point to an environment/data-specific edge case. At this stage, a Microsoft support case would be the most effective way to investigate further.
https://support.serviceshub.microsoft.com/supportforbusiness/create

If you do identify a minimal repro later, feel free to share the community would be happy to take another look.

Thanks,
Akhil.
Akhil Gajavelly 1,830 Reputation points Microsoft External Staff Moderator

2026-05-12T10:32:52.02+00:00

Hi @Parag Nimbhore ,

Following up to see if you had a chance to review the earlier guidance.

At this point, without a reproducible script or additional diagnostic details, there may not be much more the community can isolate from this side. As suggested earlier, opening a Microsoft support case would likely be the best path for deeper investigation into the environment-specific behavior.

If you later manage to capture a minimal repro or additional error details, please feel free to share them here and the community can revisit the issue.

Thanks,
Akhil.
Matthew Williamson 0 Reputation points

2026-05-14T15:34:54.2+00:00

We've had this same issue with nightly snapshots since 2026-03-30 when our first snapshot replication failed. All of the errors have been consistently unexpected EOF/duplicate PK/field length, suggesting that either a field or line terminator is not being respected. After troubleshooting for about a week, we moved to backup/restore for that situation. In the last month we have had 9 other occurrences of this during transactional replication setups, all coming from different environments, publishers, subscribers, and databases. Our publishers are all fully patched SQL 2019 and the subscribers are varied since they are under the control of other parties, but are SQL Server 2017-2022 as well as Azure SQL Database. The only solution we've found is to move to character encoding, but that seems to be causing massive slowdowns in the transfers. I will try the WHERE 1=1 filter on my next snapshot using native encoding, which I anticipate creating in the next 1-2 days, to see if it is successful and if things speed up. This has been a massive headache for our team as we manage around 100 transactional replication setups with pull subscriptions, most with 5 or more publications. These regularly have to be taken down and set back up as part of normal operations, so we hope to find a more performant solution and thank the community here for this additional suggestion as we have been searching for something specific in our environment to explain the sudden change.
Matthew Williamson 0 Reputation points

2026-05-16T22:13:27.2066667+00:00

As a follow-up to my comment, we've tried the 1=1 filter on all articles in 4 different subscriptions across two environments and it worked for 3 of those in one environment but not for 1 in the other environment. In the failing environment we still received the missing field separator or line terminator error so we went back to character encoding to successfully transfer the initial snapshot for transactional replication. Unfortunately we cannot just upgrade SQL Server for support purposes, so we'll continue to hunt additional workarounds or a common root cause and lower our threshold for backup initialization candidates.
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2026-05-17T09:59:00.5733333+00:00

It's interesting that you can give a specific date for when this issue started to appear. What may have happened on that day?

A update to SQL Server? Unlikely, since you are on SQL 2019 which is out of mainstream support.

A Windows patch that causes the snapshot files to be garbled?

A change in your data which triggers a bug in replication?

The error message seems somewhat inaccurate to me. For native-format bulk copy I would not expect any line or field terminators, but I don't know how their format files look like.

As I said earlier, to investigate this, I would need a repro that demonstrates the issue.

While you may not be able to upgrade in production, I think that you would still be served by trying to repro this in a test environment on SQL 2019 and then upgrade that environment to SQL 2025. If the issue persists in SQL 2025, there is all reason to open a support case with Microsoft.
Matthew Williamson 0 Reputation points

2026-05-18T14:26:36.14+00:00

Hello Erland,

To my knowledge, there were no changes made by our organization on that date, which has made this difficult to track down. We had last patched over a week prior during our monthly maintenance window. We were only able to pinpoint to the date because this particular setup ingested it's snapshot daily and it worked the night before but never worked again afterwards, after multiple attempts. We removed the first table that threw an error and it moved to another table. I think that by the time we got to 7 tables we stopped trying to remove them because it seemed evident that this was likely not a data issue if it is happening in 7 unrelated tables. The following weekend we had two other environments that were seeing the same issues, which does indicate a sweeping change of some sort. After a month of looking at it, we were questioning whether it may be due to a platform change by our cloud provider affecting the network transfer or the storage target.

I hadn't considered trying to get a 2025 instance set up just to reproduce and potentially getting support, but will see if we can pursue that avenue regardless of whether our application is 2025 ready or not. No expectations of investigation on your part, just relieved that we're not the only ones seeing this and wanted to add our experience for anyone in the community that may stumble upon this.
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2026-05-18T20:48:34.8733333+00:00

Had it been a single table, I would lean towards that there is some data issue that took the table over the brink. But seven? Well, if the same funny data has crept into all tables, but I believe you when you say that the tables are unrelated. I'm afraid that I am out of ideas for now.
Akhil Gajavelly 1,830 Reputation points Microsoft External Staff Moderator

2026-06-18T05:41:59.27+00:00

Hi @David Yard ,

Just checking whether you were able to identify any specific table, data pattern, or reproducible scenario that consistently triggers the Snapshot Replication failure. As requested earlier, any additional details that help reproduce the issue would be useful for further investigation.

Thanks,
Akhil.

Answer 1

The WHERE 1=1 workaround confirming single-file BCP resolves it is a strong diagnostic signal — this points to a race condition or data alignment issue in parallel BCP file generation under Snapshot Replication, not a data content problem per se.

To answer your specific questions:

Parallel BCP partitioning — Snapshot Agent splits large articles into multiple .bcp files when MaxBcpThreads or article row counts trigger parallel processing. There is no global switch to disable this without row filters or reducing MaxBcpThreads to 1 in the Snapshot Agent profile, which is worth trying before upgrading drivers.

SNAC 11.0 and datetime2(7) — SNAC has known limitations with high-precision temporal types in native BCP mode. This is a legitimate suspect, especially if the table recently had datetime2 columns added or populated with non-null high-precision values.

Driver upgrade — Switching to ODBC Driver 17 or 18 is the right long-term move and has resolved similar BCP encoding issues for others. It won't disable partitioning but handles datetime2 correctly in native mode.

Immediate thing to test — Set MaxBcpThreads = 1 in the Snapshot Agent profile for this publication and retest without the row filter. If that stabilizes it, you've isolated parallel BCP as the cause and can escalate to Microsoft with that confirmation.

Share via

SQL Snapshot Replication Fails with BCP EOF / NULL Errors (Works in Transactional Replication)

1 answer

Your answer