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]&#
91;ODBC SQL
Server Driver][SQL Server]Database state cannot be changed while other u
sers
are using the database 'abcdefg'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE st
atement
failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption comma
nd failed.
[4] Database abcdefg: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft]&#
91;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][ODB
C
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 com
mand failed.
> [4] Database abcdefg: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODB
C
SQL
> Server Driver][SQL Server]Repair statement not processed. Database needs[/vbco
l]
to[vbcol=seagreen]
> 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:

so I'm guessing it cannot because there are open connections.
Thanks anyway
"Argenis Fernandez" wrote:
[vbcol=seagreen]
> You can try modifying the job for the maintenance plan and adding a step t
o
> 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...
> on
> SQL
> users
> SQL
> to
> --
> the
> ALL
>
>|||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...[vbcol=seagreen]
> Actually the job is attempting to ALTER DATABASE:
>
statement[vbcol=seagreen]
> so I'm guessing it cannot because there are open connections.
> Thanks anyway
>
> "Argenis Fernandez" wrote:
>
to[vbcol=seagreen]
set[vbcol=seagreen]
[Microsoft][ODBC[vbcol=seagreen]
statement[vbcol=seagreen]
failed.[vbcol=seagreen]
[Microsoft][ODBC[vbcol=seagreen]
needs[vbcol=seagreen]
> ----
except[vbcol=seagreen]
DISCONNECT[vbcol=seagreen]
Therefore,[vbcol=seagreen]|||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 so
meone 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 com
mand 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 nee
ds to
> be in single user mode.
> ----
--
> As a result, I'm assuming the job must disconnect everyone first, except t
he
> job itself. The only way I can find to do this is to issue a DISCONNECT A
LL
> statement. This is new turf for me, having never been a DBA. Therefore,
> please give me any other options or feedback.
> Thanks :-)
>