How to fix Error 4871 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: SQL Server Error 4871 – Bulk Load Error While Attempting to Log Errors

Why This Error Happens

Error 4871 occurs when SQL Server’s BULK INSERT or BCP operation encounters row-level errors and attempts to write those errors to the specified error log file, but fails to do so. This typically happens because the SQL Server service account lacks write permissions to the target directory, the specified error file path doesn’t exist, or the file is locked/already exists with restrictive permissions. In some cases, this is compounded by a corrupted source data file causing excessive error logging that the system cannot handle.

Step-by-Step Resolution Guide

Step 1: Verify Service Account Permissions

  • Open Services.msc on Windows Server 2025 / Windows 11 and identify the account running the SQL Server (MSSQLSERVER) service.
  • Navigate to the target error log directory (e.g., C:\SQLErrorLogs\) and right-click → Properties → Security tab.
  • Grant Modify and Write permissions to the SQL Server service account (e.g., NT SERVICE\MSSQLSERVER).
  • Run icacls "C:\SQLErrorLogs" /grant "NT SERVICE\MSSQLSERVER:(OI)(CI)F" in an elevated Command Prompt to apply recursive full control.

Step 2: Validate the Error File Path in Your Query

  • Confirm the path specified in ERRORFILE = 'path' within your BULK INSERT statement exists before execution.
  • Ensure the folder is local or a properly mapped UNC path (avoid mapped drive letters for service accounts, as they often don’t resolve in service context).
  • Example corrected syntax:
  BULK INSERT dbo.TargetTable
  FROM 'C:\Data\import.csv'
  WITH (
      ERRORFILE = 'C:\SQLErrorLogs\import_errors.log',
      FIRSTROW = 2
  );

Step 3: Remove Pre-Existing or Locked Error Files

  • Check if an old .log or .log.error.txt file already exists at the target path from a previous failed run — SQL Server will not overwrite it and will throw 4871 instead.
  • Delete or rename the existing error file manually, or dynamically generate a unique filename per execution using a timestamp variable in your script.
  • Restart the SQL Server Agent service if the file handle remains locked due to a previous incomplete job.

Step 4: Check Disk Space and Volume Health

  • Run Get-PSDrive -PSProvider FileSystem in PowerShell to confirm the target volume isn’t full.
  • Run chkdsk C: /f (schedule on reboot if needed) to rule out file system corruption preventing new file writes.

Alternative or Verification Steps

  • Verify the fix: Re-run the BULK INSERT or BCP command and confirm the error log file is created successfully in the target directory without triggering Error 4871, even if row-level data errors are present.
  • Alternative workaround: If permissions issues persist in a locked-down environment, redirect the ERRORFILE parameter to a temp directory with broader access (e.g., C:\Windows\Temp\) that the SQL Server service account can always write to by default.