unable to bring database in single user mode

Question

unable to bring database in single user mode

surya_7482

SSC-Addicted

Points: 445
More actions
January 23, 2012 at 11:08 am

#249535

unable to bring database in single user mode .its keeps on running
ultimately I have to cancel the command. any advice
thanks is advance

Viewing 4 posts - 1 through 3 (of 3 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

There's another existing connection and you didn't specify a ROLLBACK option on the Alter Database?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

umasingh Ten Centuries Points: 1182 More actions · Answer 2

any transaction might be using the database. that's why your transaction got blocked.

You can try with killing that transaction or use this query to rollback that particular transaction.

Alter database dbname set single_user with rollback immediate

Lowell SSC Guru Points: 323518 More actions · Answer 3

if you use the optional WITH ROLLBACK IMMEDIATE, it will disconnect everyone from the database.

ALTER DATABASE Logging_demoSP2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE

--do some exclusive stuff, like RESTORE

--if the database as in Multi_user mode at the time of the backup,

--that will be the mode after the restore, but just in case, put it back.

ALTER DATABASE Logging_demoSP2 SET MULTI_USER

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!