A mystery staring arithabort.
I was coding along one day, working on rolling out some monitoring for a client—monitoring that I had used for previous clients. The code was pretty straightforward and addressed a concern for the client. Having used it for several previous clients, I felt pretty confident in the code. The main purpose was simply to audit index definition changes. It was rolled into a stored procedure and designed to be deployed to a “DBA” database.
With confidence pretty steady on this code, I deployed the stored procedure along with all of the supporting tables and structures. Then I proceeded to deploy the SQL Agent job that controls the execution of the stored procedure. When I deploy a job I like to test the job to ensure it will work. As luck would have it, the job failed on the first test run. I instantly became baffled. Here is the error message.
Job Name DBA – Track Index Changes
Step Name exec proc
Duration 00:00:08 Sql Severity 16
Sql Message ID 1934
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: sa.
INSERT failed because the following SET options have incorrect settings:
‘ARITHABORT’.
Verify that SET options are correct for use with
indexed views and/or indexes on computed columns
and/or filtered indexes
and/or query notifications
and/or XML data type methods
and/or spatial index operations.
[SQLSTATE 42000] (Error 1934). The step failed.
Immediately I started looking at my creation scripts for the tables. Nope, no XML indexes, no spatial indexes, no filtered indexes, no indexes on computed columns (not even any computed columns), and no query notifications.
Next I started checking the database settings. Maybe the vendor for the application this client bought had set something for all of the databases regarding ARITHABORT.
Querying sys.databases, we could easily see that ARITHABORT is not enabled at the database level (just like previous implementations). Well, this is a minor head scratcher at this point. The code works in other environments, the database setting is the same as other environments. I guess I could try setting ARITHABORT within the stored procedure and then re-test.
When I add the last line, “SET ARITHABORT ON;” to this stored procedure and then rerun the job it runs without any error. It is a simple fix but the story doesn’t end there.
After making that change, I decided to go another round with the stored procedure and the ARITHABORT setting. I removed it in the next round and decided to test the stored procedure directly. Running the stored procedure in Management Studio with or without the ARITHABORT setting produces the same result. The result is that both work as desired without any error. For giggles, I ran the job again and discovered that the job still fails. In the end, it appears to be something that the SQL Agent is setting as a part of its connection back to the database in this case.
In addition to this minor nuisance, you saw that the error outlines several possible causes for failure with regards to ARITHABORT. One that I found that can be of big concern is with filtered indexes. Check your connection settings from your application when dealing with any filtered indexes. Filtered Indexes have produced this error in quite a few cases I have been asked to help fix. Just a thought for something you should monitor and check should you run into this error or if you are considering the use of filtered indexes.
Putting a Bow on it…
In conclusion, this can be a short-term head scratcher. Pay close attention to what has changed in the environment. Test alternatives. And check those connection strings.
