Sql-server – ALTER DATABASE SET ARITHABORT not taking effect

sql server

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.

Best Answer

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?

Related Question