Tuesday, June 24, 2014

What if SQL Server Service is paused?

If you have gone through Windows services, specifically SQL Server services, either through SQL Server Configurations manager or Windows Services, you might have noticed that some services can be paused in addition to stopped or restarted. What if the service is paused?

If you pause the SQL Server service, there will not be any issue for existing connection but new connections will not be allowed. Users who have been working with databases can continue until they disconnect the connection. This is generally useful when you need to shutdown the service without disturbing existing users’ activities. Since this does not allow new connection, no user can establish a new connection and once all existing connections are disconnected, server can be safely shutdown.

Here is an example;

User makes a connection and runs a query:
image

Administrator pauses the service:
image

User still runs queries:
image

New user cannot establish the connection:
image

No comments: