March 21, 2003 at 10:44 am
Hello,
New to SQL Server but have benefited greatly from your forum. I have some maintenance jobs (Check Data, Index) that fail 60-70 percent of the time -
Error 5070: [Microsoft][ODBC SQL Server Driver][SQL Server]Database state cannot be changed while other users are using the database 'CE8'
Error 7919: [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode.
How can I identify who/what is causing the conflict here? Jobs run after 11:30 PM and should not be in contention with others. And as I wrote above, 20-30 percent of jobs are successful. Also, doesn't some to happen with my backups.
Thanks.
March 21, 2003 at 11:09 am
It seems that your database is having problems. The maintenance job is runnging a command call DBCC CHECKDB that checks for consistency error in the db. I think that is found errors in your database and that it tried to put the database in single user mode, to repair the db.
You should, put the db in single user mode, you can do this with Enterprise manager, or executing this sentence with Query Analizer:
exec sp_dboption 'db_Name','single user',true
and then you should run the command DBCC CheckDB(db_name) to check for errors.
March 21, 2003 at 11:10 am
Are you repairing minor errors? Wouldn't, know bugs with this.
There is probably someone still connected nad the db can't be set to single user mode.
On error, run sp_who2
Steve Jones
March 21, 2003 at 11:24 am
I don't think getting the error indicates a problem, just that it can't achieve single user mode. I usually leave the repair errors option unchecked, if the integrity check reports an error, then I can manually put the db in single user mode and do the repair. You can run a job prior to kill all connections, but not always feasible - may have users continuing to hit it, log readers that will restart, etc.
Andy
March 24, 2003 at 8:14 am
Look in http://support.microsoft.com/default.aspx?scid=kb;en-us;290622
It referes to system databases, but i have the same issue on user dbs...try to uncheck the option 'Attemp to repair minor problems' and see what happens...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply