Database Maintenance Plans

Setting up SQL Server Maintenance Plans. Getting the following error set on
some tasks:
[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 'abcdefg'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement
failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
[4] Database abcdefg: 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
-----
As a result, I'm assuming the job must disconnect everyone first, except the
job itself. The only way I can find to do this is to issue a DISCONNECT ALL
statement. This is new turf for me, having never been a DBA. Therefore,
please give me any other options or feedback.
Thanks :-)You can try modifying the job for the maintenance plan and adding a step to
do the following:
ALTER DATABASE <name> SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE.
Let us know if it helps.
-Argenis
"Brett Ossman" <BrettOssman@.discussions.microsoft.com> wrote in message
news:A2B4D992-8FB9-4F7C-A559-EB6FDCA32D66@.microsoft.com...
> Setting up SQL Server Maintenance Plans. Getting the following error set
on
> some tasks:
> [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 'abcdefg'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement
> failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
> [4] Database abcdefg: 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.
> ----
--
> As a result, I'm assuming the job must disconnect everyone first, except
the
> job itself. The only way I can find to do this is to issue a DISCONNECT
ALL
> statement. This is new turf for me, having never been a DBA. Therefore,
> please give me any other options or feedback.
> Thanks :-)
>|||Actually the job is attempting to ALTER DATABASE:
> > [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement
> > failed.
so I'm guessing it cannot because there are open connections.
Thanks anyway
"Argenis Fernandez" wrote:
> You can try modifying the job for the maintenance plan and adding a step to
> do the following:
> ALTER DATABASE <name> SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE.
> Let us know if it helps.
> -Argenis
>
> "Brett Ossman" <BrettOssman@.discussions.microsoft.com> wrote in message
> news:A2B4D992-8FB9-4F7C-A559-EB6FDCA32D66@.microsoft.com...
> > Setting up SQL Server Maintenance Plans. Getting the following error set
> on
> > some tasks:
> >
> > [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 'abcdefg'
> >
> > [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement
> > failed.
> >
> > [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
> > [4] Database abcdefg: 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.
> > ----
> --
> > As a result, I'm assuming the job must disconnect everyone first, except
> the
> > job itself. The only way I can find to do this is to issue a DISCONNECT
> ALL
> > statement. This is new turf for me, having never been a DBA. Therefore,
> > please give me any other options or feedback.
> >
> > Thanks :-)
> >
>
>|||Yes, but probably the job is not issuing a "WITH ROLLBACK IMMEDIATE"
statement. Can't tell for sure. You might want to give it a try -- just
remember to bring the DB to MULTI_USER again at the end of the job.
-Argenis
"Brett Ossman" <BrettOssman@.discussions.microsoft.com> wrote in message
news:DB23A1EC-B4EF-469C-BCD9-DAF6466FFF45@.microsoft.com...
> Actually the job is attempting to ALTER DATABASE:
> > > [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE
statement
> > > failed.
> so I'm guessing it cannot because there are open connections.
> Thanks anyway
>
> "Argenis Fernandez" wrote:
> > You can try modifying the job for the maintenance plan and adding a step
to
> > do the following:
> >
> > ALTER DATABASE <name> SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE.
> >
> > Let us know if it helps.
> >
> > -Argenis
> >
> >
> > "Brett Ossman" <BrettOssman@.discussions.microsoft.com> wrote in message
> > news:A2B4D992-8FB9-4F7C-A559-EB6FDCA32D66@.microsoft.com...
> > > Setting up SQL Server Maintenance Plans. Getting the following error
set
> > on
> > > some tasks:
> > >
> > > [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 'abcdefg'
> > >
> > > [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE
statement
> > > failed.
> > >
> > > [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command
failed.
> > > [4] Database abcdefg: 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.
> >
> ----
> > --
> > > As a result, I'm assuming the job must disconnect everyone first,
except
> > the
> > > job itself. The only way I can find to do this is to issue a
DISCONNECT
> > ALL
> > > statement. This is new turf for me, having never been a DBA.
Therefore,
> > > please give me any other options or feedback.
> > >
> > > Thanks :-)
> > >
> >
> >
> >|||Uncheck the "attempt to repair minor problems" option in your maint plan. If you have a corruption,
you'd want to know about it and do root cause analysis instead of "having someone trying to fix it
overnight".
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Brett Ossman" <BrettOssman@.discussions.microsoft.com> wrote in message
news:A2B4D992-8FB9-4F7C-A559-EB6FDCA32D66@.microsoft.com...
> Setting up SQL Server Maintenance Plans. Getting the following error set on
> some tasks:
> [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 'abcdefg'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement
> failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
> [4] Database abcdefg: 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.
> -----
> As a result, I'm assuming the job must disconnect everyone first, except the
> job itself. The only way I can find to do this is to issue a DISCONNECT ALL
> statement. This is new turf for me, having never been a DBA. Therefore,
> please give me any other options or feedback.
> Thanks :-)
>