May 19, 2011 at 12:49 pm
I want to connect to the SQL Server Instance as a Single User Mode.
First i stopped the SQL Server Service
and then i went to the SQL Server Configurations Manager and in Startup Paramaters i put "-m;" in front of the others.
then re-started the service.
Then i went to CMD promt and went to the folder where there is sqlservr.exe file and typed "sqlservr.exe -m" but somehow its saying that "specified path cannot be found" even though the path is correct.
I dont know where i am going wrong. Please advise
May 19, 2011 at 12:54 pm
If you don't go into Single User Mode can you exec sqlcmd.exe?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 19, 2011 at 1:22 pm
No i still cannot, i dont know why, its the same location...
May 19, 2011 at 1:32 pm
You may want to try these to help you get better acquainted with the SQLCMD Utility.
http://databases.about.com/b/2008/10/28/using-sqlcmd-microsoft-sql-servers-command-line-utility.htm
http://msdn.microsoft.com/en-us/library/ms170207.aspx
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 19, 2011 at 1:45 pm
Thanks for the reply, but i am able to do that, sqlcmd works fine for me.
does this mean that i have to type "sqlservr.exe -m" after i type in sqlcmd?
May 19, 2011 at 2:18 pm
king_emperor_in (5/19/2011)
Thanks for the reply, but i am able to do that, sqlcmd works fine for me.does this mean that i have to type "sqlservr.exe -m" after i type in sqlcmd?
Make sure that you do not have any open connections first.
Starting sqlcmd in single user mode:
http://msdn.microsoft.com/en-us/library/ms188236.aspx
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 19, 2011 at 2:26 pm
Mind me asking why would you want to your instance to be in single user mode ?
Couldn't a user db in restricted user mode serve you well enough ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 19, 2011 at 2:48 pm
Well I can only move TEMPDB database point to a different drive only if i am on a single user mode right?
The instance on which i want to perform this action is a very active instance used my several databases and also severs applications.
So do you have any other ideas except single user mode to perfom this action?
Thanks
May 19, 2011 at 2:55 pm
king_emperor_in (5/19/2011)
Well I can only move TEMPDB database point to a different drive only if i am on a single user mode right?The instance on which i want to perform this action is a very active instance used my several databases and also severs applications.
So do you have any other ideas except single user mode to perfom this action?
Thanks
Moving System Databases
http://msdn.microsoft.com/en-us/library/ms345408.aspx
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 19, 2011 at 4:29 pm
king_emperor_in (5/19/2011)
went to the SQL Server Configurations Manager and in Startup Paramaters i put "-m;" in front of the others.then re-started the service.
wrong
king_emperor_in (5/19/2011)
Well I can only move TEMPDB database point to a different drive only if i am on a single user mode right?
wrong
Firstly remove the -m parameter from the startup in SQL Server configuration manager and restart the service. Once done go into SSMS and open a new query window and use the following
ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev ,
FILENAME = 'D:\MSSQLSERVER\MSSQL.2\MSSQL\Data\tempdb.mdf' )
ALTER DATABASE tempdb MODIFY FILE ( NAME = templog ,
FILENAME = 'E:\MSSQLSERVER\MSSQL.2\MSSQL\LOG\templog.ldf' )
Change the drive letter and path as necessary. Once this is done restart the sql service again and the new paths will be in use
😎
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 19, 2011 at 9:21 pm
Thanks for your reply, Well i did exactly the same thing on my local machine and it was fine.
but if i will do that on my Production Server, wouldnt that create lot of problem because there are too many active connections which are related to the production server?
So first of all i would have to figure out how many active connections are there on the production server and then perform the function and once thats done, i will have to re-open all the active connections, so it seems it will increase a lot of administrative work.
So i thought if i could perform the task of moving the tempdb with single user mode, i wont have to worry about the active connections and it wont allow any other connection at all.
Please advise...
May 20, 2011 at 12:04 am
concerning the number of active connections ... that is not a problem because you'll have to stop and restart the sqlinstance anyway to actually have it using your tempdb files on the new location.
Double check your sqlinstance service account has the needed windows authorities to access and use the new tempdb files !
I use :
rem /T Creates directory structure, but does not copy files. Does not include empty directories or subdirectories.
rem /T /E includes empty directories and subdirectories.
rem /O Copies file ownership and ACL information
xcopy e:\MSSQL.1\MSSQL F:\MSSQL.1\MSSQL /E /T /O
btw starting your instance single user, NOT having stopped application access is a huge gamble !
That is the reason why you cannot connect anyway ! One of your applications already did succeed to connect and consume your single connection.:crazy:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 20, 2011 at 2:58 pm
Thank you very much everybody, i will try all suggestions and let you know what happens
May 21, 2011 at 3:03 am
king_emperor_in (5/19/2011)
Thanks for your reply, Well i did exactly the same thing on my local machine and it was fine.but if i will do that on my Production Server, wouldnt that create lot of problem because there are too many active connections which are related to the production server?
So first of all i would have to figure out how many active connections are there on the production server and then perform the function and once thats done, i will have to re-open all the active connections, so it seems it will increase a lot of administrative work.
So i thought if i could perform the task of moving the tempdb with single user mode, i wont have to worry about the active connections and it wont allow any other connection at all.
Please advise...
if you execute the query i provided to you the new paths are not used until you restart sql server, so active connections have nothing to do with the changing of the paths
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply