March 11, 2026 at 11:56 am
We have two "identical" instances of an ASP.NET web service (or so I have been told), that are connecting to databases that are also reasonably identical, running on separate instances of SQL Server 2022.
For some reason, XACT_ABORT has been found to be set to ON with one of the web services, but not with the other one. The enabling of this setting is causing problems in our code, and so we need to understand where this is being set. I understand that this is a session setting, set as part of the connection/session initialisation. But I am having a hard time tracking down what is causing XACT_ABORT to be enabled by one of the web services.
I have run an extended session trace to capture events where sqlserver.sql_text LIKE '%XACT_ABORT%', but this has not proved fruitful. None of the database objects set XACT_ABORT and there are no logon triggers setting this. How can I pinpoint the culprit that is setting this?
March 11, 2026 at 9:53 pm
as a guess, I think that can be a default on the instance level (maybe database level), so it may be set on the server not in the web service.
Failing that, does the ASP.NET web service call stored procedures for it's work? If so, it may be a workaround to update the SP's to have the expected flags set at start such as having XACT_ABORT OFF.
If the TSQL is hard-coded into the ASP.NET web service, I would look at moving it to stored procedures. One HUGE plus to this approach is that if you need to update the database logic (new table, new column, bug in the SP logic, etc.), you can update it without any service interruption. update the SP and the application doesn't even know or care. If it's hard-coded in the app, you may need to recompile in the worst case and best case stop the web services, update the files, restart the web services resulting in a service interruption.
I would strongly encourage you to put the database logic into stored procedures so you can control things like XACT_ABORT at the SP level and the application level doesn't even notice the change.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 13, 2026 at 8:43 am
I can see some default connection options for the instance, but all of our instances have XACT_ABORT turned off (including the instances concerned).
All of our database code is in stored procedures, functions, views and triggers in the database. There is no T-SQL in our web applications or services. But even so, I have run a search against all the code in our web applications and services, and XACT_ABORT is not mentioned anywhere, including in all our database code.
So the question remains on where this is being set!
March 13, 2026 at 5:25 pm
My next best guess is that something in the connection string is setting it (not likely as I don't think you can set that in there), in the driver (possibly), or the application itself. You put "identical" in quotes which makes me think that you asked and someone told you they were the same but nobody actually verified it.
My next steps would be to spin up a test DB that is a clone of the production one, spin up a new VM that is a clone of the one that sends the XACT_ABORT ON to the server but points to the test DB. I would verify it is sending the XACT_ABORT ON to the test server so you are getting the exact same behavior. Next I'd stop the web services, rename the folder containing the ASP.NET application and copy the one from the known good machine to the TEST one, restart the web services and check if XACT_ABORT ON issue is still happening. If it is, then it's probably driver related or some setting at the database level. If the problem goes away when you copy the folder across, then the 2 apps are not identical. If you prove the apps are not identical, you can use a diff tool (such as winmerge) to compare the files and it'll tell you what's different and it may be an easy fix.
Quick google also indicates that depending on how the code was done can impact things. So even if the applications are functionally equivalent, if one was using Entity Framework (for example) and the other not, or even different versions of EF, EF MAY have tried to be helpful and turned it on implicitly.
Is that XACT_ABORT being turned ON for ALL queries being run against that system, ALL queries from the web app, or just specific stored procedures?
And probably a dumb question but are you 100% certain that the XACT_ABORT ON is what is causing the errors you are seeing? You said that nothing in the XE was useful but did you see anything turning XACT_ABORT ON from that web service? Just wondering if it is ON by default (in which case something is turning it on, either a database setting, logon trigger, something) or if it is OFF by default and something is changing it? AND is it on for everyone or just the web service? If you connect, is it on for you?
But again, I'd test from a test environment so you don't break production worse while trying to fix this. Reproduce the problem in a test environment where you have more control and fewer transactions so you can get a good XE trace without having "fluff" in the trace to sift through then work to resolve the problem.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply