We upgraded to sql 2000 last night and now the maintenance
plans do not work. I'm using simple model, no tlog backup.
The error I'm working with is this:
sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The
step failed.
The log looks like this:
Microsoft (R) SQLMaint Utility (Unicode), Version Logged
on to SQL Server '1s' as 'NT AUTHORITY\SYSTEM' (trusted)
Starting maintenance plan 'DB Maintenance Plan1' on
7/31/2003 2:00:02 AM
[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 'r'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
DATABASE statement failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption
command failed.
[1] Database rbcs: 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 **
[2] Database t: Check Data and Index Linkage...
** Execution Time: 0 hrs, 0 mins, 2 secs **
[3] Database r: Database Backup...
The backup was not performed since data verification
errors were found.
[4] Database t: Database Backup...
Destination: [D:\mssql7\BACKUP\t\t_db_200307310200.BAK]
** Execution Time: 0 hrs, 0 mins, 14 secs **
[5] Database t: Verifying Backup...
** Execution Time: 0 hrs, 0 mins, 5 secs **
Deleting old text reports... 1 file(s) deleted.
End of maintenance plan 'DB Maintenance Plan1' on
7/31/2003 2:00:23 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)D
You are geting this error because the maintenance plan is
trying to put the database into single user mode. It fails
because there is a user (or at least an open transaction)
in the database. It is attempting to do this so it can
perform a dbcc checkdb with repair.
If this worked before, perhaps something in the upgrade
has lead to open transactions? Or have you changed the
time the job runs?
If you change the dbcc part of the maint plan, not to do
the repair it would not try to put the database into
single user mode. Check the output from your dbcc checkdb
and if you find you do need to run with repair, schedule a
job to run at a time when you can switch to single user
mode.
Hope this helps
John|||The plan was setup under sql 7 so I guess there wasnt a
problem in that version. I will remove the repair option,
but I suppose I'll have to run dbcc later.
Thanks
>--Original Message--
>D
>You are geting this error because the maintenance plan is
>trying to put the database into single user mode. It
fails
>because there is a user (or at least an open transaction)
>in the database. It is attempting to do this so it can
>perform a dbcc checkdb with repair.
>If this worked before, perhaps something in the upgrade
>has lead to open transactions? Or have you changed the
>time the job runs?
>If you change the dbcc part of the maint plan, not to do
>the repair it would not try to put the database into
>single user mode. Check the output from your dbcc checkdb
>and if you find you do need to run with repair, schedule
a
>job to run at a time when you can switch to single user
>mode.
>Hope this helps
>John
>.
>
