DBCC CHECKDB: repair

Showing posts with label repair. Show all posts

Saturday, February 25, 2012

DB maintenance Plan error

Hi,
I am using DB maintenance plan for the integrity checks
and have included the dbs
to repair minor problems but I think it needs the db to
be in single user mode before it can do it so my jobs
have been failing with these error message
Microsoft (R) SQLMaint Utility (Unicode), Version Logged
on to SQL Server 'SQLMELDEV' as 'NT AUTHORITY\SYSTEM'
(trusted)
Starting maintenance plan 'DB Maintenance Plan1' on
2/03/2005 9:00:00 AM
[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 'Northwind'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
DATABASE statement failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]
sp_dboption command failed.
[1] Database Northwind: 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, 1 secs **
End of maintenance plan 'DB Maintenance Plan1' on
2/03/2005 9:00:00 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
Anyone know how i can overcome this one as I have about
10 dbs in that server.
Do i need to kill all the users before using it?
Any simple methods/logic?
AnupSure, un check the checkbox that says to fix minor issues. Then it won't
try and put it in single user mode. And if you do find errors you should be
the one to decide how you are going to approach the repair instead of
letting the mp blindly fix issues that you may never know you had.
--
Andrew J. Kelly SQL MVP
"Anup" <anonymous@.discussions.microsoft.com> wrote in message
news:2c5301c51eab$71f88340$a401280a@.phx.gbl...
> Hi,
> I am using DB maintenance plan for the integrity checks
> and have included the dbs
> to repair minor problems but I think it needs the db to
> be in single user mode before it can do it so my jobs
> have been failing with these error message
>
> Microsoft (R) SQLMaint Utility (Unicode), Version Logged
> on to SQL Server 'SQLMELDEV' as 'NT AUTHORITY\SYSTEM'
> (trusted)
> Starting maintenance plan 'DB Maintenance Plan1' on
> 2/03/2005 9:00:00 AM
> [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 'Northwind'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
> DATABASE statement failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]
> sp_dboption command failed.
> [1] Database Northwind: 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, 1 secs **
> End of maintenance plan 'DB Maintenance Plan1' on
> 2/03/2005 9:00:00 AM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
>
> Anyone know how i can overcome this one as I have about
> 10 dbs in that server.
> Do i need to kill all the users before using it?
> Any simple methods/logic?
> Anup|||Uncheck the 'repair minor errors' box for the integrity check job. It
requires that the database is put into single user mode before the DBCC can
be run. You don't want to automatically repair any errors without working
out why they happened.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Anup" <anonymous@.discussions.microsoft.com> wrote in message
news:2c5301c51eab$71f88340$a401280a@.phx.gbl...
> Hi,
> I am using DB maintenance plan for the integrity checks
> and have included the dbs
> to repair minor problems but I think it needs the db to
> be in single user mode before it can do it so my jobs
> have been failing with these error message
>
> Microsoft (R) SQLMaint Utility (Unicode), Version Logged
> on to SQL Server 'SQLMELDEV' as 'NT AUTHORITY\SYSTEM'
> (trusted)
> Starting maintenance plan 'DB Maintenance Plan1' on
> 2/03/2005 9:00:00 AM
> [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 'Northwind'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
> DATABASE statement failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]
> sp_dboption command failed.
> [1] Database Northwind: 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, 1 secs **
> End of maintenance plan 'DB Maintenance Plan1' on
> 2/03/2005 9:00:00 AM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
>
> Anyone know how i can overcome this one as I have about
> 10 dbs in that server.
> Do i need to kill all the users before using it?
> Any simple methods/logic?
> Anup|||But how will i know if has any errors during the process
any tools codes will be helpfull to check those
thanks
>--Original Message--
>Sure, un check the checkbox that says to fix minor
issues. Then it won't
>try and put it in single user mode. And if you do find
errors you should be
>the one to decide how you are going to approach the
repair instead of
>letting the mp blindly fix issues that you may never
know you had.
>--
>Andrew J. Kelly SQL MVP
>
>"Anup" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2c5301c51eab$71f88340$a401280a@.phx.gbl...
>> Hi,
>> I am using DB maintenance plan for the integrity checks
>> and have included the dbs
>> to repair minor problems but I think it needs the db to
>> be in single user mode before it can do it so my jobs
>> have been failing with these error message
>>
>> Microsoft (R) SQLMaint Utility (Unicode), Version
Logged
>> on to SQL Server 'SQLMELDEV' as 'NT AUTHORITY\SYSTEM'
>> (trusted)
>> Starting maintenance plan 'DB Maintenance Plan1' on
>> 2/03/2005 9:00:00 AM
>> [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 'Northwind'
>> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
>> DATABASE statement failed.
>> [Microsoft][ODBC SQL Server Driver][SQL Server]
>> sp_dboption command failed.
>> [1] Database Northwind: 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, 1 secs **
>> End of maintenance plan 'DB Maintenance Plan1' on
>> 2/03/2005 9:00:00 AM
>> SQLMAINT.EXE Process Exit Code: 1 (Failed)
>>
>> Anyone know how i can overcome this one as I have about
>> 10 dbs in that server.
>> Do i need to kill all the users before using it?
>> Any simple methods/logic?
>> Anup
>
>.
>|||The job will fail if there are errors. If not then it will succeed.
--
Andrew J. Kelly SQL MVP
"anup" <anonymous@.discussions.microsoft.com> wrote in message
news:2c6d01c51eb1$a69feab0$a401280a@.phx.gbl...
> But how will i know if has any errors during the process
> any tools codes will be helpfull to check those
> thanks
>>--Original Message--
>>Sure, un check the checkbox that says to fix minor
> issues. Then it won't
>>try and put it in single user mode. And if you do find
> errors you should be
>>the one to decide how you are going to approach the
> repair instead of
>>letting the mp blindly fix issues that you may never
> know you had.
>>--
>>Andrew J. Kelly SQL MVP
>>
>>"Anup" <anonymous@.discussions.microsoft.com> wrote in
> message
>>news:2c5301c51eab$71f88340$a401280a@.phx.gbl...
>> Hi,
>> I am using DB maintenance plan for the integrity checks
>> and have included the dbs
>> to repair minor problems but I think it needs the db to
>> be in single user mode before it can do it so my jobs
>> have been failing with these error message
>>
>> Microsoft (R) SQLMaint Utility (Unicode), Version
> Logged
>> on to SQL Server 'SQLMELDEV' as 'NT AUTHORITY\SYSTEM'
>> (trusted)
>> Starting maintenance plan 'DB Maintenance Plan1' on
>> 2/03/2005 9:00:00 AM
>> [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 'Northwind'
>> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
>> DATABASE statement failed.
>> [Microsoft][ODBC SQL Server Driver][SQL Server]
>> sp_dboption command failed.
>> [1] Database Northwind: 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, 1 secs **
>> End of maintenance plan 'DB Maintenance Plan1' on
>> 2/03/2005 9:00:00 AM
>> SQLMAINT.EXE Process Exit Code: 1 (Failed)
>>
>> Anyone know how i can overcome this one as I have about
>> 10 dbs in that server.
>> Do i need to kill all the users before using it?
>> Any simple methods/logic?
>> Anup
>>
>>.|||I have the same problem, but I have never had the box to fix minor...
checked. It is still unchecked, what could be wrong?
"Paul S Randal [MS]" skrev:
> Uncheck the 'repair minor errors' box for the integrity check job. It
> requires that the database is put into single user mode before the DBCC can
> be run. You don't want to automatically repair any errors without working
> out why they happened.
> Regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Anup" <anonymous@.discussions.microsoft.com> wrote in message
> news:2c5301c51eab$71f88340$a401280a@.phx.gbl...
> > Hi,
> >
> > I am using DB maintenance plan for the integrity checks
> > and have included the dbs
> > to repair minor problems but I think it needs the db to
> > be in single user mode before it can do it so my jobs
> > have been failing with these error message
> >
> >
> > Microsoft (R) SQLMaint Utility (Unicode), Version Logged
> > on to SQL Server 'SQLMELDEV' as 'NT AUTHORITY\SYSTEM'
> > (trusted)
> > Starting maintenance plan 'DB Maintenance Plan1' on
> > 2/03/2005 9:00:00 AM
> > [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 'Northwind'
> > [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
> > DATABASE statement failed.
> > [Microsoft][ODBC SQL Server Driver][SQL Server]
> > sp_dboption command failed.
> > [1] Database Northwind: 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, 1 secs **
> >
> > End of maintenance plan 'DB Maintenance Plan1' on
> > 2/03/2005 9:00:00 AM
> > SQLMAINT.EXE Process Exit Code: 1 (Failed)
> >
> >
> > Anyone know how i can overcome this one as I have about
> > 10 dbs in that server.
> > Do i need to kill all the users before using it?
> > Any simple methods/logic?
> > Anup
>
>

Friday, February 24, 2012

DB maintenance Plan error

DB Maintenance Plan - integrity check

Hi:
After I selected "Attemp to repair any minor problems",
my integrity check failed. - "Repair statement not
processed. Database needs to be in single user mode" for
Master and msdb.
How do I change to single user mode from within
Maintenance Plan?
Thanks in advance.You don't - just deselect the option to repair. It's
probably better to just monitor the DBCC statements and if
there are errors, intervene yourself, track down the issues,
make sure there aren't any hardware problems, etc.
-Sue
On Mon, 22 Sep 2003 11:03:43 -0700, "Dave K."
<uk@.hotmail.com> wrote:
>Hi:
>After I selected "Attemp to repair any minor problems",
>my integrity check failed. - "Repair statement not
>processed. Database needs to be in single user mode" for
>Master and msdb.
>How do I change to single user mode from within
>Maintenance Plan?
>Thanks in advance.

DB Maintenance job

I have 2 SQL 7 servers. First one is NT. I created job using default. Like
reorganize data and Index, remove unused spaceand attempt to repair any minor
index problem. Win NT machine take more than 3 hours for the part check data
and Index Linkage.
For Win 2k machine, I am doing the same thing. Here check data and Index
Linkage finishes in a second. Or doing nothing.
Please give me an idea why it is happening. Because my 2 database are acting
different in term of performance.Probably there are users in the database and "repairing" can't set to single user mode, result it
does nothing and you get a warning from the job. Don't use this setting. If you have a corruption,
you ant to be there, do root causes analysis and handle the situation based on the he circumstances:
http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Astros" <Astros@.discussions.microsoft.com> wrote in message
news:A44FADEF-FD22-45E7-BC29-A13F16A16A56@.microsoft.com...
>I have 2 SQL 7 servers. First one is NT. I created job using default. Like
> reorganize data and Index, remove unused spaceand attempt to repair any minor
> index problem. Win NT machine take more than 3 hours for the part check data
> and Index Linkage.
> For Win 2k machine, I am doing the same thing. Here check data and Index
> Linkage finishes in a second. Or doing nothing.
> Please give me an idea why it is happening. Because my 2 database are acting
> different in term of performance.
>|||This repair has nothng to do with reindex. My reindex part of the job ran
fine. Still I have performance issue. What do you think?
Thank you for your reply.
"Tibor Karaszi" wrote:
> Probably there are users in the database and "repairing" can't set to single user mode, result it
> does nothing and you get a warning from the job. Don't use this setting. If you have a corruption,
> you ant to be there, do root causes analysis and handle the situation based on the he circumstances:
> http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Astros" <Astros@.discussions.microsoft.com> wrote in message
> news:A44FADEF-FD22-45E7-BC29-A13F16A16A56@.microsoft.com...
> >I have 2 SQL 7 servers. First one is NT. I created job using default. Like
> > reorganize data and Index, remove unused spaceand attempt to repair any minor
> > index problem. Win NT machine take more than 3 hours for the part check data
> > and Index Linkage.
> >
> > For Win 2k machine, I am doing the same thing. Here check data and Index
> > Linkage finishes in a second. Or doing nothing.
> >
> > Please give me an idea why it is happening. Because my 2 database are acting
> > different in term of performance.
> >
>
>|||Perhaps some bug in maint wiz? Are you current on service packs? Same service pack on both? If you
specify a report file for the maint plan, any errors or warnings?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Astros" <Astros@.discussions.microsoft.com> wrote in message
news:4145C478-EAF4-43C0-8E16-F5E6B030972F@.microsoft.com...
> This repair has nothng to do with reindex. My reindex part of the job ran
> fine. Still I have performance issue. What do you think?
> Thank you for your reply.
> "Tibor Karaszi" wrote:
>> Probably there are users in the database and "repairing" can't set to single user mode, result it
>> does nothing and you get a warning from the job. Don't use this setting. If you have a
>> corruption,
>> you ant to be there, do root causes analysis and handle the situation based on the he
>> circumstances:
>> http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "Astros" <Astros@.discussions.microsoft.com> wrote in message
>> news:A44FADEF-FD22-45E7-BC29-A13F16A16A56@.microsoft.com...
>> >I have 2 SQL 7 servers. First one is NT. I created job using default. Like
>> > reorganize data and Index, remove unused spaceand attempt to repair any minor
>> > index problem. Win NT machine take more than 3 hours for the part check data
>> > and Index Linkage.
>> >
>> > For Win 2k machine, I am doing the same thing. Here check data and Index
>> > Linkage finishes in a second. Or doing nothing.
>> >
>> > Please give me an idea why it is happening. Because my 2 database are acting
>> > different in term of performance.
>> >
>>

Saturday, February 25, 2012

DB maintenance Plan error

Friday, February 24, 2012

DB maintenance Plan error

DB maintenance Plan error

DB Maintenance Plan - integrity check

DB Maintenance job

DB Maintenance job

DB Maintenance job

DBCC CHECKDB

Blog Archive

About Me