Solved

MSSQL 2000 Database Keeps Switching to Single User Mode

Posted on 2006-11-20
15
319 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
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…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

760 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