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

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 ?