Backup maintenance plan fails.

20 Jun 2006 11:43 AM

SQLboston

I have backup maintenance plans that fail overnight.
The main error refers to database being in single user mode.
I will post the complete error message tomorrow.

I can do a backup outright to my hard drive but I could not run a
maintenance plan to do the same backup let's say in one half an hour.

The maintenance plans worked in the past.
I have lately updated from 7 sp4 to 2000 sp4.
I have rewritten new maintenance plans. They still fail.

Any idea?

20 Jun 2006 1:33 PM

Steen Persson (DK)

SQLboston wrote:

> I have backup maintenance plans that fail overnight.
> The main error refers to database being in single user mode.
> I will post the complete error message tomorrow.
>
> I can do a backup outright to my hard drive but I could not run a
> maintenance plan to do the same backup let's say in one half an hour.
>
> The maintenance plans worked in the past.
> I have lately updated from 7 sp4 to 2000 sp4.
> I have rewritten new maintenance plans. They still fail.
>
> Any idea?

With out having seen the error message, my guess would be that you are
trying to do a log backup on a database that are in Simple recovery.

--
Regards
Steen SchlÃ¼ter Persson
Databaseadministrator / Systemadministrator

20 Jun 2006 4:23 PM

SQLboston

The error says ...

.... Logged on to SQL Server ... as 'NT AUTHORITY/SYSTEM' (trusted)
Starting maintenance plan ....
[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 ...
[Microsoft][ODBC SQL Server Driver][SQL Server] ALTER DATABASE statement
failed.
.....
[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.
......

20 Jun 2006 6:42 PM

Tibor Karaszi

Remove the "attempt to repair minor problems" from the integrity check part of the maint plan.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

Show quote

"SQLboston" <SQLbos***@discussions.microsoft.com> wrote in message
news:1BAE136B-6BB0-43FF-894B-F79DD00E9BC3@microsoft.com...
> The error says ...
>
> ... Logged on to SQL Server ... as 'NT AUTHORITY/SYSTEM' (trusted)
> Starting maintenance plan ....
> [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 ...
> [Microsoft][ODBC SQL Server Driver][SQL Server] ALTER DATABASE statement
> failed.
> ....
> [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.
> .....
>
>
>