SQL Server Performance Forum – Threads Archive

Rebuild Index Maintenance Plan Fails

I created a Maintenance plan for my SQL 2000 database that is supposed to "Reorganize data and index pages" and "Change free space per page percentage to 10%". It keeps failing, and the error message I get is:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’. I looked for this in Knowledge Base, and I found article KB301292, which states:
This behavior is by design. DBCC CHECKDB, DBREINDEX and CHECKTABLE require the following SET options if an index on a computed column exists in the database or table:
ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS must be set to ON.
NUMERIC_ROUNDABORT must be set to OFF. The error message is more likely to occur if the CHECKDB or CHECKTABLE is being scheduled from a SQL Server Agent job or from an Integrity Check in a Database Maintenance Plan. This is because by default the SQL Server Agent does not set QUOTED_IDENTIFIER or ARITHABORT. To schedule a DBCC CHECKTABLE or CHECKDB integrity check on the database, you must create a SQL Server Agent Job and in the Transact-SQL command you must add the needed SET OPTIONS as in the following example: SET ARITHABORT ON
SET QUOTED_IDENTIFIER ON
DBCC CHECKTABLE(mytable)
go
————————————————————————————– How do I write a T-SQL statement that is equivalent to rebuilding indexes for my entire database, so that I can add the SET QUOTED_IDENTIFIER statement? This was also happening to my Integrity Checks Maintenance Plan, but I was able to successfully rewrite it in T-SQL. Thanks!
Queena [:I]

You can find in Forumhttp://www.sql-server-performance.com/forum/forum.asp?FORUM_ID=11
severals sp to run DBreindex of all database and with % of defrag do yo want.
I use one from Thomas and work fine.
Luis Martin
Moderator
SQL-Server-Performance.com The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

Thanks for the info, Luis! Queena

Take help from this KBAhttp://support.microsoft.com/default.aspx?scid=kb;en-us;301292&Product=sql to troubleshoot the issue. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>