August 2, 2006 at 9:00 am
Due to the high activity on out servers, we have a number of jobs which perform routine maintenance tasks. This past Sunday, July 30, two of these jobs aborted with simialar errors on two different servers.
The first job, which runs at noon againsts all databases on all production servers, executes the following command:
SQLMAINT.EXE -D xxxx -CkDB -CkAl -CkTxtAl -CkCat -UpdOptiStats -10
On two of our servers, when the above job ran against the msdb database, it errored out on the UpdOptiStats command with the following error:
[5] Database msdb: Updating Query Processor Statistics (sampling -10 percent of the data)...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE STATISTICS failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
The second job runs at 4 PM on only one of the two servers and performs a defragmentation of all clustered indexes on all databases as well as an UPDATE STATISTICS command for each table.
This job failed on the UPDATE STATISTICS command with the following error:
UPDATE STATISTICS msdb..sysdbmaintplan_history
(1 rows(s) affected)
Msg 1934, Sev 16: UPDATE STATISTICS failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'. [SQLSTATE 42000]
I used the 'databasepropertyex' function to check the settings for these two options on both of the servers with errors and on serveral other production servers. In all cases, the function returned a value of 0(false), indicating that the option was turned off. Since we do not change these settings after installation, I am assuming that this is the default for these options.
I then ran an UPDATE STATISTICS command against the tables in the msdb database on both of the servers thru Query Analyzer. This worked with no errors.
I then used the xp_cmdshell on both servers to run an 'sqlmaint' with the UpdOptiStats command. This failed with the same error above.
It seems that when I try to run any kind of update statistics thru job, that it fails with the errors listed above. i do not know what else to try. Any suggestions would be appreciated.
August 2, 2006 at 11:42 am
Brian,
CREATE STATISTICS topic in BOL says:
"Computed columns can be specified only if the ARITHABORT and QUOTED_IDENTIFIER database options are set to ON. "
http://msdn2.microsoft.com/en-us/library/ms188038.aspx
So your statistics can not probably be updated if the connection - specific settings for these SET options are OFF. They are probably ON by default when you use Query Analyzer but OFF when you use sqlmaint. Please see the following for the SET option precedence:
http://msdn2.microsoft.com/en-us/library/ms175088.aspx
SET Options That Affect Results
Regards,Yelena Varsha
August 2, 2006 at 12:28 pm
I did read BOL on the options, however, as I stated, we did not change any option on the system databases since we did our install. Also, I do appologize. I should have specified that both of these jobs have been running with no problems for at least 18-24 months and that this is only happening in the msdb database.
August 2, 2006 at 2:11 pm
Why do you need to Update Statistics on msdb?
Regards,Yelena Varsha
August 2, 2006 at 2:45 pm
Technically, we don't, however, the jobs we run use the same commands for all databases. We do run integrity checks on all databases, including master and msdb. The update statistics is just included. We run a defragmentation on the system databses on one server. Again, the update statistics is part of the job.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply