SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SQL Server – Pause VS Stop

You have the ability to actually pause SQL Server, if you are in SQL Server Management Studio (SSMS), you might have noticed it as the below image.


As stated via official Microsoft documentation “Pausing the Database Engine service prevents new users from connecting to the Database Engine, but users who are already connected can continue to work until their connections are broken. Use pause when you want to wait for users to complete work before you stop the service. This enables them to complete transactions that are in progress”.

Very handy! Let’s see it in action and compare it to a STOP.

I run a silly query that will give me some time to play with the setting, I click STOP – then the following occurs.


The query doesn’t get the chance to complete and the connection gets disconnected, just as I expect.

So now is a good time to test out PAUSE ( I start the service and re issue the query ).


You will get the pause warning, which is fine by me.


The dodgy query is actually still running.


1.5 minutes later it completes.


Try and re-connect via SSMS and you will not be able to. It will say that SQL Server has been paused and no new connections will be allowed.


The error log reflects the pause state too.


You will need to RESUME.


Then connections can be made again.



Filed under: Admin, SQL SERVER Tagged: Admin, Config, Pause, SQL database, SQL server

All About SQL

I am a Senior DBA with interest in MS technology especially SQL Server and Azure. During 2015 I was mentored by Paul Randal – Data Platform (SQL Server) MVP and during 2016 I completed my SQLskills Immersion training on Internals and Performance Tuning. When I am not working I am in the gym burning calories.


Leave a comment on the original post [blobeater.blog, opens in a new window]

Loading comments...