How to set arithabort on at the instance level for all new dbs?

Question

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?

Answer 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.

Answer 2

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.