Solved

MSSQL 2000 Database Keeps Switching to Single User Mode

Posted on 2006-11-20
15
320 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
  • 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
 
LVL 5

Assisted Solution

by:Netstore
Netstore earned 75 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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 175 total points
ID: 17981909
<<Does that sound like a rational assessment?>>

Yes
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

919 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now