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;

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 ;

Or in an elevated command window;

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.

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

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

Or in an elevated command window;

Thanks for reading!