PAUSE SQL Server – SQL Server Tips

As some of you may have noticed we can PAUSE SQL Server resulting in the two ‘pause’ bars in the management studio interface;

SQL Server Icon Paused

But, what does Pausing the SQL Server actually do?

When we Pause SQL Server, any new connections cannot be made but existing connections can finish the work they’re doing. This may be a single transaction or multiple transactions whilst a connection is open. Pausing rather than stopping the services gives remaining connections time to finish any outstanding work in a clean manner and is just one more tool in our kit.

The following demonstration will pause the SQL Server database service and, as with usual disclaimers, should only be run on test environments where stopping or pausing services has no effect.

 

Lets prove this. First we’ll script a database and a table.

USE [master]
GO

CREATE DATABASE [Demo]

CREATE TABLE [dbo].[Orders](
       [ID] [INT] IDENTITY(1,1) NOT NULL,
       [OrderDateTime] [DATETIME2](7) NOT NULL,
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED 
(
       [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Orders] ADD  CONSTRAINT [DF_Orders_OrderDateTime]  DEFAULT (GETDATE()) FOR [OrderDateTime]
GO

Next we’ll create ten dummy transactions inserting data into our table every five seconds.

BEGIN TRANSACTION
INSERT INTO dbo.Orders DEFAULT VALUES
WAITFOR DELAY '00:00:05'
COMMIT TRANSACTION
GO 10

Now whilst this is running we can pause the MSSQLSERVER service, assuming the default instance.

We can do this with the GUI in SQL Server Management Studio ;

Pause SQL Server in SSMS

Or in an elevated command window;

NET PAUSE MSSQLSERVER

Once we have paused we can see that our transactions are still running by going to the messages tab and seeing more messages as the following transactions run and insert a row.

Messages Tab showing messages

We can also try to start an new connection or command and will get the following message ;

New connection error message

 

Then we can resume our MSSQLSERVER service with the GUI in SQL Server Management Studio;

Restart SQL Server in SSMSOr in an elevated command window;

NET CONTINUE MSSQLSERVER

 

Thanks for reading!

PAUSE SQL Server – SQL Server Tips

One thought on “PAUSE SQL Server – SQL Server Tips

Leave a Reply

Your email address will not be published.

Pin It on Pinterest