1

How do I set Arithabort 'on' for al new databases created in SQL Server? I know how to set it at the db level but how do I set it at the instance level?

8
  • 1
    Ok, I'll bite... ARITHABORT is a session-scoped option, something that's set to ON or OFF by clients as and when needed - sometimes on a per-statement basis. How are you setting it ON at the database level? Commented Sep 21, 2023 at 21:35
  • 1
    @AlwaysLearning - you can do that here ALTER DATABASE SET - but as most or every client will set it automatically I doubt the value set there is used much/at all in practice. Commented Sep 21, 2023 at 21:44
  • 2
    Why do you need to do this? ARITHABORT is effectively on with most clients due to ANSI_WARNINGS ON and database compatibility greater than 80 (SQL 2000). The database setting is a no-op.
    – Dan Guzman
    Commented Sep 21, 2023 at 21:58
  • 1
    Why are you bothering to set it on? What benefit do you thing setting it ON gives you? Commented Sep 22, 2023 at 2:19
  • 2
    Scripts won't run faster with it on. The database setting is almost entirely (if not entirely) ignored as clients will set it explicitly anyway. Plus for ARITHABORT this is further ignored anyway as Dan explains. If you think scripts run faster because you changed this once and you saw an improvement then this was likely a parameter sniffing issue sommarskog.se/query-plan-mysteries.html - and to be clear if the problem plan had been cached with ARITHABORT ON then you would have seen similar improvements by setting it OFF Commented Sep 22, 2023 at 9:06

2 Answers 2

1

Regarding your comment:

I support thousands of databases and some times I find it set off. I run sp_msforeachdb to set it on for all dbs but I don't want to keep doing this

TL;DR, it is likely you do not need to change the ARITHABORT database default setting at all.

ARITHABORT ON is implicitly set when clients connect with ANSI_WARNINGS ON (as do modern drivers) and the database compatibility level is 90 (SQL 2005) or higher. The default database ARITHABORT ON setting need only be set if you are using an unsupported SQL Server version (which allows database compatibility level 80 or lower) or use a client driver that doesn't set ANSI_WARNINGS ON when connecting, such as the long obsolete DB-Library driver.

Be aware that an implictly set ARITHABORT ON setting is not exposed by DMVs, the bit 64 @@OPTIONS flag, or DBCC USEROPTIONS. These report ARITHABORT OFF even when implicitly set due to ANSI_WARNINGS ON and database compatibility 90+. ARITHABORT ON will be reported only after a T-SQL SET ARITHABORT ON; is executed by the client.

Also, regarding your other comment:

Scripts run faster with it on and its part of MS recommendations to keep it on.

The MS documentation states "Setting ARITHABORT to OFF can negatively impact query optimization, leading to performance issues." This is likely why MS went through the effort of turning it on by default as described above. Consider the only way to turn it off (with modern client drivers and 90+ compatibility) is with an explicit SET ARITHABORT OFF; T-SQL statement in app code or non-default ODBC setting. Furthermore, the effect of ARITHABORT on performance is often misconstrued as the answers to this question discuss since the explicit ARITHABORT session setting is part of the query plan cache key.

0

Each new database that is created in a SQL Server instance is based on the model database of that instance. The model's database setting for Arithmetic Abort Enabled is set to False. This is why each new database is configured with the setting turned off (False).

Answering Your question

How do I set Arithabort 'on' for al new databases created in SQL Server?

Using SQL Server Management Studio

  1. Right-click the model database
  2. Open Properties
  3. Switch to Options
  4. Scroll down until you find Arithmetic Abort Enabled
  5. Change the value from False to True
  6. Click on OK to close the properties window.
  7. Create a new database....

Using SQL Statement

USE [master]
GO
ALTER DATABASE [model] SET ARITHABORT ON WITH NO_WAIT;
GO

....then create a new database.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.