Errors during Maintenance Plan execution

I am getting error on several database when trying to use
the Maintenance Plan to backup all databses in our SQL
Server 2000 with Service Pack 3.
The following is the text generated from one of the
database in the log file. The other databases generate
the same error message
Log portion============================== [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 'SQLCatalog'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
DATABASE statement failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]
sp_dboption command failed.
[18] Database SQLCatalog: 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 **
========================================
Any ideas on how to handle this? Is this a matter of
running the maintenance plan at a different time?You've checked the option to "Attempt to repair minor problems" for which
SQL Server tries to set the db in single user mode which will fail if you
have users in the database. Remove that option, if you do get a problem with
the database, you want to know about it and be there to make a conscious
decision of your actions at that stage.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jim" <jim.abel@.lmco.com> wrote in message
news:051501c3bdaf$b172e7d0$3101280a@.phx.gbl...
> I am getting error on several database when trying to use
> the Maintenance Plan to backup all databses in our SQL
> Server 2000 with Service Pack 3.
> The following is the text generated from one of the
> database in the log file. The other databases generate
> the same error message
> Log portion==============================> [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 'SQLCatalog'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
> DATABASE statement failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]
> sp_dboption command failed.
> [18] Database SQLCatalog: 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 **
> ========================================> Any ideas on how to handle this? Is this a matter of
> running the maintenance plan at a different time?|||That helped. So now I have an error with the transaction
log backup portion of the Maintenace Plan.
Here is the erro
==========================================================Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe
failed. [SQLSTATE 42000] (Error 22029). The step failed.
===============================================
I can't find this in the error logs or the backup log so
I'm not sure what is happening
>--Original Message--
>You've checked the option to "Attempt to repair minor
problems" for which
>SQL Server tries to set the db in single user mode which
will fail if you
>have users in the database. Remove that option, if you
do get a problem with
>the database, you want to know about it and be there to
make a conscious
>decision of your actions at that stage.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Jim" <jim.abel@.lmco.com> wrote in message
>news:051501c3bdaf$b172e7d0$3101280a@.phx.gbl...
>> I am getting error on several database when trying to
use
>> the Maintenance Plan to backup all databses in our SQL
>> Server 2000 with Service Pack 3.
>> The following is the text generated from one of the
>> database in the log file. The other databases generate
>> the same error message
>> Log portion==============================>> [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 'SQLCatalog'
>> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
>> DATABASE statement failed.
>> [Microsoft][ODBC SQL Server Driver][SQL Server]
>> sp_dboption command failed.
>> [18] Database SQLCatalog: 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 **
>> ========================================>> Any ideas on how to handle this? Is this a matter of
>> running the maintenance plan at a different time?
>
>.
>|||Possibly the database is in simple recovery model. You will not find details
error info from looking at just the job. There's a GUI for this in the main
folder, but I prefer the text report file option that maint wiz has.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jim" <jim.abel@.lmco.com> wrote in message
news:10f501c3be74$0be078d0$a401280a@.phx.gbl...
> That helped. So now I have an error with the transaction
> log backup portion of the Maintenace Plan.
> Here is the erro
> ==========================================================> Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe
> failed. [SQLSTATE 42000] (Error 22029). The step failed.
> ===============================================> I can't find this in the error logs or the backup log so
> I'm not sure what is happening
> >--Original Message--
> >You've checked the option to "Attempt to repair minor
> problems" for which
> >SQL Server tries to set the db in single user mode which
> will fail if you
> >have users in the database. Remove that option, if you
> do get a problem with
> >the database, you want to know about it and be there to
> make a conscious
> >decision of your actions at that stage.
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at:
> >http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"Jim" <jim.abel@.lmco.com> wrote in message
> >news:051501c3bdaf$b172e7d0$3101280a@.phx.gbl...
> >> I am getting error on several database when trying to
> use
> >> the Maintenance Plan to backup all databses in our SQL
> >> Server 2000 with Service Pack 3.
> >> The following is the text generated from one of the
> >> database in the log file. The other databases generate
> >> the same error message
> >>
> >> Log portion==============================> >> [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 'SQLCatalog'
> >> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
> >> DATABASE statement failed.
> >> [Microsoft][ODBC SQL Server Driver][SQL Server]
> >> sp_dboption command failed.
> >> [18] Database SQLCatalog: 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 **
> >>
> >> ========================================> >>
> >> Any ideas on how to handle this? Is this a matter of
> >> running the maintenance plan at a different time?
> >
> >
> >.
> >|||Hello Jim,
First of all, thanks to Tibor for pointing you in the right direction.
Just as an additional information,
you can refer to the following article which explains how to t-shoot
General SQL Maint Wiz failures,
INF: Troubleshooting Database Maintenance Plan Failures
http://support.microsoft.com/default.aspx?scid=kb;en-us;288577
In case, you are doing Transaction log backups of one of the databases
which is set to
Simple Recovery Mode, you might want to refer to following article
explaining this scenario,
BUG: Expired Transaction Log Backups May Not Be Deleted by Maintenance Plan
http://support.microsoft.com/default.aspx?scid=kb;en-us;303292
Please let us know if these suggestions resolve your issue, if not , feel
free to post any further questions you have.
Thanks for posting to MSDN Managed Newsgroup.
Vikrant Dalwale
Microsoft SQL Server Support Professional
Microsoft highly recommends to all of our customers that they visit the
http://www.microsoft.com/protect site and perform the three straightforward
steps listed to improve your computer?s security."
This posting is provided "AS IS" with no warranties, and confers no rights.
>Content-Class: urn:content-classes:message
>From: "Jim" <jim.abel@.lmco.com>
>Sender: "Jim" <jim.abel@.lmco.com>
>References: <051501c3bdaf$b172e7d0$3101280a@.phx.gbl>
<#iNW#wbvDHA.1872@.TK2MSFTNGP09.phx.gbl>
>Subject: Re: Errors during Maintenance Plan execution
>Date: Tue, 9 Dec 2003 08:46:52 -0800
>Lines: 71
>Message-ID: <10f501c3be74$0be078d0$a401280a@.phx.gbl>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="iso-8859-1"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>X-MIMEOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>thread-index: AcO+dAvglHDFmnxfSiyfAzlM01uGHQ==>Newsgroups: microsoft.public.sqlserver.server
>Path: cpmsftngxa07.phx.gbl
>Xref: cpmsftngxa07.phx.gbl microsoft.public.sqlserver.server:319894
>NNTP-Posting-Host: tk2msftngxa12.phx.gbl 10.40.1.164
>X-Tomcat-NG: microsoft.public.sqlserver.server
>That helped. So now I have an error with the transaction
>log backup portion of the Maintenace Plan.
>Here is the erro
>==========================================================>Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe
>failed. [SQLSTATE 42000] (Error 22029). The step failed.
>===============================================>I can't find this in the error logs or the backup log so
>I'm not sure what is happening
>>--Original Message--
>>You've checked the option to "Attempt to repair minor
>problems" for which
>>SQL Server tries to set the db in single user mode which
>will fail if you
>>have users in the database. Remove that option, if you
>do get a problem with
>>the database, you want to know about it and be there to
>make a conscious
>>decision of your actions at that stage.
>>--
>>Tibor Karaszi, SQL Server MVP
>>Archive at:
>>http://groups.google.com/groups?
>oi=djq&as_ugroup=microsoft.public.sqlserver
>>
>>"Jim" <jim.abel@.lmco.com> wrote in message
>>news:051501c3bdaf$b172e7d0$3101280a@.phx.gbl...
>> I am getting error on several database when trying to
>use
>> the Maintenance Plan to backup all databses in our SQL
>> Server 2000 with Service Pack 3.
>> The following is the text generated from one of the
>> database in the log file. The other databases generate
>> the same error message
>> Log portion==============================>> [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 'SQLCatalog'
>> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
>> DATABASE statement failed.
>> [Microsoft][ODBC SQL Server Driver][SQL Server]
>> sp_dboption command failed.
>> [18] Database SQLCatalog: 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 **
>> ========================================>> Any ideas on how to handle this? Is this a matter of
>> running the maintenance plan at a different time?
>>
>>.
>|||All right guys are really helping me out. I looked up
the proble and it has come down to the following The
maintenace Plan says that it cannot do transaction log
backups on the master and the msdb databases.
My question now is how can I exclude those 2 DB's from
the Transaction log backup portion of the maintenance
Plan?
>--Original Message--
>
>Hello Jim,
>First of all, thanks to Tibor for pointing you in the
right direction.
>Just as an additional information,
>you can refer to the following article which explains
how to t-shoot
>General SQL Maint Wiz failures,
>INF: Troubleshooting Database Maintenance Plan Failures
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;288577
>In case, you are doing Transaction log backups of one of
the databases
>which is set to
>Simple Recovery Mode, you might want to refer to
following article
>explaining this scenario,
>BUG: Expired Transaction Log Backups May Not Be Deleted
by Maintenance Plan
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;303292
>Please let us know if these suggestions resolve your
issue, if not , feel
>free to post any further questions you have.
>Thanks for posting to MSDN Managed Newsgroup.
>Vikrant Dalwale
>Microsoft SQL Server Support Professional
>Microsoft highly recommends to all of our customers
that they visit the
>http://www.microsoft.com/protect site and perform the
three straightforward
>steps listed to improve your computer's security."
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>
>--
>>Content-Class: urn:content-classes:message
>>From: "Jim" <jim.abel@.lmco.com>
>>Sender: "Jim" <jim.abel@.lmco.com>
>>References: <051501c3bdaf$b172e7d0$3101280a@.phx.gbl>
><#iNW#wbvDHA.1872@.TK2MSFTNGP09.phx.gbl>
>>Subject: Re: Errors during Maintenance Plan execution
>>Date: Tue, 9 Dec 2003 08:46:52 -0800
>>Lines: 71
>>Message-ID: <10f501c3be74$0be078d0$a401280a@.phx.gbl>
>>MIME-Version: 1.0
>>Content-Type: text/plain;
>> charset="iso-8859-1"
>>Content-Transfer-Encoding: 7bit
>>X-Newsreader: Microsoft CDO for Windows 2000
>>X-MIMEOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>>thread-index: AcO+dAvglHDFmnxfSiyfAzlM01uGHQ==>>Newsgroups: microsoft.public.sqlserver.server
>>Path: cpmsftngxa07.phx.gbl
>>Xref: cpmsftngxa07.phx.gbl
microsoft.public.sqlserver.server:319894
>>NNTP-Posting-Host: tk2msftngxa12.phx.gbl 10.40.1.164
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>That helped. So now I have an error with the
transaction
>>log backup portion of the Maintenace Plan.
>>Here is the erro
>>==========================================================>>Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe
>>failed. [SQLSTATE 42000] (Error 22029). The step
failed.
>>===============================================>>I can't find this in the error logs or the backup log
so
>>I'm not sure what is happening
>>--Original Message--
>>You've checked the option to "Attempt to repair minor
>>problems" for which
>>SQL Server tries to set the db in single user mode
which
>>will fail if you
>>have users in the database. Remove that option, if you
>>do get a problem with
>>the database, you want to know about it and be there
to
>>make a conscious
>>decision of your actions at that stage.
>>--
>>Tibor Karaszi, SQL Server MVP
>>Archive at:
>>http://groups.google.com/groups?
>>oi=djq&as_ugroup=microsoft.public.sqlserver
>>
>>"Jim" <jim.abel@.lmco.com> wrote in message
>>news:051501c3bdaf$b172e7d0$3101280a@.phx.gbl...
>> I am getting error on several database when trying
to
>>use
>> the Maintenance Plan to backup all databses in our
SQL
>> Server 2000 with Service Pack 3.
>> The following is the text generated from one of the
>> database in the log file. The other databases
generate
>> the same error message
>> Log portion==============================>> [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 'SQLCatalog'
>> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
>> DATABASE statement failed.
>> [Microsoft][ODBC SQL Server Driver][SQL Server]
>> sp_dboption command failed.
>> [18] Database SQLCatalog: 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 **
>> ========================================>> Any ideas on how to handle this? Is this a matter of
>> running the maintenance plan at a different time?
>>
>>.
>>
>.
>|||Hello Jim,
You need to create a seperate Maintenance Plan for those DBs which need
both the Database and Transaction log backups and
a seperate one for master and msdb for only Database backups.
Does that answer your question ?
Thanks for using MSDN Managed Newsgroup.
Vikrant Dalwale
Microsoft SQL Server Support Professional
Microsoft highly recommends to all of our customers that they visit the
http://www.microsoft.com/protect site and perform the three straightforward
steps listed to improve your computer?s security.
This posting is provided "AS IS" with no warranties, and confers no rights.
>Content-Class: urn:content-classes:message
>From: "Jim" <jim.abel@.lmco.com>
>Sender: "Jim" <jim.abel@.lmco.com>
>References: <051501c3bdaf$b172e7d0$3101280a@.phx.gbl>
<#iNW#wbvDHA.1872@.TK2MSFTNGP09.phx.gbl>
<10f501c3be74$0be078d0$a401280a@.phx.gbl>
<TChLAfovDHA.2520@.cpmsftngxa07.phx.gbl>
>Subject: Re: Errors during Maintenance Plan execution
>Date: Fri, 12 Dec 2003 08:27:51 -0800
>Lines: 171
>Message-ID: <05dc01c3c0cc$e343d240$a001280a@.phx.gbl>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="iso-8859-1"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>Thread-Index: AcPAzONDhi3qx6uXTfm9O5c0Z9Bk1A==>Newsgroups: microsoft.public.sqlserver.server
>Path: cpmsftngxa07.phx.gbl
>Xref: cpmsftngxa07.phx.gbl microsoft.public.sqlserver.server:320465
>NNTP-Posting-Host: tk2msftngxa08.phx.gbl 10.40.1.160
>X-Tomcat-NG: microsoft.public.sqlserver.server
>All right guys are really helping me out. I looked up
>the proble and it has come down to the following The
>maintenace Plan says that it cannot do transaction log
>backups on the master and the msdb databases.
>My question now is how can I exclude those 2 DB's from
>the Transaction log backup portion of the maintenance
>Plan?
>>--Original Message--
>>
>>Hello Jim,
>>First of all, thanks to Tibor for pointing you in the
>right direction.
>>Just as an additional information,
>>you can refer to the following article which explains
>how to t-shoot
>>General SQL Maint Wiz failures,
>>INF: Troubleshooting Database Maintenance Plan Failures
>>http://support.microsoft.com/default.aspx?scid=kb;en-
>us;288577
>>In case, you are doing Transaction log backups of one of
>the databases
>>which is set to
>>Simple Recovery Mode, you might want to refer to
>following article
>>explaining this scenario,
>>BUG: Expired Transaction Log Backups May Not Be Deleted
>by Maintenance Plan
>>http://support.microsoft.com/default.aspx?scid=kb;en-
>us;303292
>>Please let us know if these suggestions resolve your
>issue, if not , feel
>>free to post any further questions you have.
>>Thanks for posting to MSDN Managed Newsgroup.
>>Vikrant Dalwale
>>Microsoft SQL Server Support Professional
>>Microsoft highly recommends to all of our customers
>that they visit the
>>http://www.microsoft.com/protect site and perform the
>three straightforward
>>steps listed to improve your computer's security."
>>This posting is provided "AS IS" with no warranties, and
>confers no rights.
>>
>>--
>>Content-Class: urn:content-classes:message
>>From: "Jim" <jim.abel@.lmco.com>
>>Sender: "Jim" <jim.abel@.lmco.com>
>>References: <051501c3bdaf$b172e7d0$3101280a@.phx.gbl>
>><#iNW#wbvDHA.1872@.TK2MSFTNGP09.phx.gbl>
>>Subject: Re: Errors during Maintenance Plan execution
>>Date: Tue, 9 Dec 2003 08:46:52 -0800
>>Lines: 71
>>Message-ID: <10f501c3be74$0be078d0$a401280a@.phx.gbl>
>>MIME-Version: 1.0
>>Content-Type: text/plain;
>> charset="iso-8859-1"
>>Content-Transfer-Encoding: 7bit
>>X-Newsreader: Microsoft CDO for Windows 2000
>>X-MIMEOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>>thread-index: AcO+dAvglHDFmnxfSiyfAzlM01uGHQ==>>Newsgroups: microsoft.public.sqlserver.server
>>Path: cpmsftngxa07.phx.gbl
>>Xref: cpmsftngxa07.phx.gbl
>microsoft.public.sqlserver.server:319894
>>NNTP-Posting-Host: tk2msftngxa12.phx.gbl 10.40.1.164
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>That helped. So now I have an error with the
>transaction
>>log backup portion of the Maintenace Plan.
>>Here is the erro
>>========================================================>==>>Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe
>>failed. [SQLSTATE 42000] (Error 22029). The step
>failed.
>>===============================================>>I can't find this in the error logs or the backup log
>so
>>I'm not sure what is happening
>>--Original Message--
>>You've checked the option to "Attempt to repair minor
>>problems" for which
>>SQL Server tries to set the db in single user mode
>which
>>will fail if you
>>have users in the database. Remove that option, if you
>>do get a problem with
>>the database, you want to know about it and be there
>to
>>make a conscious
>>decision of your actions at that stage.
>>--
>>Tibor Karaszi, SQL Server MVP
>>Archive at:
>>http://groups.google.com/groups?
>>oi=djq&as_ugroup=microsoft.public.sqlserver
>>
>>"Jim" <jim.abel@.lmco.com> wrote in message
>>news:051501c3bdaf$b172e7d0$3101280a@.phx.gbl...
>> I am getting error on several database when trying
>to
>>use
>> the Maintenance Plan to backup all databses in our
>SQL
>> Server 2000 with Service Pack 3.
>> The following is the text generated from one of the
>> database in the log file. The other databases
>generate
>> the same error message
>> Log portion==============================>> [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 'SQLCatalog'
>> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
>> DATABASE statement failed.
>> [Microsoft][ODBC SQL Server Driver][SQL Server]
>> sp_dboption command failed.
>> [18] Database SQLCatalog: 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 **
>> ========================================>> Any ideas on how to handle this? Is this a matter of
>> running the maintenance plan at a different time?
>>
>>.
>>
>>.
>|||Thank you Vikrant
That answers all of my questions.
I appreciate all of the help.
Happy Holidays
>--Original Message--
>Hello Jim,
>You need to create a seperate Maintenance Plan for
those DBs which need
>both the Database and Transaction log backups and
> a seperate one for master and msdb for only Database
backups.
>Does that answer your question ?
>Thanks for using MSDN Managed Newsgroup.
>Vikrant Dalwale
>Microsoft SQL Server Support Professional
>
>Microsoft highly recommends to all of our customers
that they visit the
>http://www.microsoft.com/protect site and perform the
three straightforward
>steps listed to improve your computer's security.
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>
>--
>>Content-Class: urn:content-classes:message
>>From: "Jim" <jim.abel@.lmco.com>
>>Sender: "Jim" <jim.abel@.lmco.com>
>>References: <051501c3bdaf$b172e7d0$3101280a@.phx.gbl>
><#iNW#wbvDHA.1872@.TK2MSFTNGP09.phx.gbl>
><10f501c3be74$0be078d0$a401280a@.phx.gbl>
><TChLAfovDHA.2520@.cpmsftngxa07.phx.gbl>
>>Subject: Re: Errors during Maintenance Plan execution
>>Date: Fri, 12 Dec 2003 08:27:51 -0800
>>Lines: 171
>>Message-ID: <05dc01c3c0cc$e343d240$a001280a@.phx.gbl>
>>MIME-Version: 1.0
>>Content-Type: text/plain;
>> charset="iso-8859-1"
>>Content-Transfer-Encoding: 7bit
>>X-Newsreader: Microsoft CDO for Windows 2000
>>X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>>Thread-Index: AcPAzONDhi3qx6uXTfm9O5c0Z9Bk1A==>>Newsgroups: microsoft.public.sqlserver.server
>>Path: cpmsftngxa07.phx.gbl
>>Xref: cpmsftngxa07.phx.gbl
microsoft.public.sqlserver.server:320465
>>NNTP-Posting-Host: tk2msftngxa08.phx.gbl 10.40.1.160
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>All right guys are really helping me out. I looked up
>>the proble and it has come down to the following The
>>maintenace Plan says that it cannot do transaction log
>>backups on the master and the msdb databases.
>>My question now is how can I exclude those 2 DB's from
>>the Transaction log backup portion of the maintenance
>>Plan?
>>--Original Message--
>>
>>Hello Jim,
>>First of all, thanks to Tibor for pointing you in the
>>right direction.
>>Just as an additional information,
>>you can refer to the following article which explains
>>how to t-shoot
>>General SQL Maint Wiz failures,
>>INF: Troubleshooting Database Maintenance Plan Failures
>>http://support.microsoft.com/default.aspx?scid=kb;en-
>>us;288577
>>In case, you are doing Transaction log backups of one
of
>>the databases
>>which is set to
>>Simple Recovery Mode, you might want to refer to
>>following article
>>explaining this scenario,
>>BUG: Expired Transaction Log Backups May Not Be
Deleted
>>by Maintenance Plan
>>http://support.microsoft.com/default.aspx?scid=kb;en-
>>us;303292
>>Please let us know if these suggestions resolve your
>>issue, if not , feel
>>free to post any further questions you have.
>>Thanks for posting to MSDN Managed Newsgroup.
>>Vikrant Dalwale
>>Microsoft SQL Server Support Professional
>>Microsoft highly recommends to all of our customers
>>that they visit the
>>http://www.microsoft.com/protect site and perform the
>>three straightforward
>>steps listed to improve your computer's security."
>>This posting is provided "AS IS" with no warranties,
and
>>confers no rights.
>>
>>--
>>Content-Class: urn:content-classes:message
>>From: "Jim" <jim.abel@.lmco.com>
>>Sender: "Jim" <jim.abel@.lmco.com>
>>References: <051501c3bdaf$b172e7d0$3101280a@.phx.gbl>
>><#iNW#wbvDHA.1872@.TK2MSFTNGP09.phx.gbl>
>>Subject: Re: Errors during Maintenance Plan execution
>>Date: Tue, 9 Dec 2003 08:46:52 -0800
>>Lines: 71
>>Message-ID: <10f501c3be74$0be078d0$a401280a@.phx.gbl>
>>MIME-Version: 1.0
>>Content-Type: text/plain;
>> charset="iso-8859-1"
>>Content-Transfer-Encoding: 7bit
>>X-Newsreader: Microsoft CDO for Windows 2000
>>X-MIMEOLE: Produced By Microsoft MimeOLE
V5.50.4910.0300
>>thread-index: AcO+dAvglHDFmnxfSiyfAzlM01uGHQ==>>Newsgroups: microsoft.public.sqlserver.server
>>Path: cpmsftngxa07.phx.gbl
>>Xref: cpmsftngxa07.phx.gbl
>>microsoft.public.sqlserver.server:319894
>>NNTP-Posting-Host: tk2msftngxa12.phx.gbl 10.40.1.164
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>That helped. So now I have an error with the
>>transaction
>>log backup portion of the Maintenace Plan.
>>Here is the erro
>>========================================================>>==>>Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe
>>failed. [SQLSTATE 42000] (Error 22029). The step
>>failed.
>>===============================================>>I can't find this in the error logs or the backup log
>>so
>>I'm not sure what is happening
>>--Original Message--
>>You've checked the option to "Attempt to repair
minor
>>problems" for which
>>SQL Server tries to set the db in single user mode
>>which
>>will fail if you
>>have users in the database. Remove that option, if
you
>>do get a problem with
>>the database, you want to know about it and be there
>>to
>>make a conscious
>>decision of your actions at that stage.
>>--
>>Tibor Karaszi, SQL Server MVP
>>Archive at:
>>http://groups.google.com/groups?
>>oi=djq&as_ugroup=microsoft.public.sqlserver
>>
>>"Jim" <jim.abel@.lmco.com> wrote in message
>>news:051501c3bdaf$b172e7d0$3101280a@.phx.gbl...
>> I am getting error on several database when trying
>>to
>>use
>> the Maintenance Plan to backup all databses in our
>>SQL
>> Server 2000 with Service Pack 3.
>> The following is the text generated from one of the
>> database in the log file. The other databases
>>generate
>> the same error message
>> Log portion==============================>> [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 'SQLCatalog'
>> [Microsoft][ODBC SQL Server Driver][SQL Server]
ALTER
>> DATABASE statement failed.
>> [Microsoft][ODBC SQL Server Driver][SQL Server]
>> sp_dboption command failed.
>> [18] Database SQLCatalog: 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 **
>> ========================================>> Any ideas on how to handle this? Is this a matter
of
>> running the maintenance plan at a different time?
>>
>>.
>>
>>.
>>
>.
>|||I have a similar problem so I verified that my database is
the only one in the maintenance plan (does not include
master or msdb). Yet my Transaction Log backup still
fails with the same message:
"The job failed. The Job was invoked by User U1ST074SORTS2
\rdenison. The last step to run was step 1 (Step 1)."
"sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The
step failed."
Any other ideas? I'm really interested in resolving this
because my Xaction log gets huge, especially on a
distributor server.
Roger.
>--Original Message--
>All right guys are really helping me out. I looked up
>the proble and it has come down to the following The
>maintenace Plan says that it cannot do transaction log
>backups on the master and the msdb databases.
>My question now is how can I exclude those 2 DB's from
>the Transaction log backup portion of the maintenance
>Plan?
>>--Original Message--
>>
>>Hello Jim,
>>First of all, thanks to Tibor for pointing you in the
>right direction.
>>Just as an additional information,
>>you can refer to the following article which explains
>how to t-shoot
>>General SQL Maint Wiz failures,
>>INF: Troubleshooting Database Maintenance Plan Failures
>>http://support.microsoft.com/default.aspx?scid=kb;en-
>us;288577
>>In case, you are doing Transaction log backups of one of
>the databases
>>which is set to
>>Simple Recovery Mode, you might want to refer to
>following article
>>explaining this scenario,
>>BUG: Expired Transaction Log Backups May Not Be Deleted
>by Maintenance Plan
>>http://support.microsoft.com/default.aspx?scid=kb;en-
>us;303292
>>Please let us know if these suggestions resolve your
>issue, if not , feel
>>free to post any further questions you have.
>>Thanks for posting to MSDN Managed Newsgroup.
>>Vikrant Dalwale
>>Microsoft SQL Server Support Professional
>>Microsoft highly recommends to all of our customers
>that they visit the
>>http://www.microsoft.com/protect site and perform the
>three straightforward
>>steps listed to improve your computer's security."
>>This posting is provided "AS IS" with no warranties, and
>confers no rights.
>>
>>--
>>Content-Class: urn:content-classes:message
>>From: "Jim" <jim.abel@.lmco.com>
>>Sender: "Jim" <jim.abel@.lmco.com>
>>References: <051501c3bdaf$b172e7d0$3101280a@.phx.gbl>
>><#iNW#wbvDHA.1872@.TK2MSFTNGP09.phx.gbl>
>>Subject: Re: Errors during Maintenance Plan execution
>>Date: Tue, 9 Dec 2003 08:46:52 -0800
>>Lines: 71
>>Message-ID: <10f501c3be74$0be078d0$a401280a@.phx.gbl>
>>MIME-Version: 1.0
>>Content-Type: text/plain;
>> charset="iso-8859-1"
>>Content-Transfer-Encoding: 7bit
>>X-Newsreader: Microsoft CDO for Windows 2000
>>X-MIMEOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>>thread-index: AcO+dAvglHDFmnxfSiyfAzlM01uGHQ==>>Newsgroups: microsoft.public.sqlserver.server
>>Path: cpmsftngxa07.phx.gbl
>>Xref: cpmsftngxa07.phx.gbl
>microsoft.public.sqlserver.server:319894
>>NNTP-Posting-Host: tk2msftngxa12.phx.gbl 10.40.1.164
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>That helped. So now I have an error with the
>transaction
>>log backup portion of the Maintenace Plan.
>>Here is the erro
>>========================================================>==>>Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe
>>failed. [SQLSTATE 42000] (Error 22029). The step
>failed.
>>===============================================>>I can't find this in the error logs or the backup log
>so
>>I'm not sure what is happening
>>--Original Message--
>>You've checked the option to "Attempt to repair minor
>>problems" for which
>>SQL Server tries to set the db in single user mode
>which
>>will fail if you
>>have users in the database. Remove that option, if you
>>do get a problem with
>>the database, you want to know about it and be there
>to
>>make a conscious
>>decision of your actions at that stage.
>>--
>>Tibor Karaszi, SQL Server MVP
>>Archive at:
>>http://groups.google.com/groups?
>>oi=djq&as_ugroup=microsoft.public.sqlserver
>>
>>"Jim" <jim.abel@.lmco.com> wrote in message
>>news:051501c3bdaf$b172e7d0$3101280a@.phx.gbl...
>> I am getting error on several database when trying
>to
>>use
>> the Maintenance Plan to backup all databses in our
>SQL
>> Server 2000 with Service Pack 3.
>> The following is the text generated from one of the
>> database in the log file. The other databases
>generate
>> the same error message
>> Log portion==============================>> [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 'SQLCatalog'
>> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
>> DATABASE statement failed.
>> [Microsoft][ODBC SQL Server Driver][SQL Server]
>> sp_dboption command failed.
>> [18] Database SQLCatalog: 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 **
>> ========================================>> Any ideas on how to handle this? Is this a matter of
>> running the maintenance plan at a different time?
>>
>>.
>>
>>.
>.
>|||Why not create your own job to do the backups and not rely on the MP at all?
Then you will know exactly what it is trying to do and when.
--
Andrew J. Kelly
SQL Server MVP
"Roger Denison" <anonymous@.discussions.microsoft.com> wrote in message
news:215001c3cfee$1f72d010$3101280a@.phx.gbl...
> I have a similar problem so I verified that my database is
> the only one in the maintenance plan (does not include
> master or msdb). Yet my Transaction Log backup still
> fails with the same message:
> "The job failed. The Job was invoked by User U1ST074SORTS2
> \rdenison. The last step to run was step 1 (Step 1)."
> "sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The
> step failed."
> Any other ideas? I'm really interested in resolving this
> because my Xaction log gets huge, especially on a
> distributor server.
> Roger.
> >--Original Message--
> >All right guys are really helping me out. I looked up
> >the proble and it has come down to the following The
> >maintenace Plan says that it cannot do transaction log
> >backups on the master and the msdb databases.
> >
> >My question now is how can I exclude those 2 DB's from
> >the Transaction log backup portion of the maintenance
> >Plan?
> >
> >>--Original Message--
> >>
> >>
> >>Hello Jim,
> >>
> >>First of all, thanks to Tibor for pointing you in the
> >right direction.
> >>Just as an additional information,
> >>you can refer to the following article which explains
> >how to t-shoot
> >>General SQL Maint Wiz failures,
> >>
> >>INF: Troubleshooting Database Maintenance Plan Failures
> >>http://support.microsoft.com/default.aspx?scid=kb;en-
> >us;288577
> >>
> >>In case, you are doing Transaction log backups of one of
> >the databases
> >>which is set to
> >>Simple Recovery Mode, you might want to refer to
> >following article
> >>explaining this scenario,
> >>
> >>BUG: Expired Transaction Log Backups May Not Be Deleted
> >by Maintenance Plan
> >>http://support.microsoft.com/default.aspx?scid=kb;en-
> >us;303292
> >>
> >>Please let us know if these suggestions resolve your
> >issue, if not , feel
> >>free to post any further questions you have.
> >>
> >>Thanks for posting to MSDN Managed Newsgroup.
> >>
> >>Vikrant Dalwale
> >>
> >>Microsoft SQL Server Support Professional
> >>
> >>Microsoft highly recommends to all of our customers
> >that they visit the
> >>http://www.microsoft.com/protect site and perform the
> >three straightforward
> >>steps listed to improve your computer's security."
> >>This posting is provided "AS IS" with no warranties, and
> >confers no rights.
> >>
> >>
> >>
> >>--
> >>Content-Class: urn:content-classes:message
> >>From: "Jim" <jim.abel@.lmco.com>
> >>Sender: "Jim" <jim.abel@.lmco.com>
> >>References: <051501c3bdaf$b172e7d0$3101280a@.phx.gbl>
> >><#iNW#wbvDHA.1872@.TK2MSFTNGP09.phx.gbl>
> >>Subject: Re: Errors during Maintenance Plan execution
> >>Date: Tue, 9 Dec 2003 08:46:52 -0800
> >>Lines: 71
> >>Message-ID: <10f501c3be74$0be078d0$a401280a@.phx.gbl>
> >>MIME-Version: 1.0
> >>Content-Type: text/plain;
> >> charset="iso-8859-1"
> >>Content-Transfer-Encoding: 7bit
> >>X-Newsreader: Microsoft CDO for Windows 2000
> >>X-MIMEOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
> >>thread-index: AcO+dAvglHDFmnxfSiyfAzlM01uGHQ==> >>Newsgroups: microsoft.public.sqlserver.server
> >>Path: cpmsftngxa07.phx.gbl
> >>Xref: cpmsftngxa07.phx.gbl
> >microsoft.public.sqlserver.server:319894
> >>NNTP-Posting-Host: tk2msftngxa12.phx.gbl 10.40.1.164
> >>X-Tomcat-NG: microsoft.public.sqlserver.server
> >>
> >>That helped. So now I have an error with the
> >transaction
> >>log backup portion of the Maintenace Plan.
> >>
> >>Here is the erro
> >>
> >>========================================================> >==> >>Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe
> >>failed. [SQLSTATE 42000] (Error 22029). The step
> >failed.
> >>===============================================> >>
> >>I can't find this in the error logs or the backup log
> >so
> >>I'm not sure what is happening
> >>--Original Message--
> >>You've checked the option to "Attempt to repair minor
> >>problems" for which
> >>SQL Server tries to set the db in single user mode
> >which
> >>will fail if you
> >>have users in the database. Remove that option, if you
> >>do get a problem with
> >>the database, you want to know about it and be there
> >to
> >>make a conscious
> >>decision of your actions at that stage.
> >>
> >>--
> >>Tibor Karaszi, SQL Server MVP
> >>Archive at:
> >>http://groups.google.com/groups?
> >>oi=djq&as_ugroup=microsoft.public.sqlserver
> >>
> >>
> >>"Jim" <jim.abel@.lmco.com> wrote in message
> >>news:051501c3bdaf$b172e7d0$3101280a@.phx.gbl...
> >> I am getting error on several database when trying
> >to
> >>use
> >> the Maintenance Plan to backup all databses in our
> >SQL
> >> Server 2000 with Service Pack 3.
> >> The following is the text generated from one of the
> >> database in the log file. The other databases
> >generate
> >> the same error message
> >>
> >> Log portion==============================> >> [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 'SQLCatalog'
> >> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
> >> DATABASE statement failed.
> >> [Microsoft][ODBC SQL Server Driver][SQL Server]
> >> sp_dboption command failed.
> >> [18] Database SQLCatalog: 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 **
> >>
> >> ========================================> >>
> >> Any ideas on how to handle this? Is this a matter of
> >> running the maintenance plan at a different time?
> >>
> >>
> >>.
> >>
> >>
> >>
> >>.
> >>
> >.
> >