Re: Error 22029 on Maintenance Plan

From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 12/10/04 

Date: Fri, 10 Dec 2004 14:23:48 -0500

Even someone with an open connection in EM will prevent this. 

-- 
Andrew J. Kelly  SQL MVP
"John Steen" <moderndads(nospam)@hotmail.com> wrote in message 
news:80AEC9F1-27DF-4131-BA5B-F2CF6469594F@microsoft.com...
> Thanks, Andrew, I'll give that a try.  But there are no users in the DB 
> when
> the maintenance plan kicks off, so it should be able to place the DB in
> single-user mode as needed.
>
> John
>
> "Andrew J. Kelly" wrote:
>
>> Check the MP and you should find a checkbox that tells the plan to 
>> "Repair
>> any minor Errors".  When this is checked it will attempt to place the db 
>> in
>> single user mode at the beginning of the check.  If there are users in 
>> the
>> db it will fail.  This is an option that should never be used anyway , so
>> just turn it off and you should be good to go.  I suspect there was a 
>> slight
>> difference in the way 7.0 and 2000 handle this.
>>
>> -- 
>> Andrew J. Kelly  SQL MVP
>>
>>
>> "John Steen" <moderndads(nospam)@hotmail.com> wrote in message
>> news:F25F51BA-4BF6-4171-8EB5-349461F14763@microsoft.com...
>> > Setup:
>> > - SQL Server 2000 (SP3 w/cumulative patch MS03-031) running on Win2K 
>> > (SP3)
>> > - Standard merge replication for one DB among three servers
>> >
>> > I have two maintenance plans.  Maintenance plan 1 is for user databases
>> > and
>> > also backs up transaction logs, and maintanance plan 2 is for system
>> > databases (master, distribution, etc.) and does not do log backups.
>> >
>> > I've been getting error 22029 (unable to put DB in single-user mode)
>> > during
>> > the "Check Data and Index Linkage" ever since I switched over from SQL 
>> > 7.0
>> > to
>> > SQL 2000 (new install on a new server, not an upgrade).  There are no
>> > errors
>> > on maintenance plan 2, just on maintenance plan 1, and it affects only 
>> > two
>> > of
>> > the seven DBs.  One is being merge replicated with identical DBs on two
>> > remote subscriber servers, the other is not.
>> >
>> > A Backup Exec SQL agent is used to backup the live databases, but it 
>> > runs
>> > an
>> > hour later than the maintenance plan.  I made sure there are no other
>> > scheduling conflicts and that no other process have connections to the
>> > databases.
>> >
>> > DBCC CHECKDB came back fine, and I've ruled out everything on 
>> > Microsoft's
>> > website havnig to do with this error.
>> >
>> > I'm at a dead end here and could really use some help.
>> >
>> > Thanks,
>> > John Steen
>> > remove (nospam) from e-mail address to send me a message
>>
>>
>>

Relevant Pages

  • Re: Maintenance Plan Fails
    ... Andrew J. Kelly SQL MVP ... a maintenance plan against the restored databases. ... When I execute the plan it works fine on the newly created test database. ...
    (microsoft.public.sqlserver.tools)
  • Re: SQL 2005 user limit
    ... Andrew J. Kelly SQL MVP ... "Marcin S." ... > I just created two databases and two users but both users can see ... >> Andrew J. Kelly SQL MVP ...
    (microsoft.public.sqlserver.security)
  • Re: Maintenance Plan Question
    ... Andrew J. Kelly SQL MVP ... "Dan Jacobs" wrote in message ... > setting in the maintenance plan. ...
    (microsoft.public.sqlserver.setup)
  • Re: Located databases no longer used.
    ... Everyone wants that feature though. ... Andrew J. Kelly SQL MVP ... >> Andrew J. Kelly SQL MVP ... >>> a specified period of time; we are looking to locate databases that are ...
    (microsoft.public.sqlserver.tools)
  • Re: Moving the Data directory in Desktop Edition
    ... Andrew J. Kelly SQL MVP ... "boe" wrote in message ... > Is it possible to change the location of the databases on the desktop ...
    (microsoft.public.sqlserver.server)