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.

 

 

One thought on “SQL Server – Pause VS Stop

  1. Pingback: Pausing Versus Stopping SQL Servers – Curated SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s