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.

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.

Then run this code in the 3rd window.

SELECT * FROM sys.dm_exec_requests WHERE session_id > 50

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.

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.

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

Filed under: Microsoft SQL Server, Settings, SQL Services, SQLServerPedia Syndication Tagged: microsoft sql server, SQL Services