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.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply