|
|
You're in the
sql server msde
group:backup error - single user mode needed?
sql server msde:
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
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 :) [quoted text, click to view] 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 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 Andrea,
[quoted text, click to view] > 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. [quoted text, click to view] >>- 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... 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 :) [quoted text, click to view] > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_start_4nhh.asp Thanks for the links> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_4g4w.asp
Hi Lyle,
Thanks for the article. Finally, a great explanation :) Richard [quoted text, click to view] 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. >
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... [quoted text, click to view] > 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,
[quoted text, click to view] Richard Fagen wrote: is the disk damaged? [quoted text, click to view] > 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... [quoted text, click to view] > - how does one get into single user mode and repair this? http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_start_4nhh.asphttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_4g4w.asp -- Andrea Montanari (Microsoft MVP - SQL Server) http://www.asql.biz/DbaMgr.shtm http://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
[quoted text, click to view]
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.shtm http://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 Richard,
[quoted text, click to view] Richard Fagen wrote: > 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. see Hari post... Maintenance Plan is "intrusive" by default and tryes to perform activities requiring single user access... [quoted text, click to view] > 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 [quoted text, click to view] >>> - 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... > > I can manually backup master, model and msdb via the Enterprise > Manager, just not via the maintenance plan. again, see Hari answer... [quoted text, click to view] > 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.shtm http://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,
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 [quoted text, click to view] "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
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |