| Author |
Topic |
|
Hommer
Aged Yak Warrior
808 Posts |
 Posted - 2004-11-30 : 10:52:09
|
| I understand some of you experts have suggested that every body should stay away from SQL Server Maintenance Plans, but to a lazy and less knowledgeable average Joe, the ease of use and its visual interface are too attractive to turn it down.Now, my plan is running fine on complete backup and transaction backup parts, but Integrity check and Optimization jobs failed. I could not figure out why. Here are the two descriptions from the job history.The job failed. The Job was invoked by Schedule 6 (Schedule 1). The last step to run was step 1 (Step 1).Executed as user: XYZ\Administrator. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.I have another plan on the same server machine but different sql instance, and it does not experience the same failure. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-11-30 : 11:04:46
|
| Well now you know why it's not recommended.I don't run inegrity checks on production databases but on restored backups.Try looking at the log produced by the maint plan. In reporting in the maint plan set it to produce a text file.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-11-30 : 13:35:49
|
| Please tell us which options you selected in the maintenance plan for the integrity and optimization checks.Tara |
|
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2004-12-06 : 11:15:22
|
| The Text file logged the errors as:[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed because the following SET options have incorrect settings:'QUOTED_IDENTIFIER, ARITHABORT'The setting I have for Integrity are:Check database Integrity Inclucde indexesFor Optimizations are:Reorganize data and Index pages Change free space per page percentage to 10%On BOL, I found those two settings. Can I just change them before the maint then change them back, and how?Thanks! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-12-06 : 12:27:09
|
| Are you running service pack 3a?Tara |
|
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2004-12-06 : 12:43:51
|
| I am not sure, and will find out. |
|
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2004-12-06 : 13:47:54
|
| It is sp3, not sp3a. Does it make difference here? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jmckeon
Starting Member
1 Post |
Posted - 2005-11-01 : 07:53:56
|
| I had a similar problem. I had checked the option for the integrity check to make minor repairs. However, in order to run dbcc checkdb ([database], repair_fast), the database must be in single user mode. Try turning off that option if checked. |
|
|
|
|
Integrity check and Optimization jobs failed