Integrity checks.....Attempt to repair any minor problems

Win2000sp4. SQL2k sp3
One server I have the option selected to Attempt to repair any minor
problems is checked on the Integrity checks. I know it is trying to repair
something due to the fact it is trying to place in single user mode and
can't due to connections.
My problem is that I have ran dbcc checkdb and no errors were found. So
what is exactly is it detecting and how do I detect the same? Certain trace
flags or option need to be set with dbcc checkdb?
TIA
CDThe DBCC CHECKDB has a parameter about how to repair the
error if it finds any. You can check BOL for details.
>--Original Message--
>Win2000sp4. SQL2k sp3
>One server I have the option selected to Attempt to
repair any minor
>problems is checked on the Integrity checks. I know it
is trying to repair
>something due to the fact it is trying to place in single
user mode and
>can't due to connections.
>My problem is that I have ran dbcc checkdb and no errors
were found. So
>what is exactly is it detecting and how do I detect the
same? Certain trace
>flags or option need to be set with dbcc checkdb?
>TIA
>CD
>
>.
>|||Remove the option to attempt to repair any minor
problems...it just causes problems with maintenance plans
and is not a good idea anyway. If you have errors in a dbcc
checkdb, you want to investigate the errors, find what the
root of the problem is and address the issue or issues
yourself before you would even consider using this option.
-Sue
On Wed, 12 Nov 2003 15:15:54 -0600, "CD"
<mcdye1@.hotmail.nospam.com> wrote:
>Win2000sp4. SQL2k sp3
>One server I have the option selected to Attempt to repair any minor
>problems is checked on the Integrity checks. I know it is trying to repair
>something due to the fact it is trying to place in single user mode and
>can't due to connections.
>My problem is that I have ran dbcc checkdb and no errors were found. So
>what is exactly is it detecting and how do I detect the same? Certain trace
>flags or option need to be set with dbcc checkdb?
>TIA
>CD
>|||Thanks for the replies, but you have missed the point. dbcc checkdb found
no errors.
The following leads me to believe that something was detected. But What?
How to confirm? Add switches to DBCC command? trace flag?
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC SQL
Server Driver][SQL Server]Database state cannot be changed while other users
are using the database 'BaseQuery'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement
failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
[1] Database BaseQuery: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL
Server Driver][SQL Server]Repair statement not processed. Database needs to
be in single user mode.
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode.
** Execution Time: 0 hrs, 0 mins, 7 secs **
> On Wed, 12 Nov 2003 15:15:54 -0600, "CD"
> <mcdye1@.hotmail.nospam.com> wrote:
> >Win2000sp4. SQL2k sp3
> >
> >One server I have the option selected to Attempt to repair any minor
> >problems is checked on the Integrity checks. I know it is trying to
repair
> >something due to the fact it is trying to place in single user mode and
> >can't due to connections.
> >
> >My problem is that I have ran dbcc checkdb and no errors were found. So
> >what is exactly is it detecting and how do I detect the same? Certain
trace
> >flags or option need to be set with dbcc checkdb?
> >
> >TIA
> >CD
> >
>|||Don't believe I missed the point. When you run the integrity
check with that option, the maintenance plan attempts to put
the database in single user mode before it even runs the
integrity check. It doesn't run one version, detects an
error and then runs it again. It just attempts to put the
database in single user mode when using this option.
-Sue
On Thu, 13 Nov 2003 08:12:55 -0600, "CD"
<mcdye1@.hotmail.nospam.com> wrote:
>Thanks for the replies, but you have missed the point. dbcc checkdb found
>no errors.
>The following leads me to believe that something was detected. But What?
>How to confirm? Add switches to DBCC command? trace flag?
>[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC SQL
>Server Driver][SQL Server]Database state cannot be changed while other users
>are using the database 'BaseQuery'
>[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement
>failed.
>[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
>[1] Database BaseQuery: Check Data and Index Linkage...
>[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL
>Server Driver][SQL Server]Repair statement not processed. Database needs to
>be in single user mode.
> The following errors were found:
>[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
>processed. Database needs to be in single user mode.
> ** Execution Time: 0 hrs, 0 mins, 7 secs **
>> On Wed, 12 Nov 2003 15:15:54 -0600, "CD"
>> <mcdye1@.hotmail.nospam.com> wrote:
>> >Win2000sp4. SQL2k sp3
>> >
>> >One server I have the option selected to Attempt to repair any minor
>> >problems is checked on the Integrity checks. I know it is trying to
>repair
>> >something due to the fact it is trying to place in single user mode and
>> >can't due to connections.
>> >
>> >My problem is that I have ran dbcc checkdb and no errors were found. So
>> >what is exactly is it detecting and how do I detect the same? Certain
>trace
>> >flags or option need to be set with dbcc checkdb?
>> >
>> >TIA
>> >CD
>> >
>|||SORRY!
Thank you for the reply....I was tunneled vision due to the next error
message. What excatly 2 hours different.|||No problem...it's not exactly all that intuitive in terms of
the error message and could be seen as having a consistency
problem when there really isn't one.
-Sue
On Thu, 13 Nov 2003 10:57:42 -0600, "CD"
<mcdye1@.hotmail.nospam.com> wrote:
>SORRY!
>Thank you for the reply....I was tunneled vision due to the next error
>message. What excatly 2 hours different.
>

Integrity check failures during maintenance plans

Hi
I have maintenance plans which run weekly to check the integrity of all my
databases. I have the option 'repair minor errors' turned on and I then
recieve the following error when the maintenenace plan runs. I have placed
the database in question into single user mode and run the same task on the
database and it completes successfully. The problem is though that I can run
the integrity check with the 'repair minor errors' option turned off and it
does not pick up any errors.
Any advise on solving this issue would be great?
Check Data and Index Linkage
Error Number: 7919
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode.Don't use the repair option. If you have a problem in the database, you want to know about it,
analyze why it happened, make sure you repair the faulty hardware (for example) etc. In short, that
option isn't a good idea to use in an automated job. If you still want to use that option, you
either have to add something to the job that kicks out all users, or live with that error if there
are users connected.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DeanM" <DeanM@.discussions.microsoft.com> wrote in message
news:AFC39BF6-9706-489A-83B5-BF7F7AD366CA@.microsoft.com...
> Hi
> I have maintenance plans which run weekly to check the integrity of all my
> databases. I have the option 'repair minor errors' turned on and I then
> recieve the following error when the maintenenace plan runs. I have placed
> the database in question into single user mode and run the same task on the
> database and it completes successfully. The problem is though that I can run
> the integrity check with the 'repair minor errors' option turned off and it
> does not pick up any errors.
> Any advise on solving this issue would be great?
>
> Check Data and Index Linkage
> Error Number: 7919
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode.

Integrity check failures during maintenance plans

Hi
I have maintenance plans which run weekly to check the integrity of all my
databases. I have the option 'repair minor errors' turned on and I then
recieve the following error when the maintenenace plan runs. I have placed
the database in question into single user mode and run the same task on the
database and it completes successfully. The problem is though that I can ru
n
the integrity check with the 'repair minor errors' option turned off and it
does not pick up any errors.
Any advise on solving this issue would be great?
Check Data and Index Linkage
Error Number: 7919
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement
not
processed. Database needs to be in single user mode.Don't use the repair option. If you have a problem in the database, you want
to know about it,
analyze why it happened, make sure you repair the faulty hardware (for examp
le) etc. In short, that
option isn't a good idea to use in an automated job. If you still want to us
e that option, you
either have to add something to the job that kicks out all users, or live wi
th that error if there
are users connected.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DeanM" <DeanM@.discussions.microsoft.com> wrote in message
news:AFC39BF6-9706-489A-83B5-BF7F7AD366CA@.microsoft.com...
> Hi
> I have maintenance plans which run weekly to check the integrity of all my
> databases. I have the option 'repair minor errors' turned on and I then
> recieve the following error when the maintenenace plan runs. I have place
d
> the database in question into single user mode and run the same task on th
e
> database and it completes successfully. The problem is though that I can
run
> the integrity check with the 'repair minor errors' option turned off and i
t
> does not pick up any errors.
> Any advise on solving this issue would be great?
>
> Check Data and Index Linkage
> Error Number: 7919
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statemen
t not
> processed. Database needs to be in single user mode.

Integrity check failures during maintenance plans

Hi
I have maintenance plans which run weekly to check the integrity of all my
databases. I have the option 'repair minor errors' turned on and I then
recieve the following error when the maintenenace plan runs. I have placed
the database in question into single user mode and run the same task on the
database and it completes successfully. The problem is though that I can run
the integrity check with the 'repair minor errors' option turned off and it
does not pick up any errors.
Any advise on solving this issue would be great?
Check Data and Index Linkage
Error Number: 7919
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode.
Don't use the repair option. If you have a problem in the database, you want to know about it,
analyze why it happened, make sure you repair the faulty hardware (for example) etc. In short, that
option isn't a good idea to use in an automated job. If you still want to use that option, you
either have to add something to the job that kicks out all users, or live with that error if there
are users connected.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DeanM" <DeanM@.discussions.microsoft.com> wrote in message
news:AFC39BF6-9706-489A-83B5-BF7F7AD366CA@.microsoft.com...
> Hi
> I have maintenance plans which run weekly to check the integrity of all my
> databases. I have the option 'repair minor errors' turned on and I then
> recieve the following error when the maintenenace plan runs. I have placed
> the database in question into single user mode and run the same task on the
> database and it completes successfully. The problem is though that I can run
> the integrity check with the 'repair minor errors' option turned off and it
> does not pick up any errors.
> Any advise on solving this issue would be great?
>
> Check Data and Index Linkage
> Error Number: 7919
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode.

Integrity Check Fails

I setup a DB Maint, Plan for several databases however,
the Integrity Check job fails for a few of my databases.
The error is "Repair statement not processed. Database
needs to be in single user mode."
Any thoughts on why this is happening?
Thanks,
DonPretty much what it says. What version and service pack of SQL Server? Note that you can't put
master in single user mode. I recommend that you remove that darn option to "attempt to repair minor
problems". :-)
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Don" <drduquette@.aol.com> wrote in message news:000001c36ca4$732448e0$a101280a@.phx.gbl...
> I setup a DB Maint, Plan for several databases however,
> the Integrity Check job fails for a few of my databases.
> The error is "Repair statement not processed. Database
> needs to be in single user mode."
> Any thoughts on why this is happening?
> Thanks,
> Don|||Thanks for the quick response! We are using SQLserver
2000 service pack 3. I realize that I could uncheck the
repair but that defeats the purpose if there is a problem
with the database. In addition, MS recommends that the
repair option be checked.
Is there any work around to this issue of putting the DB
in single user mode?
Thanks,
Don
>--Original Message--
>Pretty much what it says. What version and service pack
of SQL Server? Note that you can't put
>master in single user mode. I recommend that you remove
that darn option to "attempt to repair minor
>problems". :-)
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
>"Don" <drduquette@.aol.com> wrote in message
news:000001c36ca4$732448e0$a101280a@.phx.gbl...
>> I setup a DB Maint, Plan for several databases however,
>> the Integrity Check job fails for a few of my databases.
>> The error is "Repair statement not processed. Database
>> needs to be in single user mode."
>> Any thoughts on why this is happening?
>> Thanks,
>> Don
>
>.
>|||Don,
Tibor mentioned a restriction on master. In addition, if a database has any
open connections it cannot be changed to single user mode unless you use the
WITH ROLLBACK clauses.
ALTER DATABASE mydatabase
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
This breaks all unqualified connections and switches to single user mode.
(Much easier than the old method of writing a looping procedure to KILL
connections until they were finally all gone.)
Russell Fields
"Don" <drduquette@.aol.com> wrote in message
news:0e9901c36ca7$0659c2f0$a301280a@.phx.gbl...
> Thanks for the quick response! We are using SQLserver
> 2000 service pack 3. I realize that I could uncheck the
> repair but that defeats the purpose if there is a problem
> with the database. In addition, MS recommends that the
> repair option be checked.
> Is there any work around to this issue of putting the DB
> in single user mode?
> Thanks,
> Don
> >--Original Message--
> >Pretty much what it says. What version and service pack
> of SQL Server? Note that you can't put
> >master in single user mode. I recommend that you remove
> that darn option to "attempt to repair minor
> >problems". :-)
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at: http://groups.google.com/groups?oi=djq&as
> ugroup=microsoft.public.sqlserver
> >
> >
> >"Don" <drduquette@.aol.com> wrote in message
> news:000001c36ca4$732448e0$a101280a@.phx.gbl...
> >> I setup a DB Maint, Plan for several databases however,
> >> the Integrity Check job fails for a few of my databases.
> >> The error is "Repair statement not processed. Database
> >> needs to be in single user mode."
> >>
> >> Any thoughts on why this is happening?
> >>
> >> Thanks,
> >> Don
> >
> >
> >.
> >|||The purpose of CHECKDB is to get *notified* in the unlikely event of a problem. I surely don't want
some background thing try to repair the database if I run into a problem with it. I want to be
there, do a log backup first, think, etc etc etc.
Anyhow, master cannot be in single user mode, if this is the one which is causing your problem, then
you have to remove it from the plan, or don't do background repair.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Don" <drduquette@.aol.com> wrote in message news:0e9901c36ca7$0659c2f0$a301280a@.phx.gbl...
> Thanks for the quick response! We are using SQLserver
> 2000 service pack 3. I realize that I could uncheck the
> repair but that defeats the purpose if there is a problem
> with the database. In addition, MS recommends that the
> repair option be checked.
> Is there any work around to this issue of putting the DB
> in single user mode?
> Thanks,
> Don
> >--Original Message--
> >Pretty much what it says. What version and service pack
> of SQL Server? Note that you can't put
> >master in single user mode. I recommend that you remove
> that darn option to "attempt to repair minor
> >problems". :-)
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at: http://groups.google.com/groups?oi=djq&as
> ugroup=microsoft.public.sqlserver
> >
> >
> >"Don" <drduquette@.aol.com> wrote in message
> news:000001c36ca4$732448e0$a101280a@.phx.gbl...
> >> I setup a DB Maint, Plan for several databases however,
> >> the Integrity Check job fails for a few of my databases.
> >> The error is "Repair statement not processed. Database
> >> needs to be in single user mode."
> >>
> >> Any thoughts on why this is happening?
> >>
> >> Thanks,
> >> Don
> >
> >
> >.
> >