error rebuild index with dbcc

I have an error like this, every time I reindex this table
via db maintenance plan.
Rebuilding indexes for table 'tbJOTrStatusbyWC'
[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, ARITHABORT'.
What does it means this error ?
How to setting 'QUOTED_IDENTIFIER, ARITHABORT'
thx.You most probably have an index on a computed columns or an indexed view. And unfortunately, the
maint plan doesn't execute the needed SET commands to be able to rebuild these indexes. You would
have to create your own (TSQL) job where you execute needed SET commands and then DBCC DBREINDEX (or
DBCC INDEXDEFRAG).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in message
news:3c9401c37b42$a2255740$a601280a@.phx.gbl...
> I have an error like this, every time I reindex this table
> via db maintenance plan.
> Rebuilding indexes for table 'tbJOTrStatusbyWC'
> [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, ARITHABORT'.
> What does it means this error ?
> How to setting 'QUOTED_IDENTIFIER, ARITHABORT'
> thx.|||Tibor is correct in that the maintenance wizard has issues with things such
as computed columns and indexed views. I suggest you create your own
scheduled job to do the DBREINDEX and not use the wizard and all should be
fine.
--
Andrew J. Kelly
SQL Server MVP
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:u$PV9Y2eDHA.3788@.tk2msftngp13.phx.gbl...
> > You are wrong,
> I'm pretty certain that I'm not. But you are welcome to think so, if you
wish.
> I still recommend that you check very closely if you have either indexes
on computed columns or
> indexes on views.
> You do not need any special SET options if you have an PK over several
columns, so it has to be
> something else.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in message
> news:3d9d01c37b5e$e471ee80$a601280a@.phx.gbl...
> > You are wrong,
> >
> > I don't use a computed columns, I use multi colums(JONo,
> > OPID,seqNo) for my primay key, this is the structure.
> >
> > TrackMainID int 4 1
> > JONo int 4 0
> > OPID int 4 0
> > SeqNo int 4 0
> > QtyIN numeric 5 1
> > QtyOut numeric 5 1
> > QtyOpen numeric 5 1
> > WCID int 4 1
> > FirstInDate datetime 8 1
> > LastOutDate datetime 8 1
> >
> > I still don't understand the message : incorrect
> > settings: 'QUOTED_IDENTIFIER, ARITHABORT'
> >
> > I must setting where ?
> >
> >
> >
> > >--Original Message--
> > >You most probably have an index on a computed columns or
> > an indexed view. And unfortunately, the
> > >maint plan doesn't execute the needed SET commands to be
> > able to rebuild these indexes. You would
> > >have to create your own (TSQL) job where you execute
> > needed SET commands and then DBCC DBREINDEX (or
> > >DBCC INDEXDEFRAG).
> > >
> > >--
> > >Tibor Karaszi, SQL Server MVP
> > >Archive at: http://groups.google.com/groups?oi=djq&as
> > ugroup=microsoft.public.sqlserver
> > >
> > >
> > >"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in
> > message
> > >news:3c9401c37b42$a2255740$a601280a@.phx.gbl...
> > >> I have an error like this, every time I reindex this
> > table
> > >> via db maintenance plan.
> > >>
> > >> Rebuilding indexes for table 'tbJOTrStatusbyWC'
> > >> [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, ARITHABORT'.
> > >>
> > >> What does it means this error ?
> > >> How to setting 'QUOTED_IDENTIFIER, ARITHABORT'
> > >>
> > >> thx.
> > >
> > >
> > >.
> > >
>sql

error mssqlserver

Hi all:
The errors always during the maintenance plan, and the last error we have is:
17066; SQL Server Assertion File:<page.cpp>, line=2831
Failed Assertion='spceContig>=Align (spaceNedded)'
What means?
Thank you.
Jose
Did you search KB and Google? Using below search words, I got a number of hits on Google:
SQL 17066
Often Assertion problems are bugs in LSQ server, but then can also be corruption problems in your
database. Run DBCC CHECKDB and also search KB etc and make sure you are current on service pack. If
that doesn't help, consider opening a case with MS Support.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jose Rojas" <JoseRojas@.discussions.microsoft.com> wrote in message
news:54AF2F6A-BA57-4309-BE62-C92BD56C4ABD@.microsoft.com...
> Hi all:
> The errors always during the maintenance plan, and the last error we have is:
> 17066; SQL Server Assertion File:<page.cpp>, line=2831
> Failed Assertion='spceContig>=Align (spaceNedded)'
> What means?
> Thank you.
> Jose

error mssqlserver

Hi all:
The errors always during the maintenance plan, and the last error we have is
:
17066; SQL Server Assertion File:<page.cpp>, line=2831
Failed Assertion='spceContig>=Align (spaceNedded)'
What means'
Thank you.
JoseDid you search KB and Google? Using below search words, I got a number of hi
ts on Google:
SQL 17066
Often Assertion problems are bugs in LSQ server, but then can also be corrup
tion problems in your
database. Run DBCC CHECKDB and also search KB etc and make sure you are curr
ent on service pack. If
that doesn't help, consider opening a case with MS Support.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jose Rojas" <JoseRojas@.discussions.microsoft.com> wrote in message
news:54AF2F6A-BA57-4309-BE62-C92BD56C4ABD@.microsoft.com...
> Hi all:
> The errors always during the maintenance plan, and the last error we have
is:
> 17066; SQL Server Assertion File:<page.cpp>, line=2831
> Failed Assertion='spceContig>=Align (spaceNedded)'
> What means'
> Thank you.
> Jose

error mssqlserver

Hi all:
The errors always during the maintenance plan, and the last error we have is:
17066; SQL Server Assertion File:<page.cpp>, line=2831
Failed Assertion='spceContig>=Align (spaceNedded)'
What means'
Thank you.
JoseDid you search KB and Google? Using below search words, I got a number of hits on Google:
SQL 17066
Often Assertion problems are bugs in LSQ server, but then can also be corruption problems in your
database. Run DBCC CHECKDB and also search KB etc and make sure you are current on service pack. If
that doesn't help, consider opening a case with MS Support.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jose Rojas" <JoseRojas@.discussions.microsoft.com> wrote in message
news:54AF2F6A-BA57-4309-BE62-C92BD56C4ABD@.microsoft.com...
> Hi all:
> The errors always during the maintenance plan, and the last error we have is:
> 17066; SQL Server Assertion File:<page.cpp>, line=2831
> Failed Assertion='spceContig>=Align (spaceNedded)'
> What means'
> Thank you.
> Jose

Error modifying maintenance plans

Using my client (Microsoft SQL Server Management Studio), I am unable to
open/modify maintenance plans. Something is wrong with my client as I'm able
to open them from the server. I've uninstalled Sql2000 client as well.
I installed the Sql2005 client components, SP1 and the DTS 2000 components.
The error states: "Value cannot be null. Parameter name: component
(Sytem.Design)"
also - Microsoft SQL Server Management Studio is unable to load this document.
Has anyone experienced this or know of a solution?
Thanks.
Ron
Ron,
I am having an identical issue. I can modify plans when running Management Studio on the server,
but not from my client computer. I have 2005 Developer Edition on my computer, and I am able to
create and modify plans on my local SQL Server instance without a problem. As my computer is new,
I have never had any SQL Server 2000 components installed.
Any luck finding a solution to this one?
Joe|||No nothing yet. I'm guessing it's something not clearing out in the registry
or since I thought I was the only one, that it might have been my laptop.
Since I have a workaround (go to the server), I haven't been in a rush for a
solution.
"jkiefer580" wrote:

> Ron,
> I am having an identical issue. I can modify plans when running
> Management Studio on the server,
> but not from my client computer. I have 2005 Developer Edition on my
> computer, and I am able to
> create and modify plans on my local SQL Server instance without a
> problem. As my computer is new,
> I have never had any SQL Server 2000 components installed.
> Any luck finding a solution to this one?
> Joe
>
> --
> jkiefer580
>

Error message when trying to save modified maintenance plan?

I have a maintenance plan created that have been running for a few months.
Now I want to modify the plan and selected Modify on the plan and got into
an editor with the different steps. I selected "Edit" on the Backup step and
changed the name of the destination folder and clicked OK in that dialog.
When I try to save the modified maintenance plan, I only get an error dialog
with "No description found".
What is the problem?
OlavHi
I am not sure about the error, you may want to use SQL Profiler to find the
statement causing the error and try to run this manually. The quickest
solution may be to drop the maintenance plan and re-create it from scratch.
John
"Olav" wrote:

> I have a maintenance plan created that have been running for a few months.
> Now I want to modify the plan and selected Modify on the plan and got into
> an editor with the different steps. I selected "Edit" on the Backup step a
nd
> changed the name of the destination folder and clicked OK in that dialog.
> When I try to save the modified maintenance plan, I only get an error dial
og
> with "No description found".
> What is the problem?
> Olav
>
>|||I have the same problem when I try to modify a maintenance plan and then sav
e
the changes I get an error message saying "No description found" - this even
happens when I try to rename the maintenance plan. The changes are not save
d
and the maintenance plan executes ok.
I can create new plans using the wizard but not from scratch. I have
upgraded to SP1 (9.00.2047.00, SP1, Standard Edition running on Win2003 SP1)
Recreating maintenance plans each time I want to modify them is not an optio
n.
Does any one have a solution ?
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> I am not sure about the error, you may want to use SQL Profiler to find th
e
> statement causing the error and try to run this manually. The quickest
> solution may be to drop the maintenance plan and re-create it from scratch
.
> John
> "Olav" wrote:
>|||Hi
Are you seeing this error? http://tinyurl.com/mj88y
John
"Bridgepark" wrote:
[vbcol=seagreen]
> I have the same problem when I try to modify a maintenance plan and then s
ave
> the changes I get an error message saying "No description found" - this ev
en
> happens when I try to rename the maintenance plan. The changes are not sa
ved
> and the maintenance plan executes ok.
> I can create new plans using the wizard but not from scratch. I have
> upgraded to SP1 (9.00.2047.00, SP1, Standard Edition running on Win2003 SP
1)
> Recreating maintenance plans each time I want to modify them is not an opt
ion.
> Does any one have a solution ?
> "John Bell" wrote:
>|||I see the "No description found" message but I have plans without Cleanup
tasks in them. I tried creating a new plan without using the wizard and whe
n
I try and save it I get the error. If I create a plan using the Wizard its
fine (even if it has a clean up task) However if I then try and alter the
plan I get the message.
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Are you seeing this error? http://tinyurl.com/mj88y
> John
> "Bridgepark" wrote:
>|||Hi
You may have to log a call with PSS.
John
"Bridgepark" wrote:
[vbcol=seagreen]
> I see the "No description found" message but I have plans without Cleanup
> tasks in them. I tried creating a new plan without using the wizard and w
hen
> I try and save it I get the error. If I create a plan using the Wizard it
s
> fine (even if it has a clean up task) However if I then try and alter the
> plan I get the message.
> "John Bell" wrote:
>

Error message when trying to save modified maintenance plan?

I have a maintenance plan created that have been running for a few months.
Now I want to modify the plan and selected Modify on the plan and got into
an editor with the different steps. I selected "Edit" on the Backup step and
changed the name of the destination folder and clicked OK in that dialog.
When I try to save the modified maintenance plan, I only get an error dialog
with "No description found".
What is the problem?
OlavHi
I am not sure about the error, you may want to use SQL Profiler to find the
statement causing the error and try to run this manually. The quickest
solution may be to drop the maintenance plan and re-create it from scratch.
John
"Olav" wrote:
> I have a maintenance plan created that have been running for a few months.
> Now I want to modify the plan and selected Modify on the plan and got into
> an editor with the different steps. I selected "Edit" on the Backup step and
> changed the name of the destination folder and clicked OK in that dialog.
> When I try to save the modified maintenance plan, I only get an error dialog
> with "No description found".
> What is the problem?
> Olav
>
>|||I have the same problem when I try to modify a maintenance plan and then save
the changes I get an error message saying "No description found" - this even
happens when I try to rename the maintenance plan. The changes are not saved
and the maintenance plan executes ok.
I can create new plans using the wizard but not from scratch. I have
upgraded to SP1 (9.00.2047.00, SP1, Standard Edition running on Win2003 SP1)
Recreating maintenance plans each time I want to modify them is not an option.
Does any one have a solution ?
"John Bell" wrote:
> Hi
> I am not sure about the error, you may want to use SQL Profiler to find the
> statement causing the error and try to run this manually. The quickest
> solution may be to drop the maintenance plan and re-create it from scratch.
> John
> "Olav" wrote:
> > I have a maintenance plan created that have been running for a few months.
> >
> > Now I want to modify the plan and selected Modify on the plan and got into
> > an editor with the different steps. I selected "Edit" on the Backup step and
> > changed the name of the destination folder and clicked OK in that dialog.
> >
> > When I try to save the modified maintenance plan, I only get an error dialog
> > with "No description found".
> >
> > What is the problem?
> >
> > Olav
> >
> >
> >|||Hi
Are you seeing this error? http://tinyurl.com/mj88y
John
"Bridgepark" wrote:
> I have the same problem when I try to modify a maintenance plan and then save
> the changes I get an error message saying "No description found" - this even
> happens when I try to rename the maintenance plan. The changes are not saved
> and the maintenance plan executes ok.
> I can create new plans using the wizard but not from scratch. I have
> upgraded to SP1 (9.00.2047.00, SP1, Standard Edition running on Win2003 SP1)
> Recreating maintenance plans each time I want to modify them is not an option.
> Does any one have a solution ?
> "John Bell" wrote:
> > Hi
> >
> > I am not sure about the error, you may want to use SQL Profiler to find the
> > statement causing the error and try to run this manually. The quickest
> > solution may be to drop the maintenance plan and re-create it from scratch.
> >
> > John
> >
> > "Olav" wrote:
> >
> > > I have a maintenance plan created that have been running for a few months.
> > >
> > > Now I want to modify the plan and selected Modify on the plan and got into
> > > an editor with the different steps. I selected "Edit" on the Backup step and
> > > changed the name of the destination folder and clicked OK in that dialog.
> > >
> > > When I try to save the modified maintenance plan, I only get an error dialog
> > > with "No description found".
> > >
> > > What is the problem?
> > >
> > > Olav
> > >
> > >
> > >|||I see the "No description found" message but I have plans without Cleanup
tasks in them. I tried creating a new plan without using the wizard and when
I try and save it I get the error. If I create a plan using the Wizard its
fine (even if it has a clean up task) However if I then try and alter the
plan I get the message.
"John Bell" wrote:
> Hi
> Are you seeing this error? http://tinyurl.com/mj88y
> John
> "Bridgepark" wrote:
> > I have the same problem when I try to modify a maintenance plan and then save
> > the changes I get an error message saying "No description found" - this even
> > happens when I try to rename the maintenance plan. The changes are not saved
> > and the maintenance plan executes ok.
> > I can create new plans using the wizard but not from scratch. I have
> > upgraded to SP1 (9.00.2047.00, SP1, Standard Edition running on Win2003 SP1)
> > Recreating maintenance plans each time I want to modify them is not an option.
> >
> > Does any one have a solution ?
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > I am not sure about the error, you may want to use SQL Profiler to find the
> > > statement causing the error and try to run this manually. The quickest
> > > solution may be to drop the maintenance plan and re-create it from scratch.
> > >
> > > John
> > >
> > > "Olav" wrote:
> > >
> > > > I have a maintenance plan created that have been running for a few months.
> > > >
> > > > Now I want to modify the plan and selected Modify on the plan and got into
> > > > an editor with the different steps. I selected "Edit" on the Backup step and
> > > > changed the name of the destination folder and clicked OK in that dialog.
> > > >
> > > > When I try to save the modified maintenance plan, I only get an error dialog
> > > > with "No description found".
> > > >
> > > > What is the problem?
> > > >
> > > > Olav
> > > >
> > > >
> > > >|||Hi
You may have to log a call with PSS.
John
"Bridgepark" wrote:
> I see the "No description found" message but I have plans without Cleanup
> tasks in them. I tried creating a new plan without using the wizard and when
> I try and save it I get the error. If I create a plan using the Wizard its
> fine (even if it has a clean up task) However if I then try and alter the
> plan I get the message.
> "John Bell" wrote:
> > Hi
> >
> > Are you seeing this error? http://tinyurl.com/mj88y
> >
> > John
> >
> > "Bridgepark" wrote:
> >
> > > I have the same problem when I try to modify a maintenance plan and then save
> > > the changes I get an error message saying "No description found" - this even
> > > happens when I try to rename the maintenance plan. The changes are not saved
> > > and the maintenance plan executes ok.
> > > I can create new plans using the wizard but not from scratch. I have
> > > upgraded to SP1 (9.00.2047.00, SP1, Standard Edition running on Win2003 SP1)
> > > Recreating maintenance plans each time I want to modify them is not an option.
> > >
> > > Does any one have a solution ?
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi
> > > >
> > > > I am not sure about the error, you may want to use SQL Profiler to find the
> > > > statement causing the error and try to run this manually. The quickest
> > > > solution may be to drop the maintenance plan and re-create it from scratch.
> > > >
> > > > John
> > > >
> > > > "Olav" wrote:
> > > >
> > > > > I have a maintenance plan created that have been running for a few months.
> > > > >
> > > > > Now I want to modify the plan and selected Modify on the plan and got into
> > > > > an editor with the different steps. I selected "Edit" on the Backup step and
> > > > > changed the name of the destination folder and clicked OK in that dialog.
> > > > >
> > > > > When I try to save the modified maintenance plan, I only get an error dialog
> > > > > with "No description found".
> > > > >
> > > > > What is the problem?
> > > > >
> > > > > Olav
> > > > >
> > > > >
> > > > >

Error message when trying to do a maintenance plan

Hi all,

As far as I can tell I have installed all features of SQL2005 but when I try to setup a maintenance plan I am receiving this message:

TITLE: Microsoft SQL Server Management Studio

The action you attempted to perform on a remote instance of SQL Server has failed because the action requires a SQL Server component that is not installed on the remote computer. To proceed, install SQL Server 2005 Management Tools on the remote computer, and then try again. For more information, see "How to: Install SQL Server 2005 (Setup)" in SQL Server 2005 Books Online, or find the article on MSDN at http://go.microsoft.com/fwlink/?LinkID=57083 . (ObjectExplorer)

For help, click: http://go.microsoft.com/fwlink/?LinkID=57083

BUTTONS:

OK

Any ideas on why this is happening?

first of all you must apply sp2 of Mar

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=19624

There are many problems related to MP solved in this version. Check whether you have installed Integration services and the services is running

Madhu

|||

First things first, have you applied any service pack after you have installed the SQL 2005 components on thsi machine?

If not try as suggested to take care of such issues with maintenance plans.

Error message when runnng Integrity Check in Maintenance Job

Hi,
I have configured my Database maintenance plans which include Integrity checks. On several databases I get the error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode. Is this something I should worry about? I also run a DBCC CheckDB job and everything comes back fine. It looks as though their are
logins still to the database that make this job fail. I can turn off attempt to repair and don't get the errors.
Any thoughts or suggestions on this?
Thanks,
Warren
This is due to selecting the option on your integrity checks
for "attempt to repair any minor problems".
Attempting to repair any database requires it to be in
single user mode and the databases won't be able to always
be put into single user mode. If you have DBCC errors, it
isn't the best idea to have a maintenance plan set to
automatically repair the database. It's better to analyze
the situation further to make sure that hardware problems or
other issues aren't contributing to the database problems.
-Sue
On Wed, 7 Apr 2004 07:16:02 -0700, Warren
<anonymous@.discussions.microsoft.com> wrote:

>Hi,
>I have configured my Database maintenance plans which include Integrity checks. On several databases I get the error:
>[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode. Is this something I should worry about? I also run a DBCC CheckDB job and everything comes back fine. It looks as though their are
logins still to the database that make this job fail. I can turn off attempt to repair and don't get the errors.
>Any thoughts or suggestions on this?
>Thanks,
>Warren
|||Thanks Sue! I am not receiving any dbcc checkdb errors.
|||Then you're fine Warren. It's better to just monitor the
results of the DBCCs yourself so you would just want to
remove the option to repair on your integrity checks.
-Sue
On Wed, 7 Apr 2004 11:21:12 -0700, Warren
<anonymous@.discussions.microsoft.com> wrote:

>Thanks Sue! I am not receiving any dbcc checkdb errors.
|||You shouldn't automatically repair errors. You should always do root-cause
analysis of any errors returned by the integrity checks as they're usually
an indication that your hardware has a problem.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Warren" <anonymous@.discussions.microsoft.com> wrote in message
news:68146DFC-BB28-4D0F-B4D1-BA9358EA7A1F@.microsoft.com...
> Hi,
> I have configured my Database maintenance plans which include Integrity
checks. On several databases I get the error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode. Is this something I
should worry about? I also run a DBCC CheckDB job and everything comes back
fine. It looks as though their are logins still to the database that make
this job fail. I can turn off attempt to repair and don't get the errors.
> Any thoughts or suggestions on this?
> Thanks,
> Warren
|||I have been having this same problem. Thanks for this very helpful advice. It is good to see that the experts are willing to help out us users!
Byron Vriend, R.P.F.
GIS Analyst
Weldwood of Canada
Hinton Division
Hinton, AB, Canada
"Paul S Randal [MS]" wrote:

> You shouldn't automatically repair errors. You should always do root-cause
> analysis of any errors returned by the integrity checks as they're usually
> an indication that your hardware has a problem.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Warren" <anonymous@.discussions.microsoft.com> wrote in message
> news:68146DFC-BB28-4D0F-B4D1-BA9358EA7A1F@.microsoft.com...
> checks. On several databases I get the error:
> processed. Database needs to be in single user mode. Is this something I
> should worry about? I also run a DBCC CheckDB job and everything comes back
> fine. It looks as though their are logins still to the database that make
> this job fail. I can turn off attempt to repair and don't get the errors.
>
>

Error message when runnng Integrity Check in Maintenance Job

Hi,
I have configured my Database maintenance plans which include Integrity chec
ks. On several databases I get the error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement
not processed. Database needs to be in single user mode. Is this something
I should worry about? I also run a DBCC CheckDB job and everything comes ba
ck fine. It looks as though their are
logins still to the database that make this job fail. I can turn off attemp
t to repair and don't get the errors.
Any thoughts or suggestions on this?
Thanks,
WarrenThis is due to selecting the option on your integrity checks
for "attempt to repair any minor problems".
Attempting to repair any database requires it to be in
single user mode and the databases won't be able to always
be put into single user mode. If you have DBCC errors, it
isn't the best idea to have a maintenance plan set to
automatically repair the database. It's better to analyze
the situation further to make sure that hardware problems or
other issues aren't contributing to the database problems.
-Sue
On Wed, 7 Apr 2004 07:16:02 -0700, Warren
<anonymous@.discussions.microsoft.com> wrote:

>Hi,
>I have configured my Database maintenance plans which include Integrity che
cks. On several databases I get the error:
>[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not pro
cessed. Database needs to be in single user mode. Is this something I should worry
about? I also run a DBCC CheckDB job and everything comes back fine. It looks as t
hough their are
logins still to the database that make this job fail. I can turn off attempt to repair and
don't get the errors.
>Any thoughts or suggestions on this?
>Thanks,
>Warren|||Thanks Sue! I am not receiving any dbcc checkdb errors.|||Then you're fine Warren. It's better to just monitor the
results of the DBCCs yourself so you would just want to
remove the option to repair on your integrity checks.
-Sue
On Wed, 7 Apr 2004 11:21:12 -0700, Warren
<anonymous@.discussions.microsoft.com> wrote:

>Thanks Sue! I am not receiving any dbcc checkdb errors.|||You shouldn't automatically repair errors. You should always do root-cause
analysis of any errors returned by the integrity checks as they're usually
an indication that your hardware has a problem.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Warren" <anonymous@.discussions.microsoft.com> wrote in message
news:68146DFC-BB28-4D0F-B4D1-BA9358EA7A1F@.microsoft.com...
> Hi,
> I have configured my Database maintenance plans which include Integrity
checks. On several databases I get the error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not[/c
olor]
processed. Database needs to be in single user mode. Is this something I
should worry about? I also run a DBCC CheckDB job and everything comes back
fine. It looks as though their are logins still to the database that make
this job fail. I can turn off attempt to repair and don't get the errors.
> Any thoughts or suggestions on this?
> Thanks,
> Warren|||I have been having this same problem. Thanks for this very helpful advice.
It is good to see that the experts are willing to help out us users!
Byron Vriend, R.P.F.
GIS Analyst
Weldwood of Canada
Hinton Division
Hinton, AB, Canada
"Paul S Randal [MS]" wrote:

> You shouldn't automatically repair errors. You should always do root-cause
> analysis of any errors returned by the integrity checks as they're usually
> an indication that your hardware has a problem.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Warren" <anonymous@.discussions.microsoft.com> wrote in message
> news:68146DFC-BB28-4D0F-B4D1-BA9358EA7A1F@.microsoft.com...
> checks. On several databases I get the error:
> processed. Database needs to be in single user mode. Is this something I
> should worry about? I also run a DBCC CheckDB job and everything comes ba
ck
> fine. It looks as though their are logins still to the database that make
> this job fail. I can turn off attempt to repair and don't get the errors.
>
>

Error message when runnng Integrity Check in Maintenance Job

Hi
I have configured my Database maintenance plans which include Integrity checks. On several databases I get the error
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode. Is this something I should worry about? I also run a DBCC CheckDB job and everything comes back fine. It looks as though their are logins still to the database that make this job fail. I can turn off attempt to repair and don't get the errors.
Any thoughts or suggestions on this
Thanks
WarrenThis is due to selecting the option on your integrity checks
for "attempt to repair any minor problems".
Attempting to repair any database requires it to be in
single user mode and the databases won't be able to always
be put into single user mode. If you have DBCC errors, it
isn't the best idea to have a maintenance plan set to
automatically repair the database. It's better to analyze
the situation further to make sure that hardware problems or
other issues aren't contributing to the database problems.
-Sue
On Wed, 7 Apr 2004 07:16:02 -0700, Warren
<anonymous@.discussions.microsoft.com> wrote:
>Hi,
>I have configured my Database maintenance plans which include Integrity checks. On several databases I get the error:
>[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode. Is this something I should worry about? I also run a DBCC CheckDB job and everything comes back fine. It looks as though their are logins still to the database that make this job fail. I can turn off attempt to repair and don't get the errors.
>Any thoughts or suggestions on this?
>Thanks,
>Warren|||Then you're fine Warren. It's better to just monitor the
results of the DBCCs yourself so you would just want to
remove the option to repair on your integrity checks.
-Sue
On Wed, 7 Apr 2004 11:21:12 -0700, Warren
<anonymous@.discussions.microsoft.com> wrote:
>Thanks Sue! I am not receiving any dbcc checkdb errors.|||You shouldn't automatically repair errors. You should always do root-cause
analysis of any errors returned by the integrity checks as they're usually
an indication that your hardware has a problem.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Warren" <anonymous@.discussions.microsoft.com> wrote in message
news:68146DFC-BB28-4D0F-B4D1-BA9358EA7A1F@.microsoft.com...
> Hi,
> I have configured my Database maintenance plans which include Integrity
checks. On several databases I get the error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode. Is this something I
should worry about? I also run a DBCC CheckDB job and everything comes back
fine. It looks as though their are logins still to the database that make
this job fail. I can turn off attempt to repair and don't get the errors.
> Any thoughts or suggestions on this?
> Thanks,
> Warren|||I have been having this same problem. Thanks for this very helpful advice. It is good to see that the experts are willing to help out us users!
Byron Vriend, R.P.F.
GIS Analyst
Weldwood of Canada
Hinton Division
Hinton, AB, Canada
"Paul S Randal [MS]" wrote:
> You shouldn't automatically repair errors. You should always do root-cause
> analysis of any errors returned by the integrity checks as they're usually
> an indication that your hardware has a problem.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Warren" <anonymous@.discussions.microsoft.com> wrote in message
> news:68146DFC-BB28-4D0F-B4D1-BA9358EA7A1F@.microsoft.com...
> > Hi,
> >
> > I have configured my Database maintenance plans which include Integrity
> checks. On several databases I get the error:
> >
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode. Is this something I
> should worry about? I also run a DBCC CheckDB job and everything comes back
> fine. It looks as though their are logins still to the database that make
> this job fail. I can turn off attempt to repair and don't get the errors.
> >
> > Any thoughts or suggestions on this?
> >
> > Thanks,
> >
> > Warren
>
>

Error Message on optimization plan in sqlserver 2000

I am not a SQL Server DBA but trying to figure this out,
The 'OptimizationsJob For Db Maintenance Plan' is failing with an
error -- When I checked the error message the index rebuild is failing
for one of the tables with an error of
[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, ARITHABORT'.
This table "t" exists in both our Dev and Prod Servers but I see this
issue only in the Prod server (the prod server has more data)...
I ran this query:
select * from sysobjects where type = 'V'
and objectproperty(id, 'IsIndexed') = 1
for any indexed views and returns null
What are my options?
TIA,
MagWhat about computed columns. If memory serves me, it is enough to have statistics on a computed
column for this error to happen.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<aspiring22@.gmail.com> wrote in message news:1175025577.736504.80620@.b75g2000hsg.googlegroups.com...
>I am not a SQL Server DBA but trying to figure this out,
> The 'OptimizationsJob For Db Maintenance Plan' is failing with an
> error -- When I checked the error message the index rebuild is failing
> for one of the tables with an error of
> [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, ARITHABORT'.
>
> This table "t" exists in both our Dev and Prod Servers but I see this
> issue only in the Prod server (the prod server has more data)...
> I ran this query:
> select * from sysobjects where type = 'V'
> and objectproperty(id, 'IsIndexed') = 1
> for any indexed views and returns null
>
> What are my options?
>
> TIA,
> Mag
>|||On Mar 27, 3:09 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> What about computed columns. If memory serves me, it is enough to have statistics on a computed
> column for this error to happen.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://www.solidqualitylearning.com/
>
> <aspirin...@.gmail.com> wrote in messagenews:1175025577.736504.80620@.b75g2000hsg.googlegroups.com...
> >I am not a SQL Server DBA but trying to figure this out,
> > The 'OptimizationsJob For Db Maintenance Plan' is failing with an
> > error -- When I checked the error message the index rebuild is failing
> > for one of the tables with an error of
> > [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, ARITHABORT'.
> > This table "t" exists in both our Dev and Prod Servers but I see this
> > issue only in the Prod server (the prod server has more data)...
> > I ran this query:
> > select * from sysobjects where type = 'V'
> > and objectproperty(id, 'IsIndexed') = 1
> > for any indexed views and returns null
> > What are my options?
> > TIA,
> > Mag- Hide quoted text -
> - Show quoted text -
There is no computed columns on this table and how come the DEV/Test
instance is not showing the same exception...|||> There is no computed columns on this table and how come the DEV/Test
> instance is not showing the same exception...
That could have been because auto-statistics could have been created on the dev server but not the
prod server.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<aspiring22@.gmail.com> wrote in message
news:1175029664.750939.316180@.p77g2000hsh.googlegroups.com...
> On Mar 27, 3:09 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> What about computed columns. If memory serves me, it is enough to have statistics on a computed
>> column for this error to happen.
>> --
>> Tibor Karaszi, SQL Server
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://www.solidqualitylearning.com/
>>
>> <aspirin...@.gmail.com> wrote in
>> messagenews:1175025577.736504.80620@.b75g2000hsg.googlegroups.com...
>> >I am not a SQL Server DBA but trying to figure this out,
>> > The 'OptimizationsJob For Db Maintenance Plan' is failing with an
>> > error -- When I checked the error message the index rebuild is failing
>> > for one of the tables with an error of
>> > [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, ARITHABORT'.
>> > This table "t" exists in both our Dev and Prod Servers but I see this
>> > issue only in the Prod server (the prod server has more data)...
>> > I ran this query:
>> > select * from sysobjects where type = 'V'
>> > and objectproperty(id, 'IsIndexed') = 1
>> > for any indexed views and returns null
>> > What are my options?
>> > TIA,
>> > Mag- Hide quoted text -
>> - Show quoted text -
> There is no computed columns on this table and how come the DEV/Test
> instance is not showing the same exception...
>

Error Message on optimization plan in sqlserver 2000

I am not a SQL Server DBA but trying to figure this out,
The 'OptimizationsJob For Db Maintenance Plan' is failing with an
error -- When I checked the error message the index rebuild is failing
for one of the tables with an error of
[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, ARITHABORT'.
This table "t" exists in both our Dev and Prod Servers but I see this
issue only in the Prod server (the prod server has more data)...
I ran this query:
select * from sysobjects where type = 'V'
and objectproperty(id, 'IsIndexed') = 1
for any indexed views and returns null
What are my options?
TIA,
Mag
What about computed columns. If memory serves me, it is enough to have statistics on a computed
column for this error to happen.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<aspiring22@.gmail.com> wrote in message news:1175025577.736504.80620@.b75g2000hsg.googlegro ups.com...
>I am not a SQL Server DBA but trying to figure this out,
> The 'OptimizationsJob For Db Maintenance Plan' is failing with an
> error -- When I checked the error message the index rebuild is failing
> for one of the tables with an error of
> [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, ARITHABORT'.
>
> This table "t" exists in both our Dev and Prod Servers but I see this
> issue only in the Prod server (the prod server has more data)...
> I ran this query:
> select * from sysobjects where type = 'V'
> and objectproperty(id, 'IsIndexed') = 1
> for any indexed views and returns null
>
> What are my options?
>
> TIA,
> Mag
>
|||On Mar 27, 3:09 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> What about computed columns. If memory serves me, it is enough to have statistics on a computed
> column for this error to happen.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://www.solidqualitylearning.com/
>
> <aspirin...@.gmail.com> wrote in messagenews:1175025577.736504.80620@.b75g2000hsg.go oglegroups.com...
>
>
>
>
> - Show quoted text -
There is no computed columns on this table and how come the DEV/Test
instance is not showing the same exception...
|||> There is no computed columns on this table and how come the DEV/Test
> instance is not showing the same exception...
That could have been because auto-statistics could have been created on the dev server but not the
prod server.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<aspiring22@.gmail.com> wrote in message
news:1175029664.750939.316180@.p77g2000hsh.googlegr oups.com...
> On Mar 27, 3:09 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> There is no computed columns on this table and how come the DEV/Test
> instance is not showing the same exception...
>

Error Message on optimization plan in sqlserver 2000

I am not a SQL Server DBA but trying to figure this out,
The 'OptimizationsJob For Db Maintenance Plan' is failing with an
error -- When I checked the error message the index rebuild is failing
for one of the tables with an error of
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft]
[ODBC SQL Server Driver][SQL Server]DBCC failed because the followin
g
SET options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
This table "t" exists in both our Dev and Prod Servers but I see this
issue only in the Prod server (the prod server has more data)...
I ran this query:
select * from sysobjects where type = 'V'
and objectproperty(id, 'IsIndexed') = 1
for any indexed views and returns null
What are my options?
TIA,
MagWhat about computed columns. If memory serves me, it is enough to have stati
stics on a computed
column for this error to happen.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<aspiring22@.gmail.com> wrote in message news:1175025577.736504.80620@.b75g2000hsg.googlegroup
s.com...
>I am not a SQL Server DBA but trying to figure this out,
> The 'OptimizationsJob For Db Maintenance Plan' is failing with an
> error -- When I checked the error message the index rebuild is failing
> for one of the tables with an error of
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft]
> [ODBC SQL Server Driver][SQL Server]DBCC failed because the follow
ing
> SET options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
>
> This table "t" exists in both our Dev and Prod Servers but I see this
> issue only in the Prod server (the prod server has more data)...
> I ran this query:
> select * from sysobjects where type = 'V'
> and objectproperty(id, 'IsIndexed') = 1
> for any indexed views and returns null
>
> What are my options?
>
> TIA,
> Mag
>|||On Mar 27, 3:09 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> What about computed columns. If memory serves me, it is enough to have sta
tistics on a computed
> column for this error to happen.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asph
ttp://www.solidqualitylearning.com/
>
> <aspirin...@.gmail.com> wrote in messagenews:1175025577.736504.80620@.b75g20
00hsg.googlegroups.com...
>
>
>
>
>
>
>
>
> - Show quoted text -
There is no computed columns on this table and how come the DEV/Test
instance is not showing the same exception...|||> There is no computed columns on this table and how come the DEV/Test
> instance is not showing the same exception...
That could have been because auto-statistics could have been created on the
dev server but not the
prod server.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<aspiring22@.gmail.com> wrote in message
news:1175029664.750939.316180@.p77g2000hsh.googlegroups.com...
> On Mar 27, 3:09 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> There is no computed columns on this table and how come the DEV/Test
> instance is not showing the same exception...
>

Error Message

I have a Maintenance Plan set to run every night that Optimizes the databases, runs an Integrity check and then backs them up. Each job is staggered to run 1 hour apart. It has been running fine for months and now I get the following intermittent error during the "Reorganize data and index pages" part:
[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, ARITHABORT'.

I know what database it is failing on but I run dbcc checkdb and find no errors. From what I have found on the web this error pertains to an index problem. The only indices on this database reside in the system tables. I have dropped the database and restored it but no help there. Any ideas?

Thanks
DonDo you have indexes on views or computed columns ? If so,
you will need to run dbcc dbreindex or indexdefrag. This occurs because sql server agent does not (by default) set arithabort and quoted_identifier on. So, you will need to create a job that sets these:

set arithabort on
set quoted_identifier on
dbcc checktable(tablename)|||I don't have any indexes on views or any user tables, just the default system tables with a fresh database. I'll look into the arithabort setting.
Thanks
Don|||Have you tried the dbcc commands ?