ASYNC_NETWORK_IO wait causing data compare to fail
david.duffett
Posts: 30
We run a continuous integration process using SQL Data Compare Pro. This morning our build tasks started to fail when comparing one particular table (the only one with a data difference).
Looking at Activity Monitor the query SQL Data Compare is running has a wait type of ASYNC_NETWORK_IO with a Wait Resource of "External ExternalResource=ASYNC_NETWORK_IO".
No configuration changes have been made to the server, no changes to the SQL Data Compare project or the structure of the tables in question.
Excluding this table makes the task succeed. Data Compare returns an error code 126:
[09:29:53]: Registering databases
[09:30:04]: Mapping
[09:30:04]: Comparing databases
[09:35:03]: Error: The Paser encountered an error while parsing the Script Folder. The file
[09:35:03]: that was being read was
[09:35:03]: E:\TeamCity.BuildAgent\buildAgent\work\620efdeeceef5d8b\trunk\Data\dbo.tbMessage_Template_Data.sql
[09:35:06]: SQLDataCompare failed with error code 126
This error seems to indicate a problem with parsing the file, however Activity Monitor leads me to believe its the ASYNC_NETWORK_IO that is the issue.
Any thoughts?
Looking at Activity Monitor the query SQL Data Compare is running has a wait type of ASYNC_NETWORK_IO with a Wait Resource of "External ExternalResource=ASYNC_NETWORK_IO".
No configuration changes have been made to the server, no changes to the SQL Data Compare project or the structure of the tables in question.
Excluding this table makes the task succeed. Data Compare returns an error code 126:
[09:29:53]: Registering databases
[09:30:04]: Mapping
[09:30:04]: Comparing databases
[09:35:03]: Error: The Paser encountered an error while parsing the Script Folder. The file
[09:35:03]: that was being read was
[09:35:03]: E:\TeamCity.BuildAgent\buildAgent\work\620efdeeceef5d8b\trunk\Data\dbo.tbMessage_Template_Data.sql
[09:35:06]: SQLDataCompare failed with error code 126
This error seems to indicate a problem with parsing the file, however Activity Monitor leads me to believe its the ASYNC_NETWORK_IO that is the issue.
Any thoughts?
Comments
Any thoughts on how they could have become corrupt? They are only ever updated by SQL Data Compare Pro.
The wait type of ASYNC_NETWORK_IO would indicate that there is some other process running which the SQL Server instance is having trouble keeping up with and therefore forcing SQL Data Compare to wait. I don't believe that this is a corruption par se, at least that is what I have found in online documentation for this wait type.
http://blogs.msdn.com/b/joesack/archive ... orkio.aspx
As far as I have been able to tell, this is not a known issue for SQL Data Compare. If you are able to recreate the error, then it would be very useful to see the generated log file. Unfortunately this requires logging to be turned on beforehand, please refer to this link:
http://www.red-gate.com/supportcenter/C ... ta_Compare\knowledgebase\logging_and_log_files.htm&p=SQL%20Data%20Compare
If you are able to generate the log file, then please forward it to support@red-gate.com and reference this forum post.
Rolling back the data files for this particular table reproduces the problem - all tables UP UNTIL this one go through fine, then it stalls with the ASYNC_NETWORK_IO wait until I guess SQL Data Compare times out.
I've enabled logging and will email you the log file - hopefully you'll be able to find the problem and fix in case it happens to anyone else in the future.
David