Pausing an MSSQL Instance

10

September 4, 2014 by Kenneth Fisher

I’m sure most of you have looked at the control options of the SQL services right? Start an instance, stop an instance, pause an instance. Start, stop, pause. Wait just a minute! Is this SQL Server or an mp3? (I just want to point out I started with a record, thought about a tape, then went to a CD. I actually had to think for a minute before coming up with MP3s. Talk about showing your age.) Most DBAs have had the opportunity to start and/or stop an instance. What most DBAs haven’t done is Pause or Resume an instance.

Actually Pausing or Resuming is pretty easy. There are several ways to do it but probably the easiest is to right click on the instance name in the Configuration Manager (or SSMS) and select Pause (or Resume depending). Note: In SSCM the list of instance services is under SQL Server Services and in SSMS it’s the connection in the Object Explorer.

Pause4

You can also use windows net commands.

net pause MSSQL$instancename
net continue MSSQL$instancename

Now that you know how, you still probably shouldn’t pause or resume an SQL Server instance if you don’t know what it actually does. From BOL:

When you pause an instance of Microsoft SQL Server, users that are connected to the server can finish tasks, but new connections are not allowed.

Interesting. So existing connections are unaffected but new connections are not allowed. Sounds useful. Useful how you might ask? Well lets say I need to run maintenance on a server. I let everyone who is currently connected know that they need to get out. One of the devs comes to me and let’s me know he has a batch process that is almost finished and could I please give him 10 more minutes. The problem is that if I wait I’m going to have a whole new group of people logged in. So what do I do?

Pause the instance!

Open 3 seperate query windows. Run the following code in 2 of them.

WAITFOR DELAY '00:01:30'

Next pause your instance.

Pause2

Then run this code in the 3rd window.

SELECT * FROM sys.dm_exec_requests WHERE session_id > 50

Pause5

You can see that not only do the running queries continue to run but new queries executed within an existing connection also run.

Now try to open a new connection.

Pause1

Now we see an error letting us know that the server is in fact paused and we can’t create new connections.

At this point we can shut down connections as they complete their tasks and once everyone is cleared out finish our maintenance.

Once we are done, if we didn’t actually stop and restart the instance, we should go ahead and resume it.

Pause3

As always make sure you have tried this out and are comfortable with how it works before trying it in production.

10 thoughts on “Pausing an MSSQL Instance

  1. Very useful post! Thanks for doing this, I’m a very curious person and I would have tested this in production. Now that I’ve read this article that itch has been scratched… hahaha 🙂

  2. Excellent post. I never realized pause was there and now I’m thinking of all the times that it would have been useful.

  3. […] Pausing a MSSQL Instance – CSS SQL Server Engineers (Blog) […]

  4. Thank-you for this post! I never realized that pausing a SQL Server instance can be this useful!

    • Yea it was a surprise to me also. I’m still coming up with new ways to use it. For example if you have an application that wants to keep connecting while you are trying to do maintenance you can generate all the connections you need, pause the instance, then kill the applications connections.

  5. vijred says:

    Glad to see the same topic as the question of the day @ http://www.sqlservercentral.com

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

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,146 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: