Step: Step 3) SomeStepName
Message: Executed as user: SomeDomain\SomeServiceAccount. Starting copy... SQLState = S1000, NativeError = 0 Error = [Microsoft][ODBC Driver 11 for SQL Server]I/O error while writing BCP data-file 19 rows copied. Network packet size (bytes): 4096 Clock Time (ms.) Total : 1 Average : (19000.00 rows per sec.). Process Exit Code 1. The step failed.
Time: 2015-10-06 08:16:20
This was very interesting since the file we were writing had only 19 rows and this was very small, less than a kilobyte. So how do you investigate what the error is? It could be all kinds of things:
Permissions on the drive
Problems with the drive
Issue with a format file
If you do a search you will get all kinds of things back. However the first thing you need to run is the following
Here is what I saw after running xp_fixeddrives
drive MB free C 235644 D 1479234 E 10123 F 10123 G 10123 L 10123 S 117706 T 10123 Z 0
Take a look at the Z drive do you see that? The Z drive is full, there is no space left. It would be nice if the error message indicated that it couldn't write to the file because there was no space left. In a production environment this should not happen because hopefully you have something in place that sends out an alert if the drive is 90% full.
So, if you ever get this error, first thing you have to do is if you have space left on the drive.
I also want to point out that the xp_fixeddrives stored procedure is undocumented so there is no guarantee it will be available in a future version of SQL Server.