February 24, 2017 at 2:24 pm
I've been trying to track down an extremely intermittent bug.
One theory I'm working on, is the possibility of a dropped connection between the client and server. Here's the scenario:
1. ASP.NET web application (4.6 framework, if it matters)
2. Connecting to a db server running SQL Server 2008 R2 Enterprise Edition (Windows Authentication).
3. Both servers belong to the same domain and share the same subnet.
4. Web application executes a multi-step sproc (called by ADO.NET) in response to a form submission.
5. The results of steps 1-5 can be seen in the database as successful.
6. The results of steps 6-10 can not be seen as successful, the expected results in the database are not there.
It appears that the sproc quietly exited after step 5, without throwing an exception.
I have not been able to reproduce this bug in a sandbox environment, and it rarely happens in production.
So my question is this:
Supposing there were network issues with the switch, resulting in a dropped connection between the Web Server and the Database Server.
Would the SQL server continue to execute the stored procedure atomically despite broken connection?
OR
Would the SQL server halt execution of the stored procedure immediately, because of the broken connection?
Thanks.
February 25, 2017 at 5:54 am
It's going to halt the execution.
I'd put extended events on the job to monitor for procedure start and finish. If you have an unmatched pair, you've at least established where the problem is, if not why.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 27, 2017 at 9:29 am
Thanks very much.
I'll focus on networking issues.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply