SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Starting server in single-user mode
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sgondesi
Posting Yak Master

India
194 Posts

Posted - 12/30/2013 :  09:28:15  Show Profile  Reply with Quote
If we start sql server in single user mode, can we work with management studio?

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.

denis_the_thief
Aged Yak Warrior

Canada
591 Posts

Posted - 12/30/2013 :  10:22:04  Show Profile  Reply with Quote
If I am not mistaked, single user mode is not a start-up feature nor is it at the Server level. It is at the Database level.

Management Studio is just a Client Interface/Application. If you have permission to do something, you have this permission regardless of which Client tool you are using.

I use Single User Mode when I need to apply a lot of upgrade scripts, to prevent others from using the Database during the upgrade process. And yes, I will do it all in the Management Studio.
Go to Top of Page

denis_the_thief
Aged Yak Warrior

Canada
591 Posts

Posted - 12/30/2013 :  10:37:21  Show Profile  Reply with Quote
I take part of that back, you can start the Server in single-User Mode. (The single-user Database is also an option)

I guess I have never tried connecting with the Server in Single User Mode. But I did read, you have to turn off SQL Server Agent since it can use that one connection.

And sorry, I don't use Sinlge User mode to update the Database, I use Restricted mode - sa only. Maybe that option will work for you.

Edited by - denis_the_thief on 12/30/2013 10:52:13
Go to Top of Page

sgondesi
Posting Yak Master

India
194 Posts

Posted - 12/30/2013 :  12:25:16  Show Profile  Reply with Quote
quote:
Originally posted by denis_the_thief

If I am not mistaked, single user mode is not a start-up feature nor is it at the Server level. It is at the Database level.

Management Studio is just a Client Interface/Application. If you have permission to do something, you have this permission regardless of which Client tool you are using.

I use Single User Mode when I need to apply a lot of upgrade scripts, to prevent others from using the Database during the upgrade process. And yes, I will do it all in the Management Studio.



Thank you for your valuable words.
And i dont know how to use single user mode per database. Can you share that with here?

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page

lionofdezert
Aged Yak Warrior

Pakistan
885 Posts

Posted - 12/30/2013 :  12:25:38  Show Profile  Visit lionofdezert's Homepage  Send lionofdezert a Yahoo! Message  Reply with Quote
By single user mode you must mean, DAC-Dedicated Admin Connection. Yes you can SSMS when connected as DAC http://connectsql.blogspot.com/2012/10/sql-server-placing-alert-for.html


--------------------------
http://connectsql.blogspot.com/
Go to Top of Page

sgondesi
Posting Yak Master

India
194 Posts

Posted - 12/30/2013 :  12:27:19  Show Profile  Reply with Quote
quote:
Originally posted by denis_the_thief

I take part of that back, you can start the Server in single-User Mode. (The single-user Database is also an option)

I guess I have never tried connecting with the Server in Single User Mode. But I did read, you have to turn off SQL Server Agent since it can use that one connection.

And sorry, I don't use Sinlge User mode to update the Database, I use Restricted mode - sa only. Maybe that option will work for you.



When i am trying to stop SQL Server Agent, it is not getting stopped at all. Can you help me?

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36845 Posts

Posted - 12/30/2013 :  13:20:53  Show Profile  Visit tkizer's Homepage  Reply with Quote
Why are you trying to start SQL in single-user mode? SSMS uses multiple connections, so it can be hard to use when in single-user mode. Typically you'd use sqlcmd.exe.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

denis_the_thief
Aged Yak Warrior

Canada
591 Posts

Posted - 12/30/2013 :  13:37:39  Show Profile  Reply with Quote
On a Database Level, here is how in SSMS to set to single-user mode.

Select Database -> Properties -> Options

And then at the bottom is "Restrict Access"
Go to Top of Page

sgondesi
Posting Yak Master

India
194 Posts

Posted - 01/02/2014 :  05:14:17  Show Profile  Reply with Quote
quote:
Originally posted by denis_the_thief

On a Database Level, here is how in SSMS to set to single-user mode.

Select Database -> Properties -> Options

And then at the bottom is "Restrict Access"



Thank you for the post.

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page

sgondesi
Posting Yak Master

India
194 Posts

Posted - 01/02/2014 :  05:24:59  Show Profile  Reply with Quote
quote:
Originally posted by tkizer

Why are you trying to start SQL in single-user mode? SSMS uses multiple connections, so it can be hard to use when in single-user mode. Typically you'd use sqlcmd.exe.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/



Actually i am facing problem in executing the below command.

"ALTER DATABASE IsolationDB SET READ_COMMITTED_SNAPSHOT ON ;"

It is taking hours together and not giving any result. I dont know if we can call this as blocking.

Then one DBA here in this forum suggested me to start server in single user mode and try executing the command.

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page

sgondesi
Posting Yak Master

India
194 Posts

Posted - 01/02/2014 :  05:29:18  Show Profile  Reply with Quote
quote:
Originally posted by lionofdezert

By single user mode you must mean, DAC-Dedicated Admin Connection. Yes you can SSMS when connected as DAC http://connectsql.blogspot.com/2012/10/sql-server-placing-alert-for.html


--------------------------
http://connectsql.blogspot.com/



Sorry sir. I dont know when to use single user mode and what it was exactly.

Actually when i am facing problem in executing the below command,

"ALTER DATABASE IsolationDB SET READ_COMMITTED_SNAPSHOT ON ;"

Then one DBA here in this forum suggested me to start server in single user mode and try executing the command.

So i am trying to do that. That's all. I am not aware of single user mode, just came across DAC while learning the architecture but i dont know how to make use of it.

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36845 Posts

Posted - 01/02/2014 :  12:41:17  Show Profile  Visit tkizer's Homepage  Reply with Quote
quote:
Originally posted by sgondesi



Sorry sir. I dont know when to use single user mode and what it was exactly.

Actually when i am facing problem in executing the below command,

"ALTER DATABASE IsolationDB SET READ_COMMITTED_SNAPSHOT ON ;"

Then one DBA here in this forum suggested me to start server in single user mode and try executing the command.

So i am trying to do that. That's all. I am not aware of single user mode, just came across DAC while learning the architecture but i dont know how to make use of it.




You do not need to put the server in single-user mode to execute that query. You do need to disconnect all sessions from that database though.

This should do it if the connected users aren't members of sysadmin or db_owner roles:

ALTER DATABASE IsolationDB SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE IsolationDB SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE IsolationDB SET MULTI_USER;

If that doesn't work, then use this:


ALTER DATABASE IsolationDB SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE

DECLARE @spid varchar(10)

SELECT @spid = spid
FROM master.sys.sysprocesses
WHERE dbid = DB_ID('IsolationDB')

WHILE @@ROWCOUNT <> 0
BEGIN
 EXEC('KILL ' + @spid)

 SELECT @spid = spid
 FROM master.sys.sysprocesses
 WHERE 
  dbid = DB_ID('IsolationDB') AND
  spid > @spid 
END

ALTER DATABASE IsolationDB SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE IsolationDB SET MULTI_USER;



Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Edited by - tkizer on 01/02/2014 12:41:46
Go to Top of Page

sgondesi
Posting Yak Master

India
194 Posts

Posted - 01/03/2014 :  05:08:46  Show Profile  Reply with Quote
quote:
Originally posted by tkizer

quote:
Originally posted by sgondesi



Sorry sir. I dont know when to use single user mode and what it was exactly.

Actually when i am facing problem in executing the below command,

"ALTER DATABASE IsolationDB SET READ_COMMITTED_SNAPSHOT ON ;"

Then one DBA here in this forum suggested me to start server in single user mode and try executing the command.

So i am trying to do that. That's all. I am not aware of single user mode, just came across DAC while learning the architecture but i dont know how to make use of it.




You do not need to put the server in single-user mode to execute that query. You do need to disconnect all sessions from that database though.

This should do it if the connected users aren't members of sysadmin or db_owner roles:

ALTER DATABASE IsolationDB SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE IsolationDB SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE IsolationDB SET MULTI_USER;

If that doesn't work, then use this:


ALTER DATABASE IsolationDB SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE

DECLARE @spid varchar(10)

SELECT @spid = spid
FROM master.sys.sysprocesses
WHERE dbid = DB_ID('IsolationDB')

WHILE @@ROWCOUNT <> 0
BEGIN
 EXEC('KILL ' + @spid)

 SELECT @spid = spid
 FROM master.sys.sysprocesses
 WHERE 
  dbid = DB_ID('IsolationDB') AND
  spid > @spid 
END

ALTER DATABASE IsolationDB SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE IsolationDB SET MULTI_USER;



Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/



I did not get any result when i tried executing the command by closing all connections to the databse on the day when i got that problem.

But today when i executed it by closing all other connections, the command got executed in a flash.

And i understood why should i do so from your post only. Thanks a lot madam.

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000