?
Solved

MSSQL 2000 Database Keeps Switching to Single User Mode

Posted on 2006-11-20
15
Medium Priority
?
325 Views
Last Modified: 2012-05-05
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
Comment
Question by:Junxies
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 2
15 Comments
 
LVL 20

Expert Comment

by:Sirees
ID: 17980738
Is your application changing the mode of SQL server by any chance?
0
 
LVL 5

Expert Comment

by:Netstore
ID: 17980848
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
 

Author Comment

by:Junxies
ID: 17980896
I'm told it seems to coincide with backups, but it doesn't happen on every backup.
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 5

Assisted Solution

by:Netstore
Netstore earned 300 total points
ID: 17980916
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
 

Author Comment

by:Junxies
ID: 17981033
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
 
LVL 20

Expert Comment

by:Sirees
ID: 17981179
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
 
LVL 20

Expert Comment

by:Sirees
ID: 17981195
<<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
 

Author Comment

by:Junxies
ID: 17981557
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
 

Author Comment

by:Junxies
ID: 17981577
<<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
 
LVL 20

Expert Comment

by:Sirees
ID: 17981603
<<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
 
LVL 20

Expert Comment

by:Sirees
ID: 17981621
0
 

Author Comment

by:Junxies
ID: 17981648
<<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
 

Author Comment

by:Junxies
ID: 17981652
Okay, going off to read that link...
0
 

Author Comment

by:Junxies
ID: 17981732
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
 
LVL 20

Accepted Solution

by:
Sirees earned 700 total points
ID: 17981909
<<Does that sound like a rational assessment?>>

Yes
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question