• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 327
  • Last Modified:

MSSQL 2000 Database Keeps Switching to Single User Mode

I've got a database that keeps switching to single user mode, and it's causing problems with the Web application that uses it. What might be the cause?

Let me know if you need more details.

Thanks!
0
Junxies
Asked:
Junxies
  • 7
  • 6
  • 2
2 Solutions
 
SireesCommented:
Is your application changing the mode of SQL server by any chance?
0
 
NetstoreCommented:
Check the server logs and see when this is happening. Is the database being bounced around this time?

Is there anything in the event logs?
0
 
JunxiesAuthor Commented:
I'm told it seems to coincide with backups, but it doesn't happen on every backup.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
NetstoreCommented:
Are the backups performed via a Database Maintenance plan? Or are they custom jobs?

Also if you check the SQL server logs in Ent Manager -> Management -> SQL Server Logs this should show the database going into single user mode to pin point the exact time this occurs
0
 
JunxiesAuthor Commented:
Theory: The (global) Maintenance Plan (which does backups on all databases), is putting the DB in single user mode, because the DB “needs to be repaired.”

Facts:
•      19:00, the maintenance plan complains that the “repair statement not processed… database needs to be in single user mode”
•      19:03, the maintenance plan complains that it can’t access the DB because it’s “already open, and can only have one user at a time”

So, between these two logs, the maintenance plan seems to be switching the DB to single user mode.

One solution, I guess, would be to disable the “repair” step in the maintenance plan, but I don’t know all the implications.
0
 
SireesCommented:
Do you check the "Check Datababase integrity" in your backup plans? and also "attempt to repair any minor Problems"

If so, it runs DBCC Checkdb. To run DBCC CHECKDB on a database with a repair level, the database will be set to single user mode.

From BOL:

Attempt to repair any minor problems option-

Attempt to correct any minor problems  detected during the database integrity tests automatically. When this option is selected, the database will be put in single user mode each time the maintenance plan runs.

Check this one: http://support.microsoft.com/kb/264154/EN-US/
0
 
SireesCommented:
<<One solution, I guess, would be to disable the “repair” step in the maintenance plan, but I don’t know all the implications.>>

May be you can create another job for this option and run it once a week, when there is no/less activity.
0
 
JunxiesAuthor Commented:
It's too bad the Maintenance Plan doesn't have an "All databases *except*" option. It's seems pretty convenient to have all databases (even newly added ones) backed up without an explicit add, while being able to exclude certain DBs explicitly.

I'm starting to lean in the direction of disabling the repair step in the MP, because I think the benefits of having a global MP with "repair" disabled outweighs the benefit of having to explicitly add new DBs to a global MP that has "repair" enabled.

Do you have any sense for how much good the "repair" piece does, by the way?
0
 
JunxiesAuthor Commented:
<<May be you can create another job for this option and run it once a week, when there is no/less activity.>>

If I did this, I'd still have to figure out some automated way of detecting and switching out of single-user mode after the MP runs, right?
0
 
SireesCommented:
<<If I did this, I'd still have to figure out some automated way of detecting and switching out of single-user mode after the MP runs, right?>>

No..maintanance plans will automatically do this for you.
0
 
JunxiesAuthor Commented:
<<No..maintanance plans will automatically do this for you.>>

Okay, there's something I'm not getting then. Are you suggesting that the way it's set up now, that there's not enough time between the "repair" and the backup, and that's why it gets stuck?

Are you advocating having separate MPs, one for "repair" and one for "backup," scheduled at two different times?

Otherwise, please clarify?

0
 
JunxiesAuthor Commented:
Okay, going off to read that link...
0
 
JunxiesAuthor Commented:
From your link:

<<
# Don’t use the option "Attempt to repair any minor problems" which causes DBCC Checkdb to be run with the option REPAIR_FAST. This option performs minor repair actions without risk of data loss. However, the given database must be in single-user mode to use this value, which presents problems if this is a production database.

# If database is not in single-user mode, DBCC Checkdb with REPAIR_FAST, will not be run, unfortunately the text or html report files will still report a success.
>>

Looks like the idea of disabling the "repair" is a winner. DBCC Checkdb seems like an option that individual DBs might need ad-hoc or a la carte. It doesn't seem appropriate to have it in our production MP.

Does that sound like a rational assessment?
0
 
SireesCommented:
<<Does that sound like a rational assessment?>>

Yes
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now