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.

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

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.

Comments

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

Loading comments...