Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-09-06 : 10:10:30
|
| Oliver writes "Dear Team,I have a nightly backup for all databases at 5am on the DB Server. As the backup jobs execute and do their stuff, when they come to the girlfriend database for the integrity checks firstly it shows this error:[Microsoft SQL-DMO (ODBC SQLState: 01000)] Error 0: This server has been disconnected. You must reconnect to perform this operation.then this error in the next log file.[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 'girlfriend'[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement failed.[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.[3] Database girlfriend: 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 job then continues onto the next database and does the integrity checks and backups without any problems.I can not figure out why it says its disconnected and then does not want to run the integrity checks on the girlfriend DB.As an experiment:I have stopped all webservices and coldfusion services on the webserver that access the DB server.Then I stopped and started the SQL Server and Agent to loose any connections that may be connecting to it so that I only work with the DB.I then placed the girlfriend database in single user modeeg: alter database girlfriend set single_userI then ran,DBCC checkdb (girlfriend, repair_rebuild) and no errors were foundI also did the more stringent DBCC check withDBCC checkdb (girlfriend, repair_allow_data_loss) and no errors were foundSo that confirms that the girlfriend database is ok integrity wise but it doesn't explain why the server would say its diconnected and does not go ahead with the backup. The GF DB is 1.3gb and last time it ran succesfully it take aprox 2mins 50sec to run the integrity checks..Can you help with this please.... (-:RegardsOliver" |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-09-06 : 13:00:49
|
| Is this for real? It reminds me of that joke about software...I understand the girlfriend database insisting that it be in single-user mode, however, if you have the wife database attached, it is totally incompatible with the girlfriend database and will attempt to permanently disconnect the girlfriend database. Or it may disconnect itself, taking with it the householdfinances database. And any attempt to create backups of the wife database are best left un-tried. |
 |
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-09-06 : 16:36:18
|
Hmm... Keeping a girlfriend in a multi-user mode? ... That's an interesting idea... But personally I am more keen on the idea of porting girlfriend database to multiple platforms, and letting the same DBA manage them all!   Seriously speaking though, I don't know what that disconnect message is all about, but I doubt that's what's stopping your integrity checks. I would guess that the main problem here is that you have users connected at the time your maintenance plan gets kicked off, which means the database can't go into a single-user mode, and as a result, your DBCCs fail. I don't think you can configure the maintenance plan to kick the users out of the database prior to running DBCCs. But you should be able to modify the job that triggers maintenance plan to make things work. Just add a step to kill the users in your db ("ALTER DATABASE girlfriend SET single_user WITH rollback immediate" would do it in SQL2k) right before the step that initiates the maintenance task.Edited by - izaltsman on 09/06/2002 16:36:55 |
|
|
|
|
|
|
|
|
Scheduled backups - server disconnected