backup error - single user mode needed?

Hi Everyone,
One of my clients is running SQL 2000 SP4 and the Data Maintenance Plan
logs shows a log with...
Database master: 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 **
...
[5] Database master: Database Backup...
The backup was not performed since data verification errors were found.

There are similar messages for databases model and msdb.
Questions:
- should master, model and msdb be included in the daily backup?
- how does one get into single user mode and repair this?
Thanks
Richard
Hi,
In your maintenance plan, uncheck the Attempt to repair minor problems
checkbox in Integrity tab.
With that checkbox unchecked, integrity checks will be still done on the
databases and as a good practice verify your maintenance logs
for any integroty errors. If there is any error you could manually correct
it.
Thanks
Hari
SQL Server MVP
"Richard Fagen" <no_spam@.aol.com> wrote in message
news:O3JolEukFHA.2792@.TK2MSFTNGP10.phx.gbl...
> Hi Everyone,
> One of my clients is running SQL 2000 SP4 and the Data Maintenance Plan
> logs shows a log with...
> --
> Database master: 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 **
> ...
> [5] Database master: Database Backup...
> The backup was not performed since data verification errors were
> found.
>
> --
> There are similar messages for databases model and msdb.
> Questions:
> - should master, model and msdb be included in the daily backup?
> - how does one get into single user mode and repair this?
> Thanks
> Richard
|||hi Richard,
Richard Fagen wrote:
is the disk damaged?

> Questions:
> - should master, model and msdb be included in the daily backup?
usually not.. only when server modifications occur, like implementing new
jobs, alerts, logins and so on...

> - how does one get into single user mode and repair this?
http://msdn.microsoft.com/library/de...start_4nhh.asp
http://msdn.microsoft.com/library/de...kprst_4g4w.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Andrea Montanari wrote:
> hi Richard,
> Richard Fagen wrote:
> is the disk damaged?
always forget the Maintenance Plan is that intrusive (and bugged ) ..
always prefer self written jobs :D
yep.. Hari is right
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Hari,
I'll uncheck the box and try it again.
I use SQL as part of SBS 2000/2003 and that seems to go against what
I've read in books. All the books say be sure to check that box
However, since your both SQL MVPs and Andrea has helped me out before,
I'll go with your recommendations.
Richard
p.s. the one who says to check it, Harry Brelsford, is an MVP too, but
of SBS not SQL
Hari Prasad wrote:
> Hi,
> In your maintenance plan, uncheck the Attempt to repair minor problems
> checkbox in Integrity tab.
> With that checkbox unchecked, integrity checks will be still done on the
> databases and as a good practice verify your maintenance logs
> for any integroty errors. If there is any error you could manually correct
> it.
> Thanks
> Hari
> SQL Server MVP
>
> "Richard Fagen" <no_spam@.aol.com> wrote in message
> news:O3JolEukFHA.2792@.TK2MSFTNGP10.phx.gbl...
>
>
|||Hi Andrea,

> is the disk damaged?
Not that I know of. Besides, I remotely logged into a few SBS clients
to test the data maintenance plan with backing up master, model and msdb
and they all had the same error messages.
I guess it wouldn't hurt to have everyone run a scandisk (I try to get
them to do it on their own, but they never remember) and doublecheck.

> usually not.. only when server modifications occur, like implementing new
> jobs, alerts, logins and so on...
I can manually backup master, model and msdb via the Enterprise Manager,
just not via the maintenance plan.
I hope they fix this in SQL 2005

> http://msdn.microsoft.com/library/de...start_4nhh.asp
> http://msdn.microsoft.com/library/de...kprst_4g4w.asp
Thanks for the links
Richard
|||hi Richard,
Richard Fagen wrote:
> Hi Andrea,
> Not that I know of. Besides, I remotely logged into a few SBS clients
> to test the data maintenance plan with backing up master, model and
> msdb and they all had the same error messages.
see Hari post... Maintenance Plan is "intrusive" by default and tryes to
perform activities requiring single user access...

> I guess it wouldn't hurt to have everyone run a scandisk (I try to get
> them to do it on their own, but they never remember) and doublecheck.
but ok. checking disk integrity is always neat :D

> I can manually backup master, model and msdb via the Enterprise
> Manager, just not via the maintenance plan.
again, see Hari answer...

> I hope they fix this in SQL 2005
SQLExpress will not include the Agent, so no Maintenance Plan at all... but
worse, no jobs at all, and that includes both admin/house keeping jobs as
other dataload,dml jobs
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||From Microsoft Article 290622:
BUG: Databse Manintenance Plan on System Databases faile on Intergrity
Check if "Attempt to repair minor problems" is Selected
... The database maintenance plan attempts to place the system databases
in the single user mode in order to execute CkDBRepair. However the
Master and MSDB databases cannot be placed in single user mode because of
constantly running system processes like LAZYWRITER, LOG WRITER, SQL
Agent... The repair step fails to run and the job reports a failure...
There is no fix for tis bug as yet. The workaround is to disable the
"Attempt to repair any minor problems" option in your maintenance plan.
Lyle
In article <O3JolEukFHA.2792@.TK2MSFTNGP10.phx.gbl>, no_spam@.aol.com
says...
> Hi Everyone,
> One of my clients is running SQL 2000 SP4 and the Data Maintenance Plan
> logs shows a log with...
> --
> Database master: 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 **
> ...
> [5] Database master: Database Backup...
> The backup was not performed since data verification errors were found.
>
> --
> There are similar messages for databases model and msdb.
> Questions:
> - should master, model and msdb be included in the daily backup?
> - how does one get into single user mode and repair this?
> Thanks
> Richard
>
|||Hi Lyle,
Thanks for the article. Finally, a great explanation
Richard
Lyle wrote:

> From Microsoft Article 290622:
> BUG: Databse Manintenance Plan on System Databases faile on Intergrity
> Check if "Attempt to repair minor problems" is Selected
> ... The database maintenance plan attempts to place the system databases
> in the single user mode in order to execute CkDBRepair. However the
> Master and MSDB databases cannot be placed in single user mode because of
> constantly running system processes like LAZYWRITER, LOG WRITER, SQL
> Agent... The repair step fails to run and the job reports a failure...
> There is no fix for tis bug as yet. The workaround is to disable the
> "Attempt to repair any minor problems" option in your maintenance plan.
> Lyle
sql