December 17, 2002 at 3:04 am
Hi!
I have got a newly installed W2K SP3 + SQL2K SP2 all patched up machine. I migrated a SQL7 database to this machine and ran the DB Maintenance Plan wizard to create plans. But if I enable the option to check integrity before doing full and transaction log backups, I get the following error message:
Starting maintenance plan 'Cbs DB Maintenance' on 2002-12-16 20:09:04
[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 'Cbs'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
[1] Database Cbs: 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 Cbs: Database Backup...
The backup was not performed since data verification errors were found.
But if I run DBCC CHECKDB/CHECKALLOC in Query Analyzer, no errors can be found, and running the backups without integrity checks, it all works fine, just as it did in SQL7.
Brgds
Jonas
BrgdsJonas
December 17, 2002 at 4:46 am
Probably the MAINTPLAN wizzard will put the DB in single user mode and this cannot be done while other connections are in the DB.
The maintplan is executed by the sqlserveragent so this is one user. If you are also using the Enterprise manager that is the second ......
December 17, 2002 at 4:06 pm
Are you trying to "repair" errors? This has some bugs. I wouldn't do it, just check and manually repair (or page out) if you find errors.
Steve Jones
December 27, 2002 at 10:31 am
Integrity checks run if the database is set to a single user. If there is any active connection, then the integrity check fails. Run the backup after killing any remaining connection (we had a similar issue. One of the user was leaving at the end of the day leaving his connection open. The next daay the check failed. We implemented a kill processes at midnight. Since then, no more failures and the backup is running fine)
Good luck!!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply