Single User Mode questions

Question

Single User Mode questions

Taylor Prentice

SSC Veteran

Points: 269
More actions
April 12, 2007 at 7:54 am

#370487

I am having trouble running data and indew linkage on a database. The solution is to put the database into Single User mode.
the error is: [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed.Database needs to be in single user mode.
So I ran a query on master.dbo.sysprocesses to find out the users attached to the Db. There is one user listed, but there are 6 entries. 5 of them are sleeping and one of them is runnable with 2 active transactions. There are all TCP connections. the database is our surfcontrol webfilter.
Given that information, can I script it to set the db to single user, run my checks and backups, then set it back to multi-user, or do I need to approach this from the webfilter side, and script it to pause/shutdown while I run the maintenance tasks?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 1

You need to break all connections to get the single user mode. If an account is in an application role, you cannot disconnect it. You have to disable it.

It is not surpring that a user has more than one entry. A user may start several jobs. You need to kill all spid for this user before get the single user mode.

Taylor Prentice SSC Veteran Points: 269 More actions · Answer 2

I kinda figured that would be the case. Thanks for the info.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

This script should let you do what you want.

Take the database offline to kill the connections, bring it back online in single user mode, change to that database immediately to prevent another user from connecting, execute the DBCC repair, and then return the database to multi user mode.

use master
alter database MyDatabase set offline with rollback immediate
alter database MyDatabase set online, single_user with rollback immediate
use MyDatabase

-- Do DBCC repair stuff here

alter database MyDatabase set multi_user with rollback immediate

Taylor Prentice SSC Veteran Points: 269 More actions · Answer 4

Thats sounds like just what I need. I'm working on the script now. Thanks for all the great info!

Taylor Prentice SSC Veteran Points: 269 More actions · Answer 5

Just thought I'd update. After a little trial and error, that script format is working perfectly for my DBCC routines on this database. Thanks again for the great help!