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