Blog Post

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.

PauseSQl

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.

StopSQL

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 ).

Pauseserver

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

pausemessage

The dodgy query is actually still running.

executing

1.5 minutes later it completes.

queryCom

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.

cantconnect

The error log reflects the pause state too.

errorlogs

You will need to RESUME.

resumesql

Then connections can be made again.

 

 

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

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating