3

I've executed the following query as described here:

ALTER DATABASE [{database_name}] SET ARITHABORT ON WITH NO_WAIT;

SELECT DATABASEPROPERTYEX('{database_name}', 'IsArithmeticAbortEnabled'); returns 1, but sys.dm_exec_sessions shows arithabort set to 0 for all relevant connections. Tracing in Profiler during connection establishment - audit login rows show set arithabort off with no subsequent sets and SELECT @@OPTIONS returns 5432 (ARITHABORT off).

Why isn't this setting taking effect?

Compatibility level is 80. Seeing this across both ODBC and SqlClient connections.

andrew.rockwell
  • 131
  • 4
  • [Getting over written?](https://blog.sqlauthority.com/2018/08/07/sql-server-setting-arithabort-on-for-all-connecting-net-applications/) – S3S Aug 27 '19 at 21:19
  • 1
    Do you see the same behavior if you change the compatibility level to 90 or later? – Dan Guzman Aug 28 '19 at 01:55
  • 1
    @Dan Great question, I don't even have a server anywhere in my reach that supports 80 that I could test out. I think the answer lies somewhere between addressing whatever behavior relies on this setting and simply not using 80 anymore. – Aaron Bertrand Aug 28 '19 at 02:03
  • 1
    @AaronBertrand, I reviewed an unpublished blog post draft of mine and an excerpt is "ARITHABORT is implicitly ON for statements compiled in the context of a database in compatibility level 90 (SQL 2008) or higher database regardless of the session setting, effectively ignoring the ARITHABORT session setting entirely". So with modern ODBC and SqlClient drivers, it's effectively ON by default except in the 80 compatibility level. – Dan Guzman Aug 28 '19 at 02:23
  • @scsimon I've seen conflicting answers on whether the server-level config or database config takes precedence, but it seems that if ARITHABORT was set to ON in either case, that would be the effective setting provided no explict sets to OFF overrode it. – andrew.rockwell Aug 29 '19 at 14:03
  • @DanGuzman Thanks for the question. I do see the same behavior with compatibility_level set to 90 - dm_exec_sessions shows arithabort = 0, the Audit Login row shows arithabort off, and SELECT @@OPTIONS still says 5432. So perhaps "ARITHABORT is implicitly ON" means you can't even tell by querying @@OPTIONS? – andrew.rockwell Aug 30 '19 at 19:40
  • @DanGuzman And corroborating your blog excerpt: "Setting ANSI_WARNINGS to ON implicitly sets ARITHABORT to ON when the database compatibility level is set to 90 or higher. If the database compatibility level is set to 80 or earlier, the ARITHABORT option must be explicitly set to ON." https://docs.microsoft.com/en-us/sql/t-sql/statements/set-arithabort-transact-sql?view=sql-server-2017 – andrew.rockwell Aug 30 '19 at 19:41

1 Answers1

3

Why are you comparing the database arithabort setting to the settings for a connection (which are not scoped to a database)?

If the client connections are explicitly setting ARITHABORT OFF for that connection, then that's their setting. The code you wrote might not be issuing set arithabort off explicitly; it could be the application, middle tier, data layer, ORM, driver, or any helper code you are inheriting from projects etc. You should figure out where this is being set, and remove whatever is setting it, because after compat 80 there is simply no reason to ever set this off.

Anyway, try this example, and notice where the explicit session setting overrides the database setting in every case:

USE Testing;
GO
SET ARITHABORT ON; -- default
ALTER DATABASE Testing SET ARITHABORT OFF;
SELECT arithabort FROM sys.dm_exec_sessions WHERE session_id = @@SPID; -- 1
SET ARITHABORT OFF;
SELECT arithabort FROM sys.dm_exec_sessions WHERE session_id = @@SPID; -- 0
ALTER DATABASE Testing SET ARITHABORT ON;
SELECT arithabort FROM sys.dm_exec_sessions WHERE session_id = @@SPID; -- 0
SET ARITHABORT ON;
SELECT arithabort FROM sys.dm_exec_sessions WHERE session_id = @@SPID; -- 1

This was also true if I created the database, set it to arithabort off, created a login, added the login to the database, set that database to be the default database for the login, and connected directly to that database in the connection string. It still had the setting the application (in this case Azure Data Studio) defined for the session: arithabort on. And all of that in reverse (with the additional step of setting the initial session setting to off).

You could probably use a LOGON TRIGGER to hack sp_configure to set this option for all new connections, but step back for a second. What exactly are you trying to accomplish by attempting to turn arithabort on for all existing connections to this database, and why do you think the database setting should apply to all connections (existing and future), even if they set their own session setting? And why are you still using compatibility level 80?

Aaron Bertrand
  • 175,645
  • 25
  • 380
  • 589
  • I have some confusion around the scope of connections and sessions and accordingly how they interact with server level and database level settings. Are sessions scoped to a database? Any pointers to reading material on this? – andrew.rockwell Aug 29 '19 at 14:01
  • Does the audit login event indicate that clients are explicitly setting arithabort to off? Is that just indicating the effective config for the ...connection, session? I am aware that explicit sets would override the database configuration, it's just not clear to me that explicit sets are happening. – andrew.rockwell Aug 29 '19 at 14:01
  • 1
    Sessions are definitely not scoped to a database. But there are some slight differences depending on whether the session _started_ in a specific database (by using either their default database or an explicit database in the connection string). I am not sure what your audit login event indicates, I don't have enough information, but it's possible the tool or application they are using is applying this set for them. – Aaron Bertrand Aug 30 '19 at 17:13
  • Any suggestions for how to determine where an explicit set would be coming from? Would ODBC and SqlClient be explicitly setting ARITHABORT to off? – andrew.rockwell Aug 30 '19 at 19:48
  • They probably are setting them to off. Srutzky has some ideas for overriding them [here](https://dba.stackexchange.com/a/95090/1186), but other than trace / XE I'm not sure how to dig into exactly where a `set` command came from in the application/driver. – Aaron Bertrand Aug 30 '19 at 20:07