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.
Reference: Mr. Kenneth Blog Post
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
No comments:
Post a Comment