If you want to know the SQL Server server name and instance name then just execute the
below query:
SELECT @@servername
If you want to know only the instance name of SQl server then execute:
SELECT SERVERPROPERTY ('InstanceName')
Thanks!!!
Under certain circumstances, we may have to start an instance of SQL Server in single-user mode by using the start up option -m.
For example, if we want to change server configuration options or recover a damaged master database or other system database. Both actions require starting an instance of SQL Server in single-user mode.
When we start an instance of SQL Server in single-user mode, note the following:
Only one user can connect to the server.
The CHECKPOINT process is not executed. By default, it is executed automatically at startup.
Stop the SQL Server Agent service before connecting to an instance of SQL Server in single-user mode; otherwise, the SQL Server Agent service uses the connection, thereby blocking it.
To open the SQL Server instance in SIngle user mode just Go to SQL Server Configuration Manager and click on SQL Server Services i.e. By default it is selected.
Now Click on desired SQL Server instance to which you want to open in single user mode. Then right click go to properties.
Click on Startup Parameter. (In sql server 2005/2008 click On the Advance tab enter param ‘-m;‘ before existing params in Startup Parameters box. Make sure that you entered semi-comma after -m.)
Put -m in text box and then click on Add. Click on OK.
A warning window will appear. Click ok then restart the service.
Now lets connect it through SSMS. You can also connect it through command line.
Ooops!!! We got an error. Saying Sql server is in single user mode and only one administrator can connect at this time.
Ohhh yesss We just forgot to stop SQL Server Agent service. Now stop this service and try again to connect it through new query editor it will be successful. If you will connect through explorer then after that you will not be able to open a query window.
Once done remove -m from startup parameter and restart the service.
Thanks!!!
References: Microsoft BOL
When we do right click on any MSSQL server instance we can see the couple of options like Start, Stop, Pause, Reusme and Restart. I have used only three options in my production environment that is Start, Stop and Restart of SQL server instance but never got a chance to pause a SQL Server and frankly speaking I was not having any idea that why there is a pause option in MSSQL server instance until a couple of months ago when I read a nice blog post of Mr. Kenneth on same. So I thought to share this with you guys.
As per Microsoft BOL,
"When you pause an instance of Microsoft SQL Server, users that are connected to the server can finish tasks, but new connections are not allowed."
It means that the database users which are already connected to database server and are in between process can complete there work but trying to create a new connection will be refused.
You can think of a scenario where you need to run maintenance on a server. You let everyone who is currently connected know that they need to be logged out. One of your developer comes to you and said that he has a batch process that is almost finished and will take 10 more minutes. The problem in that scenario is that if you will wait then in that time interval a lot of new users will be connected to your server. So, How you will manage this situation??
So this is where you can Pause the instance!!!
Let see the same in SSMS. Connect to sql server instance and open a new query window.
Execute Waitfor Delay '00:05:00'. This will be in process for 5 minutes.
Now Right click on Sql server instance and click on pause. A window will appear for confimation. click on Yes.
Now click on new query window. You will get below error window saying that New connection is not allowed.
After this you can resume,Restart or stop the service as per your requirement.
Thanks!!
Reference: Mr. Kenneth Blog Post
